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:
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:
$ 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