How To Export Certain Tables And Sequences From One Schema Using DataPump API? (Doc ID 1297557.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 23-May-2013***

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