Data pump status .... is job running ?

col JOB_NAME format a20
col OPERATION format a10
col JOB_MODE format a10
col STATE format a10
Select job_name, operation, job_mode, state from user_datapump_jobs where STATE!='NOT RUNNING'


SQL> r
1* Select job_name, operation, job_mode, state from user_datapump_jobs where STATE!='NOT RUNNING'


JOB_NAME OPERATION JOB_MODE STATE

-------------------- ---------- ---------- ----------
SYS_IMPORT_TABLE_08 IMPORT TABLE EXECUTING


set lines 200 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
col date format a20
col job_name format a20
col status format a10
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;


DATE PROGRAM SID STATUS USERNAME JOB_NAME SPID SERIAL# PID

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

2010-07-13 09:44:21 udi@ln-ora-01tst (TNS V1-V3) 143 INACTIVE SYS SYS_IMPORT_TABLE_13 14130 2 23

2010-07-13 09:44:21 oracle@ln-ora-01tst (DM00) 146 ACTIVE SYS SYS_IMPORT_TABLE_13 14132 60 24

2010-07-13 09:44:21 oracle@ln-ora-01tst (DW01) 140 ACTIVE SYS SYS_IMPORT_TABLE_13 14134 3 25

2010-07-13 09:44:21 oracle@ln-ora-01tst (DW02) 137 ACTIVE SYS SYS_IMPORT_TABLE_13 14136 2 26






-bash-3.00$ impdp "'/ as sysdba'" attach=SYS_IMPORT_TABLE_08

Import: Release 10.2.0.4.0 - 64bit Production on Friday, 02 July, 2010 14:58:09

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_IMPORT_TABLE_08
Owner: SYS
Operation: IMPORT
Creator Privs: TRUE
GUID: 8A5433675F594D74E0440003BA0AE183
Start Time: Thursday, 01 July, 2010 14:10:58
Mode: TABLE
Instance: ccdb
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND '/******** AS SYSDBA' directory=datapump1 DUMPFILE=pumpuat_1405.dmp SCHEMAS=(PROD1,UAT2,ESEARCH)
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND '/******** AS SYSDBA' DIRECTORY=dir DUMPFILE=pumpuat_1405.dmp REMAP_SCHEMA=prod1:test TABLES=user_sites LOGFILE=user_sites01july.log TABLE_EXISTS_ACTION=APPEND
TABLE_EXISTS_ACTION APPEND
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle/backup/pumpuat_1405.dmp

Worker 1 Status:
State: EXECUTING
Object Schema: TEST
Object Name: USER_SITES
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Completed Rows: 239,889,948
Completed Bytes: 10,016,441,128
Percent Done: 100
Worker Parallelism: 1

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