How to Export Single Partitions From Multiple Tables Using DataPump API? (Doc ID 1078784.1)

Last updated on NOVEMBER 28, 2016

Applies to:

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

Goal

This note demonstrates how to export single partitions from multiple tables using the DataPump API.
The environment is the following one:

connect / as sysdba

-- Create user
drop user test_part cascade;
purge dba_recyclebin;
create user test_part identified by test_part default tablespace users temporary tablespace temp;
grant connect, resource to test_part;

-- Create directory object
create or replace directory dpudir as '/tmp';
grant read, write on directory dpudir to test_part;

-- Create first partitioned table in new created schema
connect test_part/test_part
create table a_tab
(
   id number,
   text varchar2(100)
)
partition by range (id)
(
   partition p001 values less than (10),
   partition p002 values less than (100),
   partition p003 values less than (1000),
   partition p004 values less than (10000),
   partition p005 values less than (maxvalue)
);

-- Populate the table
begin
  for i in 1..10000 loop
    insert into a_tab values (i, 'Text '||lpad (to_char (i), 95, '0'));
  end loop;
  commit;
end;
/

-- Create second partitioned table in new created schema
create table b_tab
(
   id number,
   text varchar2(100)
)
partition by range (id)
(
   partition q001 values less than (10),
   partition q002 values less than (100),
   partition q003 values less than (1000),
   partition q004 values less than (10000),
   partition q005 values less than (maxvalue)
);

-- Populate the table
begin
  for i in 1..10000 loop
    insert into b_tab values (i, 'Text '||lpad (to_char (i), 95, '0'));
  end loop;
  commit;
end;
/

-- Verify the table partitions
connect / as sysdba
col table_owner format a10
col table_name format a10
col partition_name format a10
select table_owner, table_name, partition_name
from   dba_tab_partitions
where  table_owner = 'TEST_PART' and
       table_name in ('A_TAB', 'B_TAB')
order  by table_owner, table_name, partition_position;


This shows:

TABLE_OWNE TABLE_NAME PARTITION_
---------- ---------- ----------
TEST_PART  A_TAB      P001
TEST_PART  A_TAB      P002
TEST_PART  A_TAB      P003
TEST_PART  A_TAB      P004
TEST_PART  A_TAB      P005
TEST_PART  B_TAB      Q001
TEST_PART  B_TAB      Q002
TEST_PART  B_TAB      Q003
TEST_PART  B_TAB      Q004
TEST_PART  B_TAB      Q005

10 rows selected.

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