Databases Reference
In-Depth Information
You can also query the
DBA_DATAPUMP_SESSIONS
view for session information via the following query:
select sid, serial#, username, process, program
from v$session s,
dba_datapump_sessions d
where s.saddr = d.saddr;
Here is some sample output, showing that several Data Pump sessions are in use:
SID SERIAL# USERNAME PROCESS PROGRAM
---------- ---------- -------------------- --------------- ----------------------
1049 6451 STAGING 11306 oracle@xengdb (DM00)
1058 33126 STAGING 11338 oracle@xengdb (DW01)
1048 50508 STAGING 11396 oracle@xengdb (DW02)
Database Alert Log
If a job is taking much longer than you expected, look in the database alert log for any messages similar to this:
statement in resumable session 'SYS_IMPORT_SCHEMA_02.1' was suspended due to
ORA-01652: unable to extend temp segment by 64 in tablespace REG_TBSP_3
This message indicates that a Data Pump import job is suspended and is waiting for space to be added to the
REG_TBSP_3
tablespace. After you add space to the tablespace, the Data Pump job automatically resumes processing.
By default a Data Pump job waits 2 hours for space to be added.
■
In addition to writing to the alert log, for each Data pump job, Oracle creates a trace file in the
ADR_HOME/trace
directory. this file contains information such as the session ID and when the job started. the trace file is named with the
following format:
<SID>_dm00_<process_ID>.trc
.
Note
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
