Site Logo

IzzySoft


› › Datapump
{itemlist}
 

Datapump

QuickRef for control

Find the job

If you specified the job name when starting the datapump action, you already know it:

expdp system full=y JOB_NAME=EXPORT_FULL

But even if not, you can find it querying the database:

SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME      OPERATION  JOB_MODE   STATE
---------- ------------- ---------- ---------- ------------
SYSTEM     IMPORT_FULL   IMPORT     FULL       EXECUTING

Job details

Now let's see what the datapump is doing – especially when the log does not seem to update. This certainly is the case with very large tables where you want to see the progress – or if the datapump job seems to be stuck:

col object_schema for a20
col object_name for a30
set lines 200
set pages 100

select * from (
select object_schema, object_name,
       round(size_estimate/power(1024,3),1) est_gb,
       to_char(start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time,
       state,
       round(total_bytes / power(1024, 3)) as total_GB,
       work_item
from system.IMPORT_FULL
where object_name is not null
and object_schema is not null
and object_type_path = 'DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA'
order by start_time
) where rownum < 10;

Issues

IMPDP creates huge amounts of UNDO

The most common cause of high UNDO generation during an IMPDP job is that the table pre-exists in the target database and one or more indexes exist on that table. During the import job, those indexes will be maintained for every row inserted. To resolve that problem, ensure that the table that needs to be imported does not pre-exist in the target database, but let import create the table.

This means: do not use TABLE_EXISTS_ACTION=APPEND or TABLE_EXISTS_ACTION=TRUNCATE parameter for the Import Data Pump job unless this is really required.

(from: MOS Note 1670349.1)

This usually effects larger imports only – but „large“ is quite relative here. As a rule of the thumb, if you have a table of a size approaching the size of your undo tablespace, you will probably run into this issue: your import will stall with an ORA-30036 and the undo tablespace full.

Datapump crashed (or was terminated); how to clean up?

This is described in MOS Note 336014.1. Basically, you have to identify the left-overs and remove them:

-- locate datapump jobs
SELECT owner_name, job_name, rtrim(operation) "OPERATION",
       rtrim(job_mode) "JOB_MODE", state, attached_sessions
  FROM dba_datapump_jobs
 WHERE job_name NOT LIKE 'BIN$%'
 ORDER BY 1,2;

-- Ensure that the jobs listed above are not active: status should be 'NOT RUNNING'.
-- Ensure that none of the jobs was just temporarily suspended (using STOP_JOB).
--   (if you e.g. can connect to it via "impdp job_name=<name>" and get a status).

-- Now identify orphan DataPump external tables (as sysdba):
SELECT owner,object_name,object_type, status,
       to_char(created, 'dd-mon-yyyy hh24:mi:ss') created,
       to_char(last_ddl_time, 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
  FROM dba_objects
 WHERE object_name like 'ET$%';

SELECT owner, table_name, default_directory_name, access_type
  FROM dba_external_tables
 ORDER BY 1,2;

-- correlate information given by the two queries to identify temporary external tables
--   of the orphaned datapump jobs. Then drop them:
DROP TABLE <owner>.<table_name> purge;

-- Now, similarly for the master table:
SELECT o.status, o.object_id, o.object_type,
       o.owner||'.'||object_name "OWNER.OBJECT"
  FROM dba_objects o, dba_datapump_jobs j
 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

SELECT table_name, owner
  FROM dba_external_tables;

-- For jobs that were stopped in the past and won't be restarted anymore, delete the master table:
DROP TABLE <owner>.<table_name>;
PURGE dba_recyclebin;

-- Re-run the query on dba_datapump_jobs and dba_objects above to make sure they're all gone.
-- Repeat steps if needed.

Should the crashed/terminated datapump process leave you with unusually heavy UNDO processing, you can check Analyzing heavy UNDO operations for how to deal with this.

Other notes

A full export does not, by default, export system schemas that contain Oracle-managed data and metadata. Examples of system schemas that are not exported by default include SYS, ORDSYS, and MDSYS.

(Oracle 12c documentation)

Hence it should be safe to do a full import on a higher database version – e.g. importing a full dump made with 12c into an Oracle 19c database.

Sources and Further Readings

2020-11-18