墨墨导读：本文分享Oracle驻场工程师常用的脚本，基本上包含了日常监控、维护、故障定位及处理、SQL性能优化大部分场景，有了这些脚本会让你的工作变得更轻松，文末附下载链接。
1. 2pc_clean.txt
2. ash_sql_line_id.txt
3. awr_db_time.txt
4. awr_metric_name.txt
5. bind_noused.txt
6. cursor_purge.txt
7. ddl_metadata.txt
8. dml_get.txt
9. fra_get.txt
10. param_get.txt
11. segment_size.txt
12. session_sid.txt
13. session_spid.txt
14. shared_pool_free.txt
15. sql_monitor.txt
16. tablespace_used.txt
17. temp_used.txt
18. transaction_get.txt
19. undo_used.txt
20. wait_event.txt
21. wait_event_block.txt
22. wait_event_hash.txt
23. wait_event_sqlid.txt
24. wait_session_hash.txt
25. wait_session_sqlid.txt
26. ash_used.txt
27. sql_profile.txt
28. tabstat.txt
29. sqlinfo_total.txt
30. awr_event_histogram.txt
31. ash_top_sql_event.txt
32. sqlhis_awr.txt
33. session_kill.txt
34. redo_switch.txt
select 'rollback force '||''''||local_tran_id||''''||';' "RollBack" from dba_2pc_pending where state='prepared';
select 'exec dbms_transaction.purge_lost_db_entry('||''''||local_tran_id||''''||');' "Purge" from dba_2pc_pending;set linesize 260 pagesize 10000SELECT SQL_PLAN_HASH_VALUE, event, sql_plan_line_id, COUNT(*)FROM dba_hist_active_sess_historyWHERE sql_id = '&SQL_ID' AND sample_time between to_date('&date1', 'yyyymmddhh24miss') and to_date('&date2', 'yyyymmddhh24miss')GROUP BY SQL_PLAN_HASH_VALUE,sql_plan_line_id,eventORDER BY 4 DESC;set linesize 220 pagesize 1000col begin_interval_time for a30col end_interval_time for a30col stat_name for a40WITH sysstat AS (SELECT ss.instance_number inst_id, sn.begin_interval_time begin_interval_time, sn.end_interval_time end_interval_time, ss.stat_name stat_name, ss.VALUE e_value, LAG(ss.VALUE) OVER(partition by ss.instance_number ORDER BY ss.snap_id) b_value FROM dba_hist_sys_time_model ss, dba_hist_snapshot sn WHERE sn.begin_interval_time >= SYSDATE - &date AND ss.snap_id = sn.snap_id AND ss.dbid = sn.dbid AND ss.instance_number = sn.instance_number AND ss.dbid = (SELECT dbid FROM v$database) and ss.stat_name = 'DB time' and ss.instance_number in (select instance_number from v$instance) )select inst_id, begin_interval_time, end_interval_time, stat_name, round((e_value - b_value)/1000/1000/60) value_min from sysstat order by 2 desc, 3 desc;set linesize 220 pagesize 1000select METRIC_NAME from V$SYSMETRIC_SUMMARY where lower(METRIC_NAME) like '%&metric_name%';
set linesize 220 pagesize 1000col begin_interval_time for a30col end_interval_time for a30col METRIC_NAME for a45select a.SNAP_ID, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME, a.METRIC_NAME, round(a.AVERAGE, 2) AVERAGE, round(a.MAXVAL, 2) MAXVAL from dba_hist_sysmetric_summary a, dba_hist_snapshot b where a.SNAP_ID = b.SNAP_ID and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER and a.INSTANCE_NUMBER in (select instance_number from v$instance) and a.METRIC_NAME in ('&metric_name') and b.BEGIN_INTERVAL_TIME>sysdate-&date order by b.BEGIN_INTERVAL_TIME;set linesize 220 pagesize 10000set long 999999999col MODULE for a40col sql_id for a30col PARSING_SCHEMA_NAME for a20alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'select a.sql_id, a.MODULE, a.PARSING_SCHEMA_NAME, a.last_active_time, a.last_load_time, a.sql_fulltext, b.pool_mb, b.cnt from v$sqlarea a, (select max(sql_id) sql_id, FORCE_MATCHING_SIGNATURE, round(sum(SHARABLE_MEM / 1024 / 1024)) pool_mb, count(1) cnt from v$sqlarea where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 3 order by count(1) desc) b where a.sql_id = b.sql_id order by cnt desc;declare v_address_hash varchar2(128);begin select address||', '||hash_value into v_address_hash from v$sqlarea where sql_id = '&SQL_ID';sys.dbms_shared_pool.purge(v_address_hash, 'C');end;/set linesize 260set long 999999set pagesize 1000select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&owner')) FROM DUAL;set linesize 220 pagesize 10000alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';col table_owner for a20col table_name for a30col partition_name for a20col subpartition_name for a20select a.*,sysdate from dba_tab_modifications a where table_name=upper('&table_name');set echo offset lines 300set pagesize 1000col reclaimable for a20COL used for a20COL QUOTA FOR A20COL NAME FOR A30col used1 for 99999 heading 'USED%';prompt "RECOVERY FILE DEST AND SIZE"SELECT substr(name, 1, 30) name, round(space_limit/1024/1024)||'M' AS quota, round(space_used/1024/1024)||'M' AS used,round(100*space_used/space_limit) used1, round(space_reclaimable/1024/1024)||'M' AS reclaimable, number_of_files AS files FROM v$recovery_file_dest /
Select file_type, percent_space_used,percent_space_reclaimable,number_of_files as "number" from v$flash_recovery_area_usage/set linesize 220 pagesize 1000col ksppinm for a40col ksppstvl for a40col ksppdesc for a100select a.ksppinm, a.ksppdesc,b.ksppstvl,a.inst_id from sys.x$ksppi a, sys.x$ksppcv b where upper(a.ksppinm) like upper('%¶m%') and a.indx = b.indx order by a.ksppinm;col owner for a15col segment_name for a29col partition_name for a30col tablespace_name for a29col size_m for 999,999,999col blocks for 999,999,999select owner,segment_name, partition_name,tablespace_name,bytes/1024/1024 size_m,blocks from dba_segments where segment_name=UPPER('&segment_name') order by 1;set linesize 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select a.sid, a.username, a.machine, a.module, a.event, a.sql_id, round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets, a.ROW_WAIT_OBJ# object_id, a.BLOCKING_INSTANCE||'_'||a.blocking_session holder, a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final, to_char(LAST_CALL_ET) seconds, a.p1 || '_' || a.p2 || '_' || a.p3 param, b.spid, trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE, substr(c.sql_text,0,6) sql_text from v$session a, v$process b,v$sql c where a.paddr = b.addr(+) and a.status = 'ACTIVE' and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and a.wait_class = 'Idle') and a.sql_id=c.sql_id(+) and a.sql_child_number=c.CHILD_NUMBER(+) and a.sid='&SID' order by a.sql_id, a.machine/set linesize 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select a.sid, a.username, a.machine, a.module, a.event, a.sql_id, round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets, a.ROW_WAIT_OBJ# object_id, a.BLOCKING_INSTANCE||'_'||a.blocking_session holder, a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final, to_char(LAST_CALL_ET) seconds, a.p1 || '_' || a.p2 || '_' || a.p3 param, b.spid, trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE, substr(c.sql_text,0,6) sql_text from v$session a, v$process b,v$sql c where a.paddr = b.addr(+) and a.status = 'ACTIVE' and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and a.wait_class = 'Idle') and a.sql_id=c.sql_id(+) and a.sql_child_number=c.CHILD_NUMBER(+) and b.spid='&SPID' order by a.sql_id, a.machine/set linesize 260 pagesize 1000select pool, name, bytes / 1024 / 1024 / 1024 GB from v$sgastat where name like 'free memory' ;SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFFSELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR( SQL_ID => '&SQL_ID', TYPE => 'TEXT', REPORT_LEVEL => 'ALL') AS REPORTFROM dual;--表空间使用率set linesize 220 pagesize 10000COL SIZE_G FOR A15COL FREE_G FOR A15COL USED_PCT FOR A10COL TABLESPACE_NAME FOR A30SELECT d.tablespace_name, to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g, to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g, to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct FROM dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') ORDER BY 4 DESC;
--查询temp表空间使用率：select df.tablespace_name "Tablespace", df.totalspace "Total(MB)", nvl(FS.UsedSpace, 0) "Used(MB)", (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)", round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace FROM dba_TEMP_files GROUP BY tablespace_name) df, (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace FROM gV$temp_extent_pool GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+);--查询temp表空间使用率：select df.tablespace_name "Tablespace", df.totalspace "Total(MB)", nvl(FS.UsedSpace, 0) "Used(MB)", (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)", round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace FROM dba_TEMP_files GROUP BY tablespace_name) df, (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace FROM gV$temp_extent_pool GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+)
--查询实时使用temp表空间的sql_id和sid：
set linesize 260 pagesize 1000col machine for a40col program for a40SELECT se.username, sid, serial#, se.sql_id machine, program, tablespace, segtype, (su.BLOCKS*8/1024/1024) GB FROM v$session se, v$sort_usage su WHERE se.saddr = su.session_addr order by su.BLOCKS desc;
--需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的
--查询历史的temp表空间的使用的SQL_ID
select a.SQL_ID, a.SAMPLE_TIME, a.program, sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB from v$active_session_history a where TEMP_SPACE_ALLOCATED is not null and sample_time between to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and to_date('&date2', 'yyyy-mm-dd hh24:mi:ss') group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM order by 2 asc,4 desc;set linesize 260 pagesize 10000column sess format a21 heading "SESSION"column program format a18column clnt_pid format a8column machine format a25column username format a12column osuser format a13column event format a32column waitsec format 999999column start_time format a18column sql_id format a15column clnt_user format a10column svr_ospid format a10
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy/mm/dd hh24:mi:ss';
set feedback offset echo off
set head offselect chr(9) from dual;select 'Waiting Transactions'||chr(10)||'====================' from dual;set head onselect /*+ rule */ lpad(nvl(s.username,' '),8)||'('||s.sid||','||s.serial#||')' as sess, p.spid as svr_ospid, nvl(osuser,' ') as clnt_user, s.process as clnt_pid, substr((case instr(s.PROGRAM, '@') when 0 then s.program else case instr(s.PROGRAM, '(TNS V1-V3)') when 0 then substr(s.program, 1, instr(s.PROGRAM, '@') - 1) || substr(s.program, instr(s.PROGRAM, '(') - 1) else substr(s.program, 1, instr(s.PROGRAM, '@') - 1) end end), 1, 18) as program, (case when length(s.MACHINE) > 8 then substr(s.machine,1,8)||'~' else s.machine end ) || '('||nvl(s.client_info, 'Unknown IP')||')' as machine, s.sql_id, substr(s.event, 1, 32) as event, s.seconds_in_wait as waitsec from v$transaction t,v$session s,v$process p where t.ses_addr=s.saddr and s.paddr=p.addr order by s.seconds_in_wait, s.program, s.machine;--实时的undo使用量set linesize 220set pagesize 1000col username for a20col module for a40col sql_id for a15col status for a10col machine for a20alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select * from (select start_time, username, s.MACHINE, s.OSUSER, r.name, ubafil, --Undo block address (UBA) filenum ubablk, --UBA block number t.status, (used_ublk * 8192 / 1024) kbtye, used_urec, s1.SQL_ID, substr(s1.SQL_TEXT,0,20) from v$transaction t, v$rollname r, v$session s, v$sqlarea s1 where t.xidusn = r.usn and s.saddr = t.ses_addr and s.sql_id = s1.sql_id(+) order by 9 desc) where rownum <= 10;set linesize 220set pagesize 1000select inst_id,event,count(*) from gv$session a where a.status='ACTIVE' and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle') group by inst_id,event order by a.inst_id,count(*) desc;set linesize 220set pagesize 1000select inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION, count(*) from gv$session a where a.status='ACTIVE' and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle') and upper(event) like upper('%&event%') group by inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION order by inst_id ,count(*) desc, sql_id;set linesize 220set pagesize 1000select a.event, c.plan_hash_value,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets,count(*) from gv$session a,gv$sql c where a.status='ACTIVE' and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle') and a.sql_id=c.sql_id(+) and a.sql_child_number=c.CHILD_NUMBER(+) and a.inst_id=c.inst_id group by a.inst_id,a.event, c.plan_hash_value order by a.inst_id,count(*) desc, c.plan_hash_value;set linesize 220set pagesize 1000select a.inst_id,a.event, a.sql_id,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets, count(*) from gv$session a,gv$sql c where a.status='ACTIVE' and not (a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle') and a.sql_id=c.sql_id(+) and a.sql_child_number=c.CHILD_NUMBER(+) and a.inst_id=c.inst_id group by a.inst_id,a.event, a.sql_id order by a.inst_id,count(*) desc, a.sql_id;set linesize 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select a.sid, a.username, a.machine, a.module, a.event, c.plan_hash_value, round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets, a.ROW_WAIT_OBJ# object_id, a.BLOCKING_INSTANCE||'_'||a.blocking_session holder, a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final, to_char(LAST_CALL_ET) seconds, a.p1 || '_' || a.p2 || '_' || a.p3 param, b.spid, trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE, substr(c.sql_text,0,6) sql_text from v$session a, v$process b,v$sql c where a.paddr = b.addr(+) and a.status = 'ACTIVE' and a.sql_id=c.sql_id(+) and a.sql_child_number=c.CHILD_NUMBER(+) and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and a.wait_class = 'Idle') order by c.plan_hash_value, a.machine/set linesize 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';select a.sid, a.username, a.machine, a.module, a.event, a.sql_id, round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets, a.ROW_WAIT_OBJ# object_id, a.BLOCKING_INSTANCE||'_'||a.blocking_session holder, a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final, to_char(LAST_CALL_ET) seconds, a.p1 || '_' || a.p2 || '_' || a.p3 param, b.spid, trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE, substr(c.sql_text,0,6) sql_text from v$session a, v$process b,v$sql c where a.paddr = b.addr(+) and a.status = 'ACTIVE' and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and a.wait_class = 'Idle') and a.sql_id=c.sql_id(+) and a.sql_child_number=c.CHILD_NUMBER(+) order by a.sql_id, a.machine/因篇幅限制，就不在此一一展示了，大家可通过下面链接下载打包好的脚本。
资源下载：https://www.modb.pro/download/43926（复制到浏览器中打开或者点击“阅读原文”立即下载）
作者
邓秋爽，云和恩墨资深技术专家，有超过5年超大型数据库专业服务经验，擅长oracle 数据库优化、SQL优化和troubleshooting。
联系客服
