Prior to 11g, there is a restriction on transporting tablespaces for a single partition. Starting in RDBMS version 11g, transport of a tablespace for a single partition and plugging it into another database is now supported. This note shows the process to follow in order to achieve this.
Steps
1. Create the partitioned table:
SQL> CREATE TABLE rptg_pd
2 (
3 a varchar2(5),
4 period_dt date,
5 b number)
6 PARTITION BY RANGE (period_dt)
7 (
8 PARTITION mv_period_dt_1994 VALUES LESS THAN (TO_DATE('01-JAN-1995')) tablespace part1,
9 PARTITION mv_period_dt_1995 VALUES LESS THAN (TO_DATE('01-JAN-1996')) tablespace part2
10 );
Table created.
SQL> select partition_name, tablespace_name 2 from user_tab_partitions 3 where table_name = 'RPTG_PD'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ MV_PERIOD_DT_1994 PART1 MV_PERIOD_DT_1995 PART2
2. Change the tablespace that contains the partition to read only:
SQL> alter tablespace PART1 read only; Tablespace altered.
3. Use the following command to export the metadata.
$ expdp samir/samir tables=samir.rptg_pd:mv_period_dt_1994 transportable=always directory=TEST_COMPRESS dumpfile=p_ct.dmp
4. Copy the datafiles to new location.
$ cp -p /data2/oracle/ora11g/PART1.dbf /data2/oracle/ora11g/TEST_DIR/
5. For this example, we will transport the partition back into the same database, but into a different schema by doing the following:
SQL> create user geek identified by geek; User created.
SQL> grant dba to geek; Grant succeeded.
6. Now perform the import:
$ impdp system PARTITION_OPTIONS=departition TRANSPORT_DATAFILES='/data2/oracle/ora11g/TEST_DIR/PART1.dbf' DIRECTORY=TEST_COMPRESS DUMPFILE=p_ct.dmp REMAP_SCHEMA=samir:geek
You can now see a new table with name RPTG_PD_MV_PERIOD_DT_1994 in schema geek.
SQL> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME -------------------------- ------------------------------ RPTG_PD_MV_PERIOD_DT_1994 PART1
Note: The table name is always a combination of the original table and the partition name.