+-------------------------------------------------------------------------------------------+
| |
| Transportable tablespaces in Oracle 10g |
| |
+-------------------------------------------------------------------------------------------+
| Techonology : Oracle database |
+-------------------------------------------------------------------------------------------+
| Created : 11/7/2007 |
+-------------------------------------------------------------------------------------------+
SYS@ORCL > exec dbms_tts.transport_set_check('TBS1',TRUE,TRUE);
PL/SQL procedure successfully completed.
SYS@ORCL > select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object SYS_C007034 in tablespace TBS1 not allowed in pluggable set
Sys owned object DATAE in tablespace TBS1 not allowed in pluggable set
Sys owned object SYS_IL0000021148C00002$$ in tablespace TBS1 not allowed in plu
ggable set
Sys owned object SYS_LOB0000021148C00002$$ in tablespace TBS1 not allowed in pl
uggable set
-----------------------------------------------------------------------------------
[oracle@tom datafiles]$ expdp "'sys/oracle as sysdba'" transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:08:54
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29341: The transportable set is not self-contained
-----------------------------------------------------------------------------------
[oracle@tom datafiles]$ expdp system/oracle transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
Export: Release 10.2.0.2.0 - Production on Thursday, 12 July, 2007 1:16:42
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** transport_tablespaces=TBS1 transport_full_check=true directory=ORA_DIR dumpfile=tbs.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tbs.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:18:21
Name (192.168.0.2:oracle): ftp
331 Password required for ftp.
Password:
230 User ftp logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> put tbs1_01.dbf
local: tbs1_01.dbf remote: tbs1_01.dbf
227 Entering Passive Mode (192,168,0,2,9,101).
150 Opening data connection for tbs1_01.dbf.
226 File received ok
10493952 bytes sent in 9.2 seconds (1.1e+03 Kbytes/s)
RMAN> convert datafile 'tbs1_01.dbf'
2> from platform='Linux IA (32-bit)'
3> db_file_name_convert="ts","win";
Starting backup at 12-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TBS1_01.DBF
converted datafile=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-ORCL_I-1121005791_TS-TBS1_FNO-7_
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 12-JUL-07
C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf
Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:49:14
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user MICHAEL does not exist in the database
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:49:20
--------------------------------------------------------------------
SQL> create user michael identified by michael;
User created.
--------------------------------------------------------------------
C:\>create user michael identified by michael;
'create' is not recognized as an internal or external command,
operable program or batch file.
C:\>impdp system/oracle directory=data_pump_dir dumpfile=tbs.dmp transport_datafiles=tbs_win01.dbf
Import: Release 10.2.0.1.0 - Production on Thursday, 12 July, 2007 1:50:07
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=tbs.dmp transport_d
atafiles=tbs_win01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 01:50:12
-------------------------------------------------------------------------
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
TBS1
SQL> alter tablespace tbs1 read write;
--------------------------------------------------------------------------
