In this Document
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
GOAL
The purpose of this document is to demonstrate how to monitor the progress of Datapump jobs.
SOLUTION
The progress can be monitored from either the Datapump client, or from within the database itself. For the demonstration it is assumed that EXPDP is being run. The same applies to IMPDP as well, but for all references to export then import should be replaced.
The progress can be monitored by:
- The exporting session itself:
Press the <Ctrl>+C keys, and from the prompt appearing, issue the STATUS command, like in:
Export> status
Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: <PATH>\<DUMP_NAME>.DMP
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: <SCHEMA_NAME>
Object Name: <OBJECT_NAME>
Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Completed Objects: 78
Worker Parallelism: 1
This provides details on the status of the DM and DW processes involved in the export operation.
- An exporting session attaching to the current Datapump job:
When the job name of the EXPDP session is known, then the same information as above can be obtained from a new EXPDP session, as in:
SQL> select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME
------------------------------ ------------------------------
OPERATION JOB_MODE
------------------------------ ------------------------------
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ---------- ----------------- -----------------
SYS SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3
-- Then from a command line session:
EXPDP ATTACH=SYS_EXPORT_FULL_01
Job: SYS_EXPORT_FULL_01
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 6F9FF0B3CF274562A56417E82A935987
Start Time: Thursday, 12 July, 2012 12:56:52
Mode: FULL
Instance: <SID>
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** AS SYSDBA directory=d full=y
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: <PATH>\<DUMP_NAME>.DMP
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: <SCHEMA_NAME>
Object Name: <OBJECT_NAME>
Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Completed Objects: 320
Worker Parallelism: 1
- Queries against the data dictionary:
The DBA_DATAPUMP_JOBS view shows whether EXPDP or IMPDP jobs are still running, or have terminated with either a success or failure status. The data dictionary view V$SESSION_LONGOPS can be queried to get more detailed information on how much work is done already by the exporting job. E.g. the following queries can be used for this:
- The status of the job:
SQL> select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME
------------------------------ ------------------------------
OPERATION JOB_MODE
------------------------------ ------------------------------
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ---------- ----------------- -----------------
SYS SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3
- The percentage of work done, like in:
SQL> SELECT b.username, a.sid, b.opname, b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
FROM v$session_longops b, v$session a
WHERE a.sid = b.sid ORDER BY 6;
USERNAME SID
------------------------------ ----------
OPNAME
----------------------------------------------------------------
TARGET
----------------------------------------------------------------
%DONE TIME_REMAINING START_TIME
----------------------------------------- -------------- -------------------
SYS 140
SYS_EXPORT_FULL_01
0% 2012/07/12 12:57:21
- The percentage of work done and the current status of the Datapump job, like in:
SQL> SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name
AND sl.sofar != sl.totalwork;
SID SERIAL# SOFAR TOTALWORK OWNER_NAME
---------- ---------- ---------- ---------- ------------------------------
STATE JOB_MODE
------------------------------ ------------------------------
140 9 0 67 SYS
EXECUTING FULL
4. The following Query displays the amount of work done so far:
SQL> SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,sl.totalwork, dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name;
For Example:
SQL> SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,sl.totalwork, dp.owner_name, dp.state, dp.job_mode
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name 2 3 ;
SID SERIAL# SOFAR MESSAGE TOTALWORK OWNER_NAME STATE JOB_MODE
---------- ---------- ---------- -------------------- ---------- ------------------------------ ------------------------------
235 39132 25091 SYS_IMPORT_TABLE_01: 57907 SYS XXX EXECUTING TABLE
IMPORT : 25091 out of 57907 MB done
|