-- -- Usage: -- @dash_wait_chains -- -- Example: -- @dash_wait_chains username||':'||program2||event2 session_type='FOREGROUND' sysdate-1 sysdate -- -- Other: -- This script uses only the DBA_HIST_ACTIVE_SESS_HISTORY view, use -- @ash_wait_chains.sql for accessiong the GV$ ASH view for realtime info -- -------------------------------------------------------------------------------- COL wait_chain FOR A300 WORD_WRAP COL "%This" FOR A6 PROMPT PROMPT -- Display ASH Wait Chain Signatures script v0.8 by Tanel Poder ( https://tanelpoder.com ) WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat), ash AS (SELECT /*+ INLINE QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */ a.* , o.* , SUBSTR(TO_CHAR(a.sample_time, 'YYYYMMDDHH24MISS'),1,13) sample_time_10s -- ASH dba_hist_ samples stored every 10sec , u.username , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n') ELSE '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')' END || ' ' program2 , NVL(a.event||CASE WHEN event like 'enq%' AND session_state = 'WAITING' THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']' WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#') THEN ' ['||NVL((SELECT class FROM bclass WHERE r = a.p3),'undo @bclass '||a.p3)||']' ELSE null END,'ON CPU') || ' ' event2 , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name FROM dba_hist_active_sess_history a , dba_users u , (SELECT object_id,data_object_id,owner,object_name,subobject_name,object_type , owner||'.'||object_name obj , owner||'.'||object_name||' ['||object_type||']' objt FROM dba_objects) o WHERE a.user_id = u.user_id (+) AND a.current_obj# = o.object_id(+) AND sample_time BETWEEN &3 AND &4 ), ash_samples AS (SELECT /*+ INLINE */ DISTINCT sample_time_10s FROM ash), ash_data AS (SELECT /*+ INLINE */ * FROM ash), chains AS ( SELECT /*+ INLINE */ d.sample_time_10s ts , level lvl , session_id sid , REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ' -> [idle blocker '||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT ' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#) = ((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']' ELSE NULL END path -- there's a reason why I'm doing this --, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ') path -- there's a reason why I'm doing this (ORA-30004 :) --, SYS_CONNECT_BY_PATH(&1, ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN '('||d.session_id||')' ELSE NULL END path --, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN ' [sid='||d.session_id||' seq#='||TO_CHAR(seq#)||']' ELSE NULL END path -- there's a reason why I'm doing this (ORA-30004 :) , CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids , CONNECT_BY_ISLEAF isleaf , CONNECT_BY_ISCYCLE iscycle , d.* FROM ash_samples s , ash_data d WHERE s.sample_time_10s = d.sample_time_10s AND d.sample_time BETWEEN &3 AND &4 CONNECT BY NOCYCLE ( PRIOR d.blocking_session = d.session_id AND PRIOR s.sample_time_10s = d.sample_time_10s AND PRIOR d.blocking_inst_id = d.instance_number) START WITH &2 ) SELECT * FROM ( SELECT LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This" , COUNT(*) * 10 seconds , ROUND(COUNT(*) * 10 / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS , path wait_chain , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen , COUNT(DISTINCT sids) num_sids , MIN(sids) , MAX(sids) FROM chains WHERE isleaf = 1 GROUP BY &1 , path ORDER BY COUNT(*) DESC ) WHERE rownum <= 30 /

比如表T生成了两个dump文件（t_1.dmp,t_2.dmp)，就可以考虑如下的方式来加载，黄色部分是对应的dump文件。 CREATE TABLE T_EXT_1 ( id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "EXPDP_LOCATION" LOCATION ( 't_1.dmp' ) ) ; CREATE TABLE T_EXT_2 ( id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "EXPDP_LOCATION" LOCATION ( 't_2.dmp' ) ) ; 对应的脚本如下： 其中在DUMP目录下存放着生成的dump文件，根据动态匹配得到最终生成了几个dump文件，来决定创建几个对应的外部表。 target_owner=`echo "2"|awk−F@′print$1′|awk−F/′print$1′|tr′[a−z]″[A−Z]′‘sourceowner=‘echo"1" |awk -F@ '{print 1}'|awk -F/ '{print $1}'|tr '[a-z]' '[A-Z]'` tab_name=`echo "3"|tr '[a-z]' '[A-Z]'` owner_account=5tmpparallel=‘ls−l../DUMP/{tab_name}_[0-9]*.dmp|wc -l` echo parallel :tmpparallelforiin1..$tmpparallel;doecho\'{tab_name}_i.dmp\' >> tmp_{tab_name}_par_dmp.lst done sed -e '/^/d' tmp_{tab_name}_par_dmp.lst > ../DUMP_LIST/{tab_name}_par_dmp.lst rm tmp_{tab_name}_par_dmp.lst dump_list=`cat ../DUMP_LIST/tabnamepardmp.lst‘print"conn1 set feedback off set linesize 100 col data_type format a30 set pages 0 set termout off SELECT t1.COLUMN_NAME, t1.DATA_TYPE || DECODE ( t1.DATA_TYPE, 'NUMBER', DECODE ( '(' || NVL (TO_CHAR (t1.DATA_PRECISION), '*') || ',' || NVL (TO_CHAR (t1.DATA_SCALE), '*') || ')', '(*,*)', NULL, '(*,0)', '(38)', '(' || NVL (TO_CHAR (t1.DATA_PRECISION), '*') || ',' || NVL (TO_CHAR (t1.DATA_SCALE), '*') || ')'), 'FLOAT', '(' || t1.DATA_PRECISION || ')', 'DATE', NULL, 'TIMESTAMP(6)', NULL, '(' || t1.DATA_LENGTH || ')') ||',' AS DATA_TYPE from all_tab_columns t1 where owner=upper('owneraccount′)ANDtablename=upper(′3' ) order by t1.column_id; "|sqlplus -s /nolog > {tab_name}.temp sed -e '/^/d' -e 's/.//' -e 's/CLOB(4000)/CLOB/g' -e 's/BLOB(4000)/BLOB/g' tabname.temp>../DESCLIST/{tab_name}.desc rm tabname.tempforiin1..$tmpparalleldoecholoadingtable{tab_name} as {tab_name}_EXT_i sqlplus -s 2settimingonsetechoonCREATETABLE{tab_name}_EXT_i(‘cat../DESCLIST/{tab_name}.desc ` ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY 4LOCATION(‘sed−n"{i}p" ../DUMP_LIST/${tab_name}_par_dmp.lst` )); EOF done exit 生成的日志类似下面的格式： loading table T as T_EXT_1 Elapsed: 00:00:01.33 loading table T as T_EXT_2 Elapsed: 00:00:01.30