A simple way to gain insight into the status of a Data Pump job is to look into a few views maintained within the Oracle instance the Data Pump job is running. These views are DBA_DATAPUMP_JOBS, DBA_DATAPUMP_SESSIONS, and V$SESSION_LOGOPS. These views are critical in the monitoring of your export jobs so, as we will see in a later article, you can attach to a Data Pump job and modify the execution of the that job. DBA_DATAPUMP_JOBS SQL> select * from dba_datapump_jobs OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS ---------- ---------------------- ---------- ---------- ------------- --------- ----------------- JKOOP SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 1 JKOOP SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA EXECUTING 1 1 DBA_DATAPUMP_SESSIONS SQL> SELECT * FROM DBA_DATAPUMP_SESSIONS V$SESSION_LONGOPS SQL> select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS USERNAME OPNAME TARGET_DES SOFAR TOTALWORK MESSAGE -------- -------------------- ---------- ----- ---------- ------------------------------------------------ JKOOP SYS_EXPORT_FULL_01 EXPORT 132 132 SYS_EXPORT_FULL_01:EXPORT:132 out of 132 MB done JKOOP SYS_EXPORT_FULL_01 EXPORT 90 132 SYS_EXPORT_FULL_01:EXPORT:90 out of 132 MB done JKOOP SYS_EXPORT_SCHEMA_01 EXPORT 17 17 SYS_EXPORT_SCHEMA_01:EXPORT:17 out of 17 MB done JKOOP SYS_EXPORT_SCHEMA_01 EXPORT 19 19 SYS_EXPORT_SCHEMA_01:EXPORT:19 out of 19 MB done SQL> select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode SID SERIAL# SOFAR TOTALWORK OWNER_NAME STATE JOB_MODE Monitor at the OS - Do a "ps -ef" on the data pump process and watch it consume CPU. You can also monitor the data pump log file with the "tail -f", command, watching the progress of the import in real time. If you watch the import log, be sure to include the feedback=1000 parameter to direct import to display a dot every 1,000 lines of inserts. Monitor with the data pump views - The main view to monitor import jobs are dba_datapump_jobs and dba_datapump_sessions. Monitor with longops - You can query the v$session_longops to see the progress of data pump, querying the sofar and totalwork columns. select
sid,
serial# select
sid,
serial#,
sofar,
totalwork select x.job_name,b.state,b.job_mode,b.degree The gadget spec URL could not be found The following are the major new features that provide this increased performance, as well as enhanced ease of use:
The gadget spec URL could not be found The DBA_DATAPUMP_JOBS and USER_DATAPUMP_JOBS ViewsThe Table 1-1 describes the columns in the Table 1-1 DBA_DATAPUMP_JOBS View and USER_DATAPUMP_JOBS View
The DBA_DATAPUMP_SESSIONS ViewThe Table 1-2 describes the columns in the Table 1-2 The DBA_DATAPUMP_SESSIONS View
Monitoring the Progress of Executing JobsData Pump operations that transfer table data (export and import) maintain an entry in the
The
|| Usage Notes: || This script is provided to demonstrate various features of Oracle 10g's || new DataPump and should be carefully proofread before executing it against || any existing Oracle database to insure that no potential | The gadget spec URL could not be found The gadget spec URL could not be found |
DBA's Home Page - A website for Oracle, Greenplum and PostgreSQL DBA's > Oracle 11g New Features >