How to Perform a Transportable Tablespace (TTS) Export Of A Single Partition Using The DBMS_DATAPUMP API (Doc ID 1298849.1)

Last updated on MARCH 31, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 31-MAR-2015***

Goal

There are times where a single partition of a table is desired to be transported.  In 11.2, we introduce the ability to perform a transportable tablespace export of a single partition. However, you may find that the same steps used to export a single table partition using the DBMS_DATAPUMP API do not work when trying to perform a transportable tablespace export as the data_filter option is not supported in TRANSPORTABLE mode.

This note will demonstrate how to perform a single partition transportable tablespace (TTS) export using the DataPump API.

In this exercise, we will be creating a partitioned table in the SCOTT schema and moving one partition to a separate tablespace and then performing a TTS export of that single partition using DBMS_DATAPUMP.

Let's first configure our environment.

1. Create the directory object:

connect / as sysdba
create or replace directory TESTCASE as '/tmp';

2. Create a partitioned table in the SCOTT schema:

connect scott/tiger

create table PART_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 (100000),
   partition p006 values less than (maxvalue)
);

3. Populate the table with data:

begin
  for i in 1..100000 loop
     insert into part_tab values (i, 'Text '||lpad (to_char (i), 95, '0'));
  end loop;
commit;
end;
/

4. Validate that the partitions exist and the related tablespaces:

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, tablespace_name
from   dba_tab_partitions
where  table_owner = 'SCOTT' and
       table_name = 'PART_TAB'
order  by table_owner, table_name, partition_position;

Our query should return:


TABLE_OWNE TABLE_NAME PARTITION_ TABLESPACE_NAME
---------- ---------- ---------- ------------------------------
SCOTT      PART_TAB   P001       USERS
SCOTT      PART_TAB   P002       USERS
SCOTT      PART_TAB   P003       USERS
SCOTT      PART_TAB   P004       USERS
SCOTT      PART_TAB   P005       USERS
SCOTT      PART_TAB   P006       USERS

6 rows selected.

5. Move one partition to another tablespace:

connect scott/tiger
ALTER TABLE PART_TAB MOVE PARTITION P004 TABLESPACE PARTTEST UPDATE INDEXES;

6. Verify that the single partition has been moved into the expected tablespaces:

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, tablespace_name
from   dba_tab_partitions
where  table_owner = 'SCOTT' and
       table_name = 'PART_TAB'
order by table_owner, table_name, partition_position;

 Our query should return:

TABLE_OWNE TABLE_NAME PARTITION_ TABLESPACE_NAME
---------- ---------- ---------- ------------------------------
SCOTT      PART_TAB   P001       USERS
SCOTT      PART_TAB   P002       USERS
SCOTT      PART_TAB   P003       USERS
SCOTT      PART_TAB   P004       PARTTEST
SCOTT      PART_TAB   P005       USERS
SCOTT      PART_TAB   P006       USERS

6 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