i am using datapump in Oracle 10g to archive old partitions from the main schema to another schema.
i notice that when dbms_datapump.open is called that a new table is created by dbms_datadpump for internal purposes. This is verified in the oracle documentation
Does anybody know whether this table can be removed by a "cleanup" Oracle dbms_datapump call, or whether it has to be cleaned up manually.
can confirm thats what you do
v_job_handle:= DBMS_DATAPUMP.OPEN('EXPORT', 'TABLE', NULL, v_job_name);
-- Set parallelism to 1 and add file
DBMS_DATAPUMP.ADD_FILE(v_job_handle, v_job_name || '_' || v_partition.partition_name || '.dmp', 'PARTITION_DUMPS');
-- Apply filter to process only a certain partition in the table
DBMS_DATAPUMP.METADATA_FILTER(v_job_handle, 'SCHEMA_EXPR', 'IN(''SIS_MAIN'')');
DBMS_DATAPUMP.METADATA_FILTER(v_job_handle, 'NAME_EXPR', 'LIKE ''' || t_archive_list(i) || '''');
DBMS_DATAPUMP.DATA_FILTER(v_job_handle, 'PARTITION_EXPR', 'IN (''' || v_partition.partition_name || ''')', t_archive_list(i), 'SIS_MAIN');
-- Use statistics (rather than blocks) to estimate time.
-- Start the job. An exception is returned if something is not set up properly.
-- The export job should now be running. We loop until its finished
WHILE (v_job_state != 'COMPLETED') and (v_job_state != 'STOPPED') LOOP
DBMS_DATAPUMP.get_status(v_job_handle,DBMS_DATAPUMP.ku$_status_job_error + DBMS_DATAPUMP.ku$_status_job_status + DBMS_DATAPUMP.ku$_status_wip,-1,v_job_state,sts);
-- As the percentage-complete changes in this loop, the new value displays.
IF js.percent_done != v_percent THEN
-- When the job finishes, display status before detaching from job.
PRC_LOG(f1, t_archive_list(i) || ': Export complete with status: ' || v_job_state);
-- use STOP_JOB instead of DETACH otherwise the "master table" which is created when OPEN is called will not be removed.