"DATABASE 조회 scripts"의 두 판 사이의 차이
DB CAFE
(→계정 잠금 사유 조회) |
|||
1,169번째 줄: | 1,169번째 줄: | ||
</source> | </source> | ||
+ | |||
+ | [[category:oracle]] |
2024년 7월 23일 (화) 18:07 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 DB 정보 조회 스크립트
- 1.1 리두로그 멤버 조회
- 1.2 전체 테이블스페이스 DDL 추출
- 1.3 유저에게 부여된 모든 권한 - DDL 추출
- 1.4 DB (데이터/템프/로그/남은공간) SIZE 조회
- 1.5 시간별 생성되는 아키이브 로그 조회
- 1.6 현재 트랜잭션 상태 조회
- 1.7 계정 잠금 사유 조회
- 1.8 테이블에서 중복된 ROW 찾기
- 1.9 데이터파일 RESIZE 스크립트 생성기
- 1.10 DB 한달 증가량 조회
- 1.11 DB UPTIME 조회
- 1.12 현재 scn 정보 조회
- 1.13 스키마(유저)의 모든 테이블 row건수
- 1.14 인덱스 사용량 모니터링
- 1.15 SQL 쿼리 HTML로 출력하기
- 1.16 오라클 패치 정보 조회
- 1.17 Data Pump jobs 정리
- 1.18 설치된 RDBMS 컴포넌트
- 1.19 AWR retention 기간 조회/변경
- 1.20 언두 리텐션 최적 사이즈 찾기
- 1.21 오래된 AWR 스냅샷 Purge
- 1.22 AWR window_size 조사/변경
- 1.23 열린 DBLINK 정보 조회
- 1.24 현재 사용중인 리루로그량 %
- 1.25 FK 컬럼에 인덱스가 없는 테이블 조회
- 1.26 DB서버 cpu 메모리 현황
- 1.27 DB incarnation 정보 조회
- 1.28 DB 타임존 조회
- 1.29 전체 DB 사이즈(사용량/남은량/총량) 조회
- 1.30 테이블스페이스 사용량 모니터링
- 1.31 언두 테이블스페이스 사용량 모니터링
- 1.32 템프 테이블스페이스 사용량 모니터링
- 1.33 블럭킹 세션 조회
- 1.34 오래 수행중인 세션 조회
- 1.35 현재 락이 발생된 세션 조회
- 1.36 프로시져로 실행된 SQL 조회
- 1.37 os 프로세스 id 로 sql 찾기
- 1.38 SID로 프로세스 아이디 조회
- 1.39 모든 세션 유저 KILL
- 1.40 sql_id가 같은 모든 세션 KILL (주로 병렬쿼리 kill 시킬때 )
- 1.41 병렬 쿼리 상세 조회
- 1.42 SNIPED(짤린) 세션 KILL
- 1.43 높은 실행 시간 TOP 쿼리
- 1.44 병렬 쿼리 모니터링
- 1.45 열려있는 커서 조회
- 1.46 [ash] 세션 로그인 이력
- 1.47 버퍼캐시 히트율
- 1.48 디스크 READ가 많은 유저 TOP SQL
- 1.49 프로시져 오브젝트로 수행중인 세션 / SQL ID
- 1.50 버퍼 캐시 사용량 조회
- 1.51 롤백 트랜잭션 조회
- 1.52 테이블/컬럼 사용 통계
- 1.53 백그라운드 프로세스 상세 조회
- 1.54 오라클 DB는 몇비트 인가?
- 1.55 오라클 라이센스 사용 정보
- 1.56 오라클 OPTIMIZER PROCESSING RATE
1 DB 정보 조회 스크립트[편집]
1.1 리두로그 멤버 조회[편집]
col member for a56
set pagesize 299
set lines 299
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) "Size (MB)"
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
1.2 전체 테이블스페이스 DDL 추출[편집]
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
spool off
1.3 유저에게 부여된 모든 권한 - DDL 추출[편집]
set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt "Enter username :"
--This line add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
-- This will generate the DDL for the user and add his objects,system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;
1.4 DB (데이터/템프/로그/남은공간) SIZE 조회[편집]
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
1.5 시간별 생성되는 아키이브 로그 조회[편집]
set lines 299
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/
1.6 현재 트랜잭션 상태 조회[편집]
col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/
1.7 계정 잠금 사유 조회[편집]
-- Return code 1017 ( INVALID LOGIN ATTEMPT)
-- Return code 28000 ( ACCOUNT LOCKED)
set pagesize 1299
set lines 299
col username for a15
col userhost for a13
col timestamp for a39
col terminal for a23
----
SELECT username,userhost,terminal,timestamp,returncode
FROM dba_audit_session
WHERE username='&USER_NAME' and returncode in (1017,28000);
1.8 테이블에서 중복된 ROW 찾기[편집]
--- Reference metalink id - 332494.1
-- Save as duplicate.sql and run as @duplicate.sql
REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/
1.9 데이터파일 RESIZE 스크립트 생성기[편집]
generate resize datafile script without ORA-03297 error
select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);
1.10 DB 한달 증가량 조회[편집]
select to_char(creation_time, 'MM-RRRR') "Month"
, sum(bytes)/1024/1024/1024 "Growth in GB
from sys.v_$datafile
where to_char(creation_time,'RRRR')='&YEAR_IN_YYYY_FORMAT'
group by to_char(creation_time, 'MM-RRRR')
order by to_char(creation_time, 'MM-RRRR');
1.11 DB UPTIME 조회[편집]
select to_char(startup_time, 'DD-MM-YYYY HH24:MI:SS'),floor(sysdate-startup_time) DAYS from v$Instance;
1.12 현재 scn 정보 조회[편집]
-- Get current scn value:
select current_scn from v$database;
-- Get scn value at particular time:
select timestamp_to_scn('19-JAN-08:22:00:10') from dual;
-- Get timestamp from scn:
select scn_to_timestamp(224292)from dual;
1.13 스키마(유저)의 모든 테이블 row건수[편집]
select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype('select /*+ PARALLEL(8) */ count(*) cnt from "&&SCHEMA_NAME".'||table_name),'/ROWSET/ROW/CNT'))
rows_in_table from dba_TABLES
where owner='&&SCHEMA_NAME';
1.14 인덱스 사용량 모니터링[편집]
---Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.
-- First enable monitoring usage for the indexes. (운영중인 db에서는 사용금지. 라이브러리 캐시 락 발생됨.)
alter index siebel.S_ASSET_TEST monitoring usage;
--Below query to find the index usage:
select * from v$object_usage;
1.15 SQL 쿼리 HTML로 출력하기[편집]
We can spool output of an sql query to html format:
set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>EMPLOYEE REPORT</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"
spool report.html
Select * from scott.emp;
spool off
exit
1.16 오라클 패치 정보 조회[편집]
--- From 12c onward
set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
--- For 11g and below:
set lines 2000
select * from dba_registry_history;
1.17 Data Pump jobs 정리[편집]
-- Find the orphaned Data Pump jobs:
SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%' and state='NOT RUNNING'
ORDER BY 1,2;
-- Drop the tables
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and job_name NOT LIKE 'BIN$%'
1.18 설치된 RDBMS 컴포넌트[편집]
col comp_id for a10
col comp_name for a56
col version for a12
col status for a10
set pagesize 200
set lines 200
set long 999
select comp_id,comp_name,version,status from dba_registry;
1.19 AWR retention 기간 조회/변경[편집]
-- View current AWR retention period
select retention from dba_hist_wr_control;
-- Modify retention period to 7 days and interval to 30 min
select dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080);
NOTE - 7 DAYS = 7*24*3600= 10080 minutes
1.20 언두 리텐션 최적 사이즈 찾기[편집]
SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value, 1, 25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) /
(1024 * 1024) "NEEDED UNDO SIZE [MByte]"
FROM (SELECT SUM(a.bytes) undo_size
FROM gv$datafile a, gv$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#) d,
gv$parameter e,
gv$parameter f,
(SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
FROM v$undostat) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
1.21 오래된 AWR 스냅샷 Purge[편집]
-- Find the AWR snapshot details.
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id
-- Purge snapshot between snapid 612 to 700
execute dbms_workload_repository.drop_snapshot_range(low_snap_id =>612 , high_snap_id =>700);
-- Verify again
select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id
1.22 AWR window_size 조사/변경[편집]
-- Check the current moving window baseline size:
select BASELINE_TYPE,MOVING_WINDOW_SIZE from dba_hist_baseline;
-- Modify window_size to (7 days):
execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7);
1.23 열린 DBLINK 정보 조회[편집]
set pagesize 200
set lines 200
col db_link for a19
set long 999
SELECT db_link,
owner_id,
logged_on,
heterogeneous,
open_cursors,
in_transaction,
update_sent
FROM gv$dblink
ORDER BY db_link;
1.24 현재 사용중인 리루로그량 %[편집]
SELECT le.leseq "Current log sequence No"
, 100*cp.cpodr_bno/le.lesiz "Percent Full"
, cp.cpodr_bno "Current Block No"
, le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8
1.25 FK 컬럼에 인덱스가 없는 테이블 조회[편집]
select * from (
select c.table_name, co.column_name, co.position column_position
from user_constraints c, user_cons_columns co
where c.constraint_name = co.constraint_name
and c.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;
select
a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,nvl(c.column_name,'***No Index***') ind_column
from user_constraints a
join
user_cons_columns b on a.constraint_name = b.constraint_name
left outer join
user_ind_columns c on b.column_name = c.column_name
and b.table_name = c.table_name
where constraint_type = 'R'
order by 2,1;
1.26 DB서버 cpu 메모리 현황[편집]
set pagesize 200
set lines 200
col name for a21
col stat_name for a25
col value for a13
col comments for a56
select STAT_NAME
, to_char(VALUE) as VALUE
, COMMENTS
from v$osstat
where stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB'
, COMMENTS
from v$osstat
where stat_name IN ('PHYSICAL_MEMORY_BYTES');
1.27 DB incarnation 정보 조회[편집]
assignment * incarnation
- 데이터베이스를 resetlogs 옵션으로 open 할때 incarnation이 생성됨(reset)
- 일반 recover 완료 후에는 resetlogs 없이도 정상 open 할 수도 있지만 Point In Time Recovery 나 using backup controlfile로 복구한 후에는 resetlogs 옵션으로 데이터베이스를 open 해야 하므로 데이터베이스의 새로운 incarnation이 생성됨
- 데이터베이스는 서로 다른 두 redo stream의 SCN이 동일하지만 서로 다른 시간에 발생한 경우 혼란을 피하기 위해 새로운 incarnation을 필요로함
- 데이터베이스에 잘못된 redo를 적용하면 데이터베이스가 손상을 초래함.
- 단일 데이터베이스에 여러 개의 incarnation이 존재함에 따라 rman이 현재 incarnation 경로에 없는 백업을 처리하는 방식이 결정됨
- 일반적으로 current incarnation의 데이터베이스를 사용하는 것이 좋음
- 일부 경우에 데이터베이스를 이전 incarnation로 재설정하는 것이 가장 좋은 방법일수도 있음
set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on
select INCARNATION# INC#
, RESETLOGS_CHANGE# RS_CHANGE#
, RESETLOGS_TIME
, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#
, STATUS
, FLASHBACK_DATABASE_ALLOWED FB_OK
from v$database_incarnation;
1.28 DB 타임존 조회[편집]
SELECT version FROM v$timezone_file;
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
1.29 전체 DB 사이즈(사용량/남은량/총량) 조회[편집]
set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;
1.30 테이블스페이스 사용량 모니터링[편집]
set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;
1.31 언두 테이블스페이스 사용량 모니터링[편집]
select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a
, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;
1.32 템프 테이블스페이스 사용량 모니터링[편집]
select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;
1.33 블럭킹 세션 조회[편집]
SELECT s.inst_id
, s.blocking_session
, s.sid
, s.serial#
, s.seconds_in_wait
FROM gv$session s
WHERE blocking_session IS NOT NULL;
1.34 오래 수행중인 세션 조회[편집]
select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining
from gv$session_longops
where totalwork <> sofar
/
1.35 현재 락이 발생된 세션 조회[편집]
col session_id head 'Sid' form 9999
col object_name head "Table|Locked" form a30
col oracle_username head "Oracle|Username" form a10 truncate
col os_user_name head "OS|Username" form a10 truncate
col process head "Client|Process|ID" form 99999999
col mode_held form a15
select lo.session_id,lo.oracle_username,lo.os_user_name
, lo.process,do.object_name
, decode(lo.locked_mode,0, 'None'
,1, 'Null'
,2, 'Row Share (SS)'
,3, 'Row Excl (SX)'
,4, 'Share'
,5, 'Share Row Excl (SSX)'
,6, 'Exclusive'
,to_char(lo.locked_mode)) mode_held
from v$locked_object lo, dba_objects do
where lo.object_id = do.object_id
order by 1,5
/
1.36 프로시져로 실행된 SQL 조회[편집]
-- Below script will provide the dependent queries getting triggered from a procedure.
SELECT s.sql_id, s.sql_text
FROM gv$sqlarea s
JOIN dba_objects o
ON s.program_id = o.object_id
and o.object_name = '&procedure_name';
1.37 os 프로세스 id 로 sql 찾기[편집]
- Get sid from os pid ( server process)
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
1.38 SID로 프로세스 아이디 조회[편집]
set lines 123
col USERNAME for a15
col OSUSER for a8
col MACHINE for a15
col PROGRAM for a20
SELECT b.spid
, a.username
, a.program
, a.osuser
, a.machine
, a.sid
, a.serial#
, a.status
FROM gv$session a, gv$process b
WHERE addr = paddr(+)
AND sid = &sid;
1.39 모든 세션 유저 KILL[편집]
BEGIN
FOR r IN (select sid,serial# from v$session where username = 'TEST_ANB')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid
|| ',' || r.serial# || '''';
END LOOP;
END;
/
1.40 sql_id가 같은 모든 세션 KILL (주로 병렬쿼리 kill 시킬때 )[편집]
select 'alter system kill session ' ||''''||SID||','||SERIAL#||' immediate ;' from v$session
where sql_id='&sql_id';
# RAC용
select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;'
from gv$session where sql_id='&sql_id'
1.41 병렬 쿼리 상세 조회[편집]
col username for a9
col sid for a8
set lines 299
select s.inst_id
, decode(px.qcinst_id,NULL,s.username
, ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username"
, decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave"
, to_char( px.server_set) "Slave Set"
, to_char(s.sid) "SID"
, decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID"
, px.req_degree "Requested DOP"
, px.degree "Actual DOP", p.spid
from gv$px_session px
, gv$session s
, gv$process p
where px.sid=s.sid (+)
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and p.inst_id = s.inst_id
and p.addr=s.paddr
order by 5 , 1 desc
/
1.42 SNIPED(짤린) 세션 KILL[편집]
-- It will generate kill session statements for all snipped sessions:
select 'alter system kill session '''||sid||','||serial#||''' immediate;'
from v$session where status='SNIPED' ;
1.43 높은 실행 시간 TOP 쿼리[편집]
-- Queries in last 1 hour ( Run from Toad, for proper view)
Select module,parsing_schema_name
, inst_id,sql_id,CHILD_NUMBER
, sql_plan_baseline,sql_profile
, plan_hash_value,sql_fulltext
, to_char(last_active_time,'DD/MM/YY HH24:MI:SS' )
, executions
, elapsed_time/executions/1000/1000
, rows_processed,sql_plan_baseline
from gv$sql
where last_active_time>sysdate-1/24
and executions <> 0
order by elapsed_time/executions desc
1.44 병렬 쿼리 모니터링[편집]
select
s.inst_id,
decode(px.qcinst_id,NULL,s.username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP", p.spid
from
gv$px_session px,
gv$session s, gv$process p
where
px.sid=s.sid (+) and
px.serial#=s.serial# and
px.inst_id = s.inst_id
and p.inst_id = s.inst_id
and p.addr=s.paddr
order by 5 , 1 desc
1.45 열려있는 커서 조회[편집]
-- Current open cursor
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
-- Max allowed open cursor and total open cursor
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
1.46 [ash] 세션 로그인 이력[편집]
SELECT c.username
, a.SAMPLE_TIME
, a.SQL_OPNAME
, a.SQL_EXEC_START
, a.program
, a.module
, a.machine
, b.SQL_TEXT
FROM DBA_HIST_ACTIVE_SESS_HISTORY a
, dba_hist_sqltext b
, dba_users c
WHERE a.SQL_ID = b.SQL_ID(+)
AND a.user_id = c.user_id
AND c.username = '&username'
ORDER BY a.SQL_EXEC_START ASC;
1.47 버퍼캐시 히트율[편집]
SELECT ROUND( (1 - (phy.VALUE / (cur.VALUE + con.VALUE))) * 100, 2 ) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
1.48 디스크 READ가 많은 유저 TOP SQL[편집]
SELECT username users
, ROUND( DISK_READS / Executions ) DReadsExec
, Executions Exec
, DISK_READS DReads
, sql_text
FROM gv$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND Executions > 0
AND DISK_READS > 100000
ORDER BY 2 DESC;
1.49 프로시져 오브젝트로 수행중인 세션 / SQL ID[편집]
SELECT sid
, sql_id
, serial#
, status
, username
, program
FROM v$session
WHERE PLSQL_ENTRY_OBJECT_ID IN ( SELECT object_id
FROM dba_objects
WHERE object_name IN ('&PROCEDURE_NAME') );
1.50 버퍼 캐시 사용량 조회[편집]
col object_name format a30
col to_total format 999.99
SELECT owner
, object_name
, object_type
, COUNT
, (COUNT / VALUE) * 100 to_total
FROM ( SELECT a.owner
, a.object_name
, a.object_type
, COUNT( * ) COUNT
FROM dba_objects a, x$bh b
WHERE a.object_id = b.obj
AND a.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY a.owner, a.object_name, a.object_type
ORDER BY 4)
, v$parameter
WHERE name = 'db_cache_size'
AND (COUNT / VALUE) * 100 > .005
ORDER BY to_total DESC
/
1.51 롤백 트랜잭션 조회[편집]
SELECT state
, UNDOBLOCKSDONE
, UNDOBLOCKSTOTAL
, UNDOBLOCKSDONE / UNDOBLOCKSTOTAL * 100
FROM gv$fast_start_transactions;
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
SELECT usn
, state
, undoblockstotal "Total"
, undoblocksdone "Done"
, undoblockstotal - undoblocksdone "ToDo"
, DECODE(
cputime
, 0, 'unknown'
, SYSDATE
+ ( ( (undoblockstotal - undoblocksdone)
/ (undoblocksdone / cputime))
/ 86400) ) "Estimated time to complete"
FROM v$fast_start_transactions;
SELECT a.sid
, a.username
, b.xidusn
, b.used_urec
, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY 5 DESC;
1.52 테이블/컬럼 사용 통계[편집]
set lines 150
set pages 500
col table_name for a20
col column_name for a20
SELECT a.object_name table_name
, c.column_name
, equality_preds
, equijoin_preds
, range_preds
, like_preds
FROM dba_objects a, col_usage$ b, dba_tab_columns c
WHERE a.object_id = b.OBJ#
AND c.COLUMN_ID = b.INTCOL#
AND a.object_name = c.table_name
AND b.obj# = a.object_id
AND a.object_name = '&table_name'
AND a.object_type = 'TABLE'
AND a.owner = '&owner'
ORDER BY 3 DESC, 4 DESC, 5 DESC;
1.53 백그라운드 프로세스 상세 조회[편집]
col ksbddidn for a15
col ksmfsnam for a20
col ksbdddsc for a60
set lines 150 pages 5000
SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
WHERE ksbdd.indx = ksbdp.indx
AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;
1.54 오라클 DB는 몇비트 인가?[편집]
select
length(addr)*4 || '-bits' word_length
from
v$process
where
ROWNUM =1;
1.55 오라클 라이센스 사용 정보[편집]
/* Formatted on 2024/07/11 오후 11:55:53 (QP5 v5.391) */
SELECT samp.dbid
, fu.name
, samp.version
, detected_usages
, total_samples
, DECODE( TO_CHAR( last_usage_date, 'MM/DD/YYYY, HH:MI:SS' )
, NULL, 'FALSE'
, TO_CHAR( last_sample_date, 'MM/DD/YYYY, HH:MI:SS' ), 'TRUE'
, 'FALSE' ) currently_used
, first_usage_date
, last_usage_date
, aux_count
, feature_info
, last_sample_date
, last_sample_period
, sample_interval
, mt.description
FROM wri$_dbu_usage_sample samp
, wri$_dbu_feature_usage fu
, wri$_dbu_feature_metadata mt
WHERE samp.dbid = fu.dbid
AND samp.version = fu.version
AND fu.name = mt.name
AND fu.name NOT LIKE '_DBFUS_TEST%'
AND /* filter out test features */
BITAND( mt.usg_det_method, 4 ) != 4 /* filter out disabled features */;
1.56 오라클 OPTIMIZER PROCESSING RATE[편집]
SELECT OPERATION_NAME, DEFAULT_VALUE
FROM V$OPTIMIZER_PROCESSING_RATE
WHERE OPERATION_NAME IN ('IO_BYTES_PER_SEC'
,'CPU_BYTES_PER_SEC'
, 'CPU_ROWS_PER_SEC');