Oracle 10g Datapump Session Wait Information

set lines 150 pages 100 numwidth 7
col program for a30
col username for a10
col spid for a7
col date for a20
col job_name for a20
col STATUS for a10

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.serial#,

s.status, s.username, d.job_name, p.spid, p.pid

from v$session s, v$process p, dba_datapump_sessions d

where p.addr=s.paddr and s.saddr=d.saddr; 2 3 4



DATE PROGRAM SID SERIAL# STATUS USERNAME JOB_NAME SPID PID

-------------------- ------------------------------ ------- ------- ---------- ---------- -------------------- ------- -------

2010-07-02 10:24:39 oracle@xxx-cccora-01tst (DM00) 119 921 ACTIVE SYS SYS_IMPORT_TABLE_08 19832 40

2010-07-02 10:24:39 oracle@xxx-cccora-01tst (DW01) 124 4 ACTIVE SYS SYS_IMPORT_TABLE_08 19834 44




-- In SQL*Plus, activate SQL tracing with DBMS_SYSTEM and SID/SERIAL#
-- Syntax: DBMS_SYSTEM.SET_EV([SID],[SERIAL#],[EVENT],[LEVEL],'')

-- Example to SQL_TRACE Worker process with level 4 (Bind values):
execute sys.dbms_system.set_ev(119,921,10046,4,'');

-- and stop tracing:
execute sys.dbms_system.set_ev(119,921,10046,0,'');


-- Example to SQL_TRACE Master Control process with level 8 (Waits):
execute sys.dbms_system.set_ev(119,921,10046,8,'');

-- and stop tracing:
execute sys.dbms_system.set_ev(119,921,10046,0,'');

SQL> execute sys.dbms_system.set_ev(119,921,10046,12,'');
PL/SQL procedure successfully completed.
SQL> execute sys.dbms_system.set_ev(124,4,10046,12,'');
PL/SQL procedure successfully completed.


-- Activate SQL tracing database wide,
-- Be careful: all processes will be traced !


CONNECT / as sysdba
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';


- Start the Import/export Data Pump or Import Data Pump job, e.g.:

impdp system/XXXX DIRECTORY=dir DUMPFILE=pumpuat_1405.dmp REMAP_SCHEMA=scott:test TABLES=user_site LOGFILE=user_site.log


-- Unset event immediately after Data Pump job ends:

ALTER SYSTEM SET EVENTS '10046 trace name context off';


To Check session trace is on ?

SQL> select sid,serial#,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS from v$session;

SID SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
------- ------- ------------------------ --------------- ---------------
119 921 ENABLED TRUE TRUE <<<<<<<<---------
124 4 ENABLED TRUE TRUE <<<<<<<<---------
127 218 DISABLED FALSE FALSE
134 636 DISABLED FALSE FALSE
140 199 DISABLED FALSE FALSE
147 4 DISABLED FALSE FALSE