Tuesday, June 13, 2006

Oracle Tablespace Transport

Oracle's transportable tablespace feature is can save a lot of time when copying a user's data. All objects will need to be in one tablespace, so it may not work for all environments. When a schema's objects are in one tablespace, this is a quick way to copy a schema and data.

Verify all objects are in one tablespace. Then SELECT to see any problems.
SQL> exec sys.dbms_tts.transport_set_check('TABLE_SPACE_NAME', true)

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

Determine names of data files.
SQL> select file_name, file_id, tablespace_name from dba_data_files where tablespace_name='TABLE_SPACE_NAME'

---------- ------------------------------


Set the tablespace to read only.
SQL> alter tablespace TABLE_SPACE_NAME read only;

Tablespace altered.

To export transportable, user must log on as sysdba: as sysdba
File expdat.dmp will be created. The file will be small because it will contain just metadata.
$ exp transport_tablespace=y tablespaces=TABLE_SPACE_NAME

Copy data files to new location, then and set ORACLE_SID.
$ cp ...

To import transportable, the user must log on as sysdba: as sysdba
$ imp file=expdat.dmp transport_tablespace=y "datafiles=(/u02/oradata/NEWSID/ts01.dbf,/u02/oradata/NEWSID/ts02.dbf)" tts_owner=SCHEMA_OWNER_NAME

Import: Release - Production on Mon Jun 12 13:31:48 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: username as sysdba

Connected to: Oracle9i Enterprise Edition Release - 64bit Production
JServer Release - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SCHEMA_OWNER_NAME's objects into SCHEMA_OWNER_NAME
. . importing table "TABLE1"
. . importing table "TABLE2"
Import terminated successfully without warnings.

The imported tablespace is read only and the objects have been imported.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'TABLE_SPACE_NAME';

------------------------------ ---------

If desired, set the tablespace to read write.
SQL> alter tablespace TABLE_SPACE_NAME read write;

Tablespace altered.

No comments:

Post a Comment