--导出问题分析 --两个时间语句分析，该语句只导出4,059,292 数据，10分钟后数据没有继续导出 Snap Id Snap Time Sessions Cursors/Session Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7 End Snap: 39407 13-Jul-14 20:20:07 556 1.7 Elapsed: 109.58 (min--导出问题分析--两个时间语句分析，该语句只导出4,059,292 数据，10分钟后数据没有继续导出Snap Id Snap Time Sessions Cursors/Session Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7 End Snap: 39407 13-Jul-14 20:20:07 556 1.7 Elapsed: 109.58 (mins) DB Time: 16.76 (mins) Snap Id Snap Time Sessions Cursors/Session Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7 End Snap: 39398 13-Jul-14 18:50:37 557 1.7 Elapsed: 20.09 (mins) DB Time: 3.51 (mins) Snap Id Snap Time Sessions Cursors/Session Begin Snap: 39396 13-Jul-14 18:30:32 558 1.7 End Snap: 39417 13-Jul-14 22:00:18 559 1.7 Elapsed: 209.77 (mins) DB Time: 27.15 (mins) Begin Snap Time Sessions Cursors/Session 39396 13-Jul-14 18:30:32 558 1.7 End Snap: 39397 13-Jul-14 18:40:34 557 1.7 Elapsed: 10.03 (mins) DB Time: 1.64 (mins)--语句没执行完毕# Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID 1 2193842017 7,218 0 39397 39397--问题时候的导出资源使用Stat Name Statement Total Per Execution % Snap Total Elapsed Time (ms) 7,218 0.72 CPU Time (ms) 3,035 0.35 Executions 0 Buffer Gets 139,102 0.34 Disk Reads 0 0.00 Parse Calls 0 0.00 Rows 4,059,292 -------------------------其实我获取两个 awrsqlrpt报告分析，两个时间，也就是18点50 以后，没有数据导出User I/O Wait Time (ms) 0 Cluster Wait Time (ms) 0 Application Wait Time (ms) 0 Concurrency Wait Time (ms) 0 Invalidations 0 Version Count 2 Sharable Mem(KB) 44--22点导出时候资源使用Snap Id Snap Time Sessions Cursors/Session Begin Snap: 39421 13-Jul-14 22:40:22 557 1.7 End Snap: 39422 13-Jul-14 22:50:23 558 1.7 Elapsed: 10.02 (mins) DB Time: 1.38 (mins) Stat Name Statement Total Per Execution % Snap Total Elapsed Time (ms) 18,405 18,404.69 22.24 CPU Time (ms) 7,555 7,554.87 10.43 Executions 1 Buffer Gets 354,583 354,583.00 35.03 Disk Reads 0 0.00 0.00 Parse Calls 1 1.00 0.05 Rows 10,555,042 10,555,042.00 ---一共导出怎么多数据（可以正常导出）
User I/O Wait Time (ms) 0 Cluster Wait Time (ms) 0 Application Wait Time (ms) 0 Concurrency Wait Time (ms) 0 Invalidations 0 Version Count 2 Sharable Mem(KB) 44
--以上问题给予语句分析：
SQL Id SQL Text bcjy9a9sp1uw9 SELECT /*+NESTED_TABLE_GET_REFS+*/ "KDTA_HIS"."H_ACK_DIVIDEND".* FROM "KDTA_HIS"."H_ACK_DIVIDEND"--分析：导出发生意外，中止或者等待，或者LMON 引起latch
--会话等待事件分析：
SQL> select substrb(session_id,1,6)||'.'||substrb(session_serial#,1,4),2 substrb(event,1,15),3 BLOCKING_SESSION,4 to_char(SAMPLE_TIME, 'yyyymmdd hh24miss'),5 INSTANCE_NUMBER,6 sql_id,7 (select username 8 from dba_users b 9 where b.user_id = a.USER_ID 10 and rownum = 1) username,11 substrb(program,1,10) 12 from DBA_HIST_ACTIVE_SESS_HISTORY a 13 where (SAMPLE_TIME >=14 to_date('2014-07-13 01:00:00', 'yyyy-mm-dd hh24:mi:ss') and 15 SAMPLE_TIME 16 to_date('2014-07-14 01:10:00', 'yyyy-mm-dd hh24:mi:ss')) 17 and a.module like '%exp%'18 order by session_id,SAMPLE_TIME;SUBSTRB(SES SUBSTRB(EVENT,1 BLOCKING_SESSION TO_CHAR(SAMPLE_ INSTANCE_NUMBER SQL_ID USERNAME SUBSTRB(PR----------- --------------- ---------------- --------------- --------------- ------------- ------------------------------ ----------7638.4126 20140713 184020 1 88jm6j85t5b8z KDTA_HIS exp.exe 7638.4126 latch free 529 20140713 184151 1 KDTA_HIS exp.exe--------7638.4126 20140713 184643 1 g4kubvga4gnxc KDTA_HIS exp.exe 7638.4126 SQL*Net more da 20140713 185145 1 dvjmxz20wf8xz KDTA_HIS exp.exe--529 会话分析SQL> select substrb(session_id,1,6)||'.'||substrb(session_serial#,1,4),2 substrb(event,1,15),3 BLOCKING_SESSION,4 to_char(SAMPLE_TIME, 'yyyymmdd hh24miss'),5 INSTANCE_NUMBER,6 sql_id,7 substrb(program,1,30) 8 from DBA_HIST_ACTIVE_SESS_HISTORY a 9 where (SAMPLE_TIME >=10 to_date('2014-07-13 18:30:00', 'yyyy-mm-dd hh24:mi:ss') and 11 SAMPLE_TIME 12 to_date('2014-07-13 18:45:00', 'yyyy-mm-dd hh24:mi:ss')) 13 and a.session_id=529 14 order by session_id,SAMPLE_TIME;SUBSTRB(SES SUBSTRB(EVENT,1 BLOCKING_SESSION TO_CHAR(SAMPLE_ INSTANCE_NUMBER SQL_ID SUBSTRB(PROGRAM,1,30)----------- --------------- ---------------- --------------- --------------- ------------- ------------------------------529.1 20140713 183204 2 oracle@KXYEB02 (LMON) 529.1 20140713 183215 2 oracle@KXYEB02 (LMON) 529.1 20140713 183609 1 oracle@KXYEB01 (LMON) 529.1 20140713 184151 1 oracle@KXYEB01 (LMON)------------529.1 20140713 184218 2 oracle@KXYEB02 (LMON) 529.1 20140713 184332 1 oracle@KXYEB01 (LMON)--dump systemstate 已经无法分析当时数据--分析awr报告，没有发现latch 问题-- 下次遇到这个问题的是及时进行一下操作--1 登录数据库sqlplus /nolog conn / as sysdba oradebug setmypid oradebug dump systemstate 10 oradebug tracefile_name--2 可以的话再执行一次1 获取process id ps -ef|grep expdp id 2 10046分析SQL> select to_char(a.last_analyzed,'yyyymmdd hh24miss') from dba_tables a where table_name='H_ACK_DIVIDEND';TO_CHAR(A.LAST_---------------20140713 181130 SQL>异常中止或者lmon引起等待挂起，根源原因分析不出来!