다른 명령
DB 정보 조회 스크립트
리두로그 멤버 조회
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 /
전체 테이블스페이스 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
유저에게 부여된 모든 권한 - 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;
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 /
시간별 생성되는 아키이브 로그 조회
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 /
현재 트랜잭션 상태 조회
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 /
계정 잠금 사유 조회
-- 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);
테이블에서 중복된 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) /
데이터파일 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);
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');
DB UPTIME 조회
select to_char(startup_time, 'DD-MM-YYYY HH24:MI:SS'),floor(sysdate-startup_time) DAYS from v$Instance;
현재 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;
스키마(유저)의 모든 테이블 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';
인덱스 사용량 모니터링
---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;
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
오라클 패치 정보 조회
--- 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;
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$%'
설치된 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;
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
언두 리텐션 최적 사이즈 찾기
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';
오래된 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
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);
열린 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;
현재 사용중인 리루로그량 %
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
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;
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');
DB 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;
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;
전체 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;
테이블스페이스 사용량 모니터링
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;
언두 테이블스페이스 사용량 모니터링
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;
템프 테이블스페이스 사용량 모니터링
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;
블럭킹 세션 조회
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;
오래 수행중인 세션 조회
select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining from gv$session_longops where totalwork <> sofar /
현재 락이 발생된 세션 조회
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 /
프로시져로 실행된 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';
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;
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;
모든 세션 유저 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; /
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'
병렬 쿼리 상세 조회
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 /
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' ;
높은 실행 시간 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
병렬 쿼리 모니터링
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
열려있는 커서 조회
-- 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;
[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;
버퍼캐시 히트율
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' /
디스크 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;
프로시져 오브젝트로 수행중인 세션 / 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') );
버퍼 캐시 사용량 조회
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 /
롤백 트랜잭션 조회
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;
테이블/컬럼 사용 통계
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;
백그라운드 프로세스 상세 조회
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;
오라클 DB는 몇비트 인가?
select length(addr)*4 || '-bits' word_length from v$process where ROWNUM =1;
오라클 라이센스 사용 정보
/* 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 */;
오라클 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');