gs79
asked on
data pump in PLSQL
I have two databases on same box db1 and db2. I have created a plsql block using dbms_datapump api to export and import a table.
When I execute the block with export option on DB1, i see the file being created in DATA_PUMP_DIR. When I execute the same plsql block with import option, the block executes successfully but I dont see any data in the table.
on DB1
On DB2
Is there anything I am missing. Thanks in Advance..
When I execute the block with export option on DB1, i see the file being created in DATA_PUMP_DIR. When I execute the same plsql block with import option, the block executes successfully but I dont see any data in the table.
on DB1
declare
h1 NUMBER;
begin
h1 := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE',
job_name => 'ABC_EXP_JOB');
dbms_datapump.add_file(handle => h1, filename => 'ABC.DMP',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(handle => h1, filename => 'ABC.LOG',
filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST',
value => '''myschema''');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST',
value => '''ABC''');
dbms_datapump.start_job(handle => h1);
dbms_datapump.detach(handle => h1);
end;
/On DB2
declare
h1 NUMBER;
begin
h1 := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE',
job_name => 'ABC_EXP_JOB');
dbms_datapump.add_file(handle => h1, filename => 'ABC.DMP',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(handle => h1, filename => 'ABC.LOG',
filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST',
value => '''myschema''');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST',
value => '''ABC''');
dbms_datapump.start_job(handle => h1);
dbms_datapump.detach(handle => h1);
end;
/Is there anything I am missing. Thanks in Advance..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I will direct you to the documentation for the datapump API -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_api.htm#SUTIL977
There is an example there.
I will then direct you to the documentation for SET_PARAMETER -> http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_datpmp.htm#ARPLS66059
Not sure exactly what you want to do when the table exists, but here is a guess:
DBMS_DATAPUMP.SET_PARAMETE
The different options are explained in the documentation that I pointed to.
There is an example there.
I will then direct you to the documentation for SET_PARAMETER -> http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_datpmp.htm#ARPLS66059
Not sure exactly what you want to do when the table exists, but here is a guess:
DBMS_DATAPUMP.SET_PARAMETE
The different options are explained in the documentation that I pointed to.
ASKER
That worked. Thank you!
ASKER
Good idea, let me check the log file. I just wanted to get this out, I will change the log name.
Here is the import log:
Master table "myschema"."ABC_EXP_JOB" successfully loaded/unloaded
Starting "myschema"."ABC_EXP_JOB":
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "myschema"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
..
..
..
Job "myschema."ABC_EXP_JOB" completed with 1 error(s) at Tue May 10 13:01:04 2016 elapsed 0 00:01:06
Its complaining that table exists. Seem like API is internally using IMPDP. How do I overwirte "TABLE_EXISTS_ACTION" to APPEND in the api call?
Let my try dropping the table and import again using API. Seem like that had failed to earlier too. But I need to be actually able to append it
Thanks