Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Display Data Pump Export/Import history

Oracle Database Tips by Donald BurlesonMarch 27, 2015

Question: I need a v$ view or other method for displaying the history of export and import jobs on my database.  Are there any data dictionary views that keep a historical record of export/import activity?  Can I use Oracle auditing to get historical values for Data Pump jobs?

Answer:  The data dictionary does not, by default, provide a way to track Data Pump export/import jobs.  However, if you add the undocumented parameters keep_master=y  and metrics=y directives to your Data Pump expdp or impdp command, then a master table where data pump keeps its metadata is retained in the data dictionary in a table named myowner.sys_export_schema_01.

SET serveroutput on
SET lines 100
DECLARE
job1 NUMBER;
BEGIN
job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_01','MYOWNER');
DBMS_DATAPUMP.STOP_JOB (job1);
END;
/

Here is an example invocation of a Data Pump export with keep_master=y:

c:\impdp directory=DATA_PUMP_DIR job_name=sys_export_schema_01 full=y metrics=y keep_master=y

Using metrics=y will include additional logging information about the number of objects and the time it took to process them in the log=myowner.log_file parameter.

 c:\impdp directory=DATA_PUMP_DIR logfile=sys_export_schema_01.log full=y metrics=y

Here is a sample query that can be run if you use keep_master=y:

select
   tab_owner,
   tab_name,
   completed_rows,
   tab_size,
   elapsed_time  
from (select
         base_object_schema,
         object_name,              
        (lag(object_name, 1) over(order by process_order)) as TAB_NAME,
        (lag(base_object_schema, 1) over(order by process_order)) as TAB_OWNER,
         completed_rows, 
        (dump_length / (1024 * 1024 * 1024)) || 'GB' TAB_SIZE,               
         elapsed_time          
     from
         myowner.sys_export_schema_01
     where
         object_type_path = 'SCHEMA_EXPORT/TABLE/TABLE_DATA'
     and
         process_order >= 1)  
where
   object_name is null;

At the heart of the Data Pump operation is the Master Table.  This table is created at the beginning of a Data Pump operation and is dropped at the end of the successful completion of a Data Pump operation. The Master Table can also be dropped if the job is killed using the kill_job interactive command. If a job is stopped using the stop_job interactive command or if the job is terminated unexpectedly, the Master Table will be retained. 

The keep_master parameter can be set to Y to retain the Master Table at the end of a successful job for debugging purposes.  The name of the Master Table is the same as the Data Pump job name and has the following columns:

