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'

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------
/u01/oradata/SID/ts01.dbf
6 TABLE_SPACE_NAME

/u01/oradata/SID/ts02.dbf
43 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 ...
$ export ORACLE_SID=NEWSID


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 9.2.0.5.0 - Production on Mon Jun 12 13:31:48 2006

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

Username: username as sysdba
Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - 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';

TABLESPACE_NAME STATUS
------------------------------ ---------
TABLE_SPACE_NAME READ ONLY


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

Tablespace altered.

No comments:

Post a Comment