Hi all,
Everyone will come across a situation where export/import jobs will run for long and we need to findout what is the current status, expected completion time or what is the current activity of the job.
Current expdp/impdp jobs:
using dba_datapump_sessions and dba_datapump_jobs
SELECT DISTINCT dp.job_name, dp.session_type, s.inst_id, s.SID, s.serial#,
s.username, s.inst_id, s.event, s.sql_id, q.sql_text,
dj.operation, dj.state
FROM gv$session s,
dba_datapump_sessions dp,
dba_datapump_jobs dj,
gv$sql q
WHERE s.saddr = dp.saddr
AND dp.job_name = dj.job_name
AND s.sql_id = q.sql_id
AND s.inst_id IN (1, 2, 3) –and session_type=’MASTER’
ORDER BY s.inst_id;
Note: I have commented the ‘MASTER’ session type if you need to know only master process, please uncomment it.
Monitor the datapump jobs if it has any errors:
select name,status,error_msg from dba_resumable;
Using v$session_longops & v$session
select a.inst_id,a.service_name, a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow
from gv$session a , gv$sql b, gv$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and status = ‘ACTIVE’ and a.username=’&USERNAME’
select * from gv$session_longops where username=’NIT_REP’ order by LAST_UPDATE_TIME desc
If we want to know what is the percent % of Job is completed, please attach the particular job_name with expdp/impdp and check the status.
[oracle@node12b ~]$ impdp attach=DB_DATA_RESTORENOV18_SE
Import: Release 11.2.0.2.0 – Production on Mon Nov 18 17:46:03 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: PREPRD
Object Name: LRL_LRL
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 3,442
Completed Rows: 870,779
Completed Bytes: 6,758,280,104
Percent Done: 38
Worker Parallelism: 1
Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: PREPRD
Object Name: SEC_MOVEMENT
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 15
Total Objects: 3,442
Completed Rows: 625,191
Completed Bytes: 145,685,488
Percent Done: 47
Worker Parallelism: 1
Worker 3 Status:
Process Name: DW02
State: EXECUTING
Object Schema: PREPRD
Object Name: POSITION_MOVEMENT
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 25
Total Objects: 3,442
Completed Rows: 360,001
Completed Bytes: 118,954,880
Percent Done: 27
Worker Parallelism: 1
above DW process will show the Total percent of import job and the what is the current table the import is going on.
Pipe:
-bash-3.2$ cat exp.sh
mknod /tmp/exppipe p
gzip -c //full.dmp.gz &
exp userid=\’/ as sysdba\’ file=/tmp/exppipe full=y compress=n log=xxxx.logn statistics=none
rm -f /tmp/exppipe
oracledbissues 