Skip to Main Content
  • Questions
  • Importing sequences along with table using DBMS_DATAPUMP package.

Breadcrumb

Question and Answer

Connor McDonald

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...

The SCHEMA_EXPR is for the schema you're importing from (KNK). You've stated the schema you're importing to (KNK1).

Change this to KNK!

And remap the schema from KNK -> KNK1 with the METADATA_REMAP procedure:

http://docs.oracle.com/database/121/ARPLS/d_datpmp.htm#ARPLS66047

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ravi Rai, May 03, 2017 - 10:03 am UTC

Hi Chris

Thank you so much for reply.Now its working.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.