Oracle, Web, Script, SQLserver, Tips & Trick

April 7, 2011

TRANSPORT_TABLESPACES with partitions

Filed under: oracle,oracle:10g — sikathabis @ 6:37 pm
Tags: ,

Syntax and Description

TRANSPORT_TABLESPACES=tablespace_name [, ...]

Use the TRANSPORT_TABLESPACES parameter to specify a list of tablespace namesfor which object metadata will be exported from the source database into the targetdatabase.

Note: You cannot export transportable tablespaces and then importthem into a database at a lower release level. The target database mustbe at the same or higher release level as the source database.

Restrictions

Transportable jobs are not restartable.

Transportable jobs are restricted to a degree of parallelism of 1.

Transportable tablespace mode requires that you have the EXP_FULL_DATABASE role.

Example 1

The following is an example of using the TRANSPORT_TABLESPACES parameter in afile-based job (rather than network-based). The tablespace tbs_1 is the tablespacebeing moved. This example assumes that tablespace tbs_1 exists and that it has beenset to read-only.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmpTRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=y LOGFILE=tts.log
taken from Oracle Database Utilities 10g Release 2.pdf page 84
 
What About Partitions?
When you move a table, you have to move the entire table. If you have partitioned a table, you can’t transport just one partition – it’s all or nothing. If you have a large table with many partitions and you want to use the transportable tablespace feature to move one partition’s worth, you can follow these steps:
  1. Create a new tablespace to store the data you want to move.
  2. In the new tablespace, create a table for the data you want to move. For example, you can use a create table as select command with a where clause that selects only the current month’s data and puts it into the new table. Optionally, you can create indexes for the new table, in either the new tablespace or in a separate isolated one.
  3. Prepare the new tablespace for the move by placing it in read-only mode.
  4. Check the transport set by using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.
  5. Export the metadata for the new tablespace.
  6. At the operating-system level, move the new tablespace’s datafiles and the export dump file to the target location.
  7. Once you have completed and verified step 6, drop the tablespace from the source database.
  8. Import the metadata for the new tablespace into the target database.
  9. Insert the data into the tables in the target database. You can do this via insert as select or, if the target table is partitioned, by using the exchange partition option to make the newly plugged-in table a partition within the target table.
  10. Commit the inserted data, and verify that everything worked.
  11. Back up the control files for the source and the target database.
taken from www.pafumi.net

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: