How to Move or Transport Table Partitions Using Transportable TableSpace (TTS) Option? (Doc ID 731559.1)

Last updated on MAY 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.

Goal

This Article show by example how to move or transport table partition using Transportable Table Space (TTS) considering Partition exchange option.

This method may help in the following scenarios as example:
- Backup/Restore table partition(s).
- May considered in ETL from OLTP to DWH databases as fast move data load method for table partition(s).

Facts:
. To be able to use TTS we should have self contained tablespace (TBS)
-- self contained means that the base object and all related/dependents should be included on the export set.
. Table partition is part of global partitioned table, even it was in separate TBS.
. So basically we will consider using TTS and partition exchange features to turn partition to regular table.


Base Idea for this method:
. Create regular table to hold partition data.
. Exchange partition data with that table
. Drop that table partition
. Now we can TTS the TBS
. Turn it back when needed or plug it in another database.

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