How To Export Certain Tables And Sequences From One Schema Using DataPump API?

(Doc ID 1297557.1)

Last updated on MAY 02, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

Goal

You want to export certain tables and sequences from one schema using DataPump API. The next example demonstrates this.

Assumed you have the following environment:

connect / as sysdba
create user a_schema identified by a_schema default tablespace users temporary tablespace temp;
grant connect, resource to a_schema;

connect a_schema/a_schema

create table a_tab (id number);
create table b_tab (id number);
create table c_tab (id number);
insert into a_tab values (1);
insert into b_tab values (1);
insert into b_tab values (2);
insert into c_tab values (1);
insert into c_tab values (2);
insert into c_tab values (3);
commit;

create sequence a_seq start with 1;
create sequence b_seq start with 1;
create sequence c_seq start with 1;


You want to export from schema A_SCHEMA the table B_TAB and the sequence B_SEQ only, in one step. Using the DataPump client expdp the command looks like:

# expdp system/passwd directory=tmp dumpfile=a_schema.dmp schemas=a_schema include=table:\"like \(\'B_TAB%\'\)\",sequence:\"like \(\'B_SEQ%\'\)\"

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms