Monitor progress of Oracle Data Pump impdp

This blog post is a quick brain dump. I realized that I have so many “blog posts to write” that I need to dump the ideas or information much quicker. So, feel free to comment and correct. These queries may be useful when you run a larger import, and you would like to monitor progress of Oracle Data Pump impdp.

Photo by George Vidos on Unsplash

Monitor Data Pump progress during impdp

To monitor the progress of Data Pump during impdp you can use the following queries.

What object types are left?

select unique object_type_seqno, object_type
from system.sys_import_full_01
where process_order > 0 AND processing_state = ‘R’
and processing_status = ‘C’;

What’s left for the current object?

select object_schema, object_name
from system.sys_import_full_01
where process_order > 0 and processing_state = 'R' and processing_status = 'C' and object_path_seqno = 103;

Get metrics on exported data

How many objects are already processed, how many are still to be processed – and how many are left out?

select sum(dump_orig_length), processing_state
from "SYSTEM"."SYS_IMPORT_FULL_01“
where process_order > 0 and duplicate = 0 and object_type = 'TABLE_DATA'
group by processing_state;

Get metrics on imported data

How many objects are already processed, how many are still to be processed – and how many are left out?

select sum(dump_orig_length), processing_state
from "SYSTEM"."SYS_IMPORT_FULL_01“
where process_order > 0 and duplicate = 0 and object_type = 'TABLE_DATA
group by processing_state;

We have an enhancement in mind already making these available as part of a “progress” command in Data Pump’s job interface.

–Mike