Thanks for the question, Ravi.
Asked: May 03, 2017 - 5:15 am UTC
Last updated: May 03, 2017 - 9:40 am UTC
Version: oracle 11g
Viewed 1000+ times
You Asked
Hello All
I am trying to import sequence along with tables.
In the exported file all three tables(T_VISIT,T_SUBVISIT,T_SERVICES) and sequences(PATIENT_INFO_SEQ,PATIENT_SERVICES_REG2_SEQ) are present.
For export, I have already used DBMS_DATAPUMP package.
This dump file is exported from KNK schema and i want to import in schema KNK1
declare
h1 number;
v_schema VARCHAR2(32);
v_time VARCHAR2(32);
v_job_state varchar2(4000);
begin
SELECT to_char(SYSDATE, 'DD_MM_YYYY_hh24_mi_ss') INTO v_time FROM DUAL;
v_schema:='KNK1';
h1 := dbms_datapump.open(operation => 'IMPORT', job_mode => 'SCHEMA');
DBMS_DATAPUMP.add_file (h1,filename => 'Test.DMP',directory => 'EXP',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.add_file (handle => h1,directory => 'EXP',filename => 'IMPapi.log',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.metadata_filter(handle => h1,name => 'SCHEMA_EXPR',value => '= ''KNK1''');
dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''T_VISIT'',''T_SUBVISIT'',''T_SERVICES'')', object_type => 'TABLE');
dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''PATIENT_INFO_SEQ'',''PATIENT_SERVICES_REG2_SEQ'')', object_type => 'SEQUENCE');
dbms_datapump.set_parameter (h1, 'TABLE_EXISTS_ACTION', 'REPLACE');
dbms_datapump.start_job(h1);
DBMS_DATAPUMP.WAIT_FOR_JOB(h1, v_job_state);
DBMS_OUTPUT.PUT_LINE(v_job_state);
end;
I am getting following issue.
ORA-31627: API call succeeded but more information is available.
And following is the log details.
Master table "KNK1"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
ORA-39039: Schema expression "= 'KNK1'" contains no valid schemas.
ORA-31655: no data or metadata objects selected for job
Starting "KNK1"."SYS_IMPORT_SCHEMA_03":
Job "KNK1"."SYS_IMPORT_SCHEMA_03" successfully completed at 10:31:36
Kindly help me to solve this issue.
Thanks
Ravi Rai
Oracle Apex Developer
(Sarada Research Labs)
and Chris said...
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment