Database Reference
In-Depth Information
Status Table
Every time you start a Data Pump job, a status table is automatically created in the account of the user running the job.
For export jobs the table name depends on what type of export job you're running. The table is named with the format
SYS_<OPERATION>_<JOB_MODE>_NN
, where
OPERATION
is either
EXPORT
or
IMPORT
.
JOB_MODE
can be
FULL
,
SCHEMA
,
TABLE
,
TABLESPACE
, and so on.
Here is an example of querying the status table for particulars about a currently running job:
select name, object_name, total_bytes/1024/1024 t_m_bytes
,job_mode
,state ,to_char(last_update, 'dd-mon-yy hh24:mi')
from SYS_EXPORT_TABLE_01
where state='EXECUTING';
Interactive Command Mode Status
A quick way to verify that Data Pump is running a job is to attach in interactive command mode and issue a
STATUS
command; for example,
$ impdp mv_maint/foo attach=SYS_IMPORT_TABLE_04
Import> status
Here is some sample output:
Job: SYS_IMPORT_TABLE_04
Operation: IMPORT
Mode: TABLE
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
You should see a state of
EXECUTING
, which indicates that the job is actively running. Other items to inspect in the
output are the number of objects and bytes processed. Those numbers should increase as the job progresses.
OS Utilities
You can use the
ps
OS utility to display jobs running on the server. For example, you can search for master and worker
processes, as follows:
$ ps -ef | egrep 'ora_dm|ora_dw' | grep -v egrep
Here is some sample output:
oracle 29871 717 5 08:26:39 ? 11:42 ora_dw01_STAGE
oracle 29848 717 0 08:26:33 ? 0:08 ora_dm00_STAGE
oracle 29979 717 0 08:27:09 ? 0:04 ora_dw02_STAGE
If you run this command multiple times, you should see the processing time (seventh column) increase for one
or more of the current jobs. This is a good indicator that Data Pump is still executing and doing work.