SQL> desc SYS_ESTIMATE_SCHEMA_01

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 PROCESS_ORDER                                      NUMBER
 DUPLICATE                                          NUMBER
 DUMP_FILEID                                        NUMBER
 DUMP_POSITION                                      NUMBER
 DUMP_LENGTH                                        NUMBER
 DUMP_ALLOCATION                                    NUMBER
 COMPLETED_ROWS                                     NUMBER
 ERROR_COUNT                                        NUMBER
 ELAPSED_TIME                                       NUMBER
 OBJECT_TYPE_PATH                                   VARCHAR2(200)
 OBJECT_PATH_SEQNO                                  NUMBER
 OBJECT_TYPE                                        VARCHAR2(30)
 IN_PROGRESS                                        CHAR(1)
 OBJECT_NAME                                        VARCHAR2(500)
 OBJECT_SCHEMA                                      VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 FLAGS                                              NUMBER
 COMPLETION_TIME                                    DATE
 OBJECT_TABLESPACE                                  VARCHAR2(30)
 SIZE_ESTIMATE                                      NUMBER
 OBJECT_ROW                                         NUMBER
 PROCESSING_STATE                                   CHAR(1)
 PROCESSING_STATUS                                  CHAR(1)
 BASE_OBJECT_TYPE                                   VARCHAR2(30)
 BASE_OBJECT_NAME                                   VARCHAR2(30)
 BASE_OBJECT_SCHEMA                                 VARCHAR2(30)
 PARALLELIZATION                                    NUMBER
 UNLOAD_METHOD                                      NUMBER
 GRANULES                                           NUMBER
 SCN                                                NUMBER
 DOMAIN_INDEX                                       VARCHAR2(30)
 DOMAIN_INDEX_SCHEMA                                VARCHAR2(30)
 GRANTOR                                            VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 VALUE_T                                            VARCHAR2(4000)
 VALUE_N                                            NUMBER
 IS_DEFAULT                                         NUMBER
 FILE_TYPE                                          NUMBER
 USER_DIRECTORY                                     VARCHAR2(4000)
 USER_FILE_NAME                                     VARCHAR2(4000)
 FILE_NAME                                          VARCHAR2(4000)
 EXTEND_SIZE                                        NUMBER
 FILE_MAX_SIZE                                      NUMBER
 EXTEND_ACTIVE                                      NUMBER
 OVERFLOW_TO                                        NUMBER
 PROCESS_NAME                                       VARCHAR2(30)
 LAST_UPDATE                                        DATE
 WORK_ITEM                                          VARCHAR2(30)
 NON_TRANSACTIONAL                                  CHAR(1)
 OBJECT_NUMBER                                      NUMBER
 COMPLETED_BYTES                                    NUMBER
 TOTAL_BYTES                                        NUMBER
 METADATA_IO                                        NUMBER
 DATA_IO                                            NUMBER
 CUMULATIVE_TIME                                    NUMBER
 OLD_VALUE                                          VARCHAR2(4000)
 SEED                                               NUMBER
 LAST_FILE                                          NUMBER
 USER_NAME                                          VARCHAR2(30)
 OPERATION                                          VARCHAR2(30)
 JOB_MODE                                           VARCHAR2(30)
 VERSION                                            NUMBER
 DB_VERSION                                         VARCHAR2(30)
 STATE                                              VARCHAR2(30)
 PHASE                                              NUMBER
 GUID                                               RAW(16)
 START_TIME                                         DATE
 BLOCK_SIZE                                         NUMBER
 METADATA_BUFFER_SIZE                               NUMBER
 DATA_BUFFER_SIZE                                   NUMBER
 DEGREE                                             NUMBER
 LANGUAGE                                           VARCHAR2(30)
 PLATFORM                                           VARCHAR2(100)
 ABORT_STEP                                         NUMBER
 INSTANCE                                           VARCHAR2(16)

The Master Table is used to track the detailed progress information of a Data Pump job:

* The current set of dump files.

* The current state of every object exported or imported and their locations in the dump file set.

* The job's user-supplied parameters.

* The status of every worker process.

* The state of current job status and restart information.

The Master Table is created in the schema of the current user running the Pump Dump export or import, and it keeps tracks of lots of detailed information.  As a result, this table can take up a significant amount of storage space.

Tip: If an export or an import job is running on a database with a large number of database objects, make sure the schema user has sufficient tablespace quota.

The Master Table is the key to Data Pump's restart capability in the event of a planned or unplanned job stoppage.

===================================================
Another approach is to create a logfile for the Data Pump job and map the OS export log file as an external table.  These log files can be kept as historical records of export/import activity and read using SQL:

set oracle_sid=youroraclesid
 
set uname=yourschema
set upass=yourpassword
 
set dt=%date:/=%
set exp_logfile=dailyexport_%dt:~3%.log
 
expdp %uname%/%upass% schemas=%uname% directory=test_dir dumpfile=test.dmp logfile=%exp_Logfile%
 
(
echo.drop table %uname%.temp_expdp_log;
echo.create table %uname%.temp_expdp_log ^(line varchar2^(400^) ^)
echo.  organization external
echo.  ^(type ORACLE_LOADER
echo.  default directory test_dir
echo.  access parameters
echo.  ^(records delimited by newline
echo.  ^)
echo.  location ^('%exp_logfile%'^)
echo. ^) reject limit unlimited;
) > create_extlog.sql
 
sqlplus %uname%/%upass% @create_extlog.sql

 

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster