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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

Asked: April 22, 2017 - 6:31 am UTC

Last updated: May 02, 2017 - 3:16 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

I am exporting some table of my schema 'KNK' using following code.



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:='KNK';
h1 := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE');
DBMS_DATAPUMP.add_file (h1, v_schema||'_'||v_time||'.DMP','EXP',reusefile => 1);
DBMS_DATAPUMP.add_file (handle => h1,filename => v_schema||'_'||v_time||'.LOG',directory => 'EXP1',reusefile => 1,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''T_VISIT'',''T_SUBVISIT'',''T_SERVICES'')', object_type => 'TABLE');
dbms_datapump.start_job(h1);
DBMS_DATAPUMP.WAIT_FOR_JOB(h1, v_job_state);
DBMS_OUTPUT.PUT_LINE(v_job_state);
end;




Along with these three tables, i want export two sequences(e.g. PATIENT_INFO_SEQ,PATIENT_SERVICES_REG2_SEQ).
Kindly help to do this task.
Thanks
Ravi Rai

and Connor said...

Sequences are not *bound* to a table even though we often make that assumption because we often create one sequence per table for a surrogate key.

So you would need to have additional filter, eg

SQL> declare
  2    h1 number;
  3    v_schema VARCHAR2(32);
  4    v_job_state varchar2(4000);
  5  begin
  6    v_schema:='SCOTT';
  7    h1 := dbms_datapump.open(operation => 'EXPORT', job_mode => 'SCHEMA');
  8    DBMS_DATAPUMP.add_file (handle => h1,filename => 'MYDUMP.dmp',directory => 'TEMP');
  9    DBMS_DATAPUMP.add_file (handle => h1,filename => 'MYDUMP.log',directory => 'TEMP',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 10    dbms_datapump.metadata_filter(handle => h1,name => 'SCHEMA_EXPR',value => '= ''SCOTT''');
 11    dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''EMP'',''DEPT'')', object_type => 'TABLE');
 12    dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''SEQ1'',''SEQ2'')', object_type => 'SEQUENCE');
 13    dbms_datapump.start_job(h1);
 14    DBMS_DATAPUMP.WAIT_FOR_JOB(h1, v_job_state);
 15    DBMS_OUTPUT.PUT_LINE(v_job_state);
 16  end;
 17  /


Rating

  (2 ratings)

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

Comments

Importing the exported file along with sequences.

Ravi Rai, April 24, 2017 - 9:03 am UTC

Hello Connor McDonald

Thank you for reply.
Now its Working fine.

Next step, I have to import that exported file in another schema KNK1 along with sequences.

Kindly find the code.

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;


But I am facing following issue.
ORA-31627: API call succeeded but more information is available

Kindly help me.
Thanks
Ravi Rai
Oracle Apex Developer
(Sarada Research Labs)
Connor McDonald
May 02, 2017 - 3:16 am UTC

Anything the log file ?

Reg : Log in Importing sequences along with tables

Ravi Rai, May 02, 2017 - 8:55 am UTC

Master table "KNK1"."SYS_IMPORT_SCHEMA_01" 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_01":
Job "KNK1"."SYS_IMPORT_SCHEMA_01" successfully completed at 14:19:15

Kindly help me to figure out this problem.

More to Explore

Data Pump

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