How to Export Single Partitions Using DataPump API?

(Doc ID 1078265.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.1 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 13-Aug-2013***

Goal

This note demonstrates how to export single partitions from a table 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 a 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;
/

-- 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 = 'A_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

5 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