Hello, I'm trying to export some table data from a schema using dbms_datapump API, but it gives me problems and it takes too much time to elaborate and I don't understand why! I want to export data only in a dmp file, that I will use later to import in an other schema. I'm using Oracle 10g R2 .
I want to export data from TABLE1 and TABLE2, and ONLY the first 10 rows (this is for test now). I used the data_filter to write the subquery to filter the rows, and NAME_LIST to filter table names. I've set INCLUDE_METADATA to 0, to not export metadata. But it takes 10 minutes to run, and the output log says that there was an error (after 10 minutes??!):
content of : table_dump.log
Starting "MYSCHEMANAME"."SYS_EXPORT_TABLE_02":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object IN ('TABLE1' was not found.
ORA-39166: Object 'TABLE2') was not found.
ORA-31655: no data or metadata objects selected for job
Job "MYSCHEMANAME"."SYS_EXPORT_TABLE_02" completed with 3 error(s) at 15:58:47
This is the code I use:
DECLARE
handle NUMBER;
status VARCHAR2(20);
BEGIN
handle := DBMS_DATAPUMP.OPEN ('EXPORT', 'TABLE');
dbms_datapump.add_file(handle => handle,filename => 'table_dump.log',directory => 'DATAPUMP_DIR',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.add_file(handle => handle,filename => 'table_dump.dmp',directory => 'DATAPUMP_DIR',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
dbms_datapump.metadata_filter(handle, 'SCHEMA_EXPR', 'IN (''MYSCHEMANAME'')');
dbms_datapump.metadata_filter (handle, 'NAME_LIST', 'IN (''TABLE1'',''TABLE2'')');
dbms_datapump.data_filter(handle, 'SUBQUERY', 'WHERE rownum <= 10', 'TABLE1', 'MYSCHEMANAME');
dbms_datapump.data_filter(handle, 'SUBQUERY', 'WHERE rownum <= 10', 'TABLE2', 'MYSCHEMANAME');
dbms_datapump.set_parameter(HANDLE => handle,NAME => 'INCLUDE_METADATA', VALUE => 0) ;
dbms_datapump.START_JOB(handle);
dbms_datapump.WAIT_FOR_JOB(handle, status);
END;
/
Edited by: user10396517 on 27-feb-2012 9.17 - added the code formatting