Oracle impdp running way too long Oracle impdp running way too long docker docker

Oracle impdp running way too long


The simplest way to find blocking locks is to look at the sessions. It's usually best to look for who is blocking, not what is blocking.

select *from gv$sessionwhere blocking_session is not null

The ALTER may be waiting on a recursive SQL statement. Those statement do not always count as "blocking". Run this statement to see what other SQL is running. If you're lucky there will be a statement that has been running almost as long as the ALTER; that will be the next query to investigate:

select elapsed_time/1000000 seconds, gv$sql.*from gv$sqlwhere users_executing > 0order by users_executing desc;

For investigating space issues it is usually better to look at DBA_RESUMABLE instead of specific space sizes. There are a lot of ways for Oracle to run out of space. The tablespace may look fine, but ASM or the OS may be out of space.

select * from dba_resumable;

Based on the updates it looks like the imported data contains Advanced Queues objects. Advanced Queue's have additional import and export restrictions. Everything is "special" with AQ's. Hopefully your organization has someone who specializes in AQs. It wouldn't surprise me if you'll need a create a Support Request for this issue.


The answer most likely lies in the output of your query on v$sql .. specifically the INSERT INTO "SYS". "IMPDP_STATS"

You will notice little use of bind variables ... this code is transferring statistics from the source database one object at a time hardcoding most of the values .. even though this is code written by Oracle's own developers.

The solution is to not export statistics during expdp - e.g. EXCLUDE=STATISTICS in the expdp parfile.

You can gather statistics anew after the import is complete - which has the advantage of ensuring up to date statistics. Alternatively, you could export statistics to tables prior to expdp and impp