TABLESPACE TRANSPORTATION
from Linux Server (192.168.188.129) to WINDOWS server
root@192.168.188.129's password:
Last login: Sun Aug 12 14:13:26 2012 from 192.168.188.1
[root@localhost ~]# ps -ef | grep pmon
oracle 4745 1 0 11:31 ? 00:00:00 asm_pmon_+ASM
oracle 8046 1 0 12:42 ? 00:00:00 ora_pmon_rcat
root 12725 12683 0 14:58 pts/5 00:00:00 grep pmon
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ export ORACLE_SID=rcat
[oracle@localhost ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 12 14:58:53 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
RCAT READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rcat/system01.dbf
/u01/app/oracle/oradata/rcat/undotbs01.dbf
/u01/app/oracle/oradata/rcat/sysaux01.dbf
/u01/app/oracle/oradata/rcat/users01.dbf
/u01/app/oracle/oradata/rcat/rmans01.dbf
/u01/app/oracle/oradata/rcat/new01.dbf
6 rows selected.
SQL> create tablespace shoaibtts datafile '/u01/app/oracle/oradata/rcat/shotts01.dbf' size 10m autoextend on next 5m extent management local segment space management auto;
Tablespace created.
SQL> alter user scott quota unlimited on shoaibtts;
User altered.
SQL> conn scott/tiger
Connected.
SQL>
create table shoaib (name varchar2(30) primary key) tablespace users;
insert into shoaib values ('ABC');
insert into shoaib values ('XYZ');
commit;
create table shoaibnew (name varchar2(30)) tablespace shoaibtts;
insert into shoaibnew values ('ABC');
insert into shoaibnew values ('XYZ');
commit;
alter table shoaib add constraint shoaib_fk foreign key (name) references shoaibnew (name);
SQL> conn / as sysdba
Connected.
SQL> begin
2 dbms_tts.transport_set_check(ts_list=>'SHOAIBTTS',incl_constraints=>TRUE);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
SQL> select a.platform_name , b.endian_format from v$database a , v$transportable_platform b where a.platform_name = b.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
------------- --------------
Linux IA (32-bit) Little
SQL> alter tablespace shoaibtts read only;
Tablespace altered.
SQL> host expdp system/oracle dumpfile=shoaibtts_metadata.dmp transport_tablespaces=shoaibtts
Export: Release 10.2.0.1.0 - Production on Sunday, 12 August, 2012 16:27:35
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
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=shoaibtts_metadata.dmp transport_tablespaces=shoaibtts
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/shoaibtts_metadata.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:28:40
SQL> select directory_name , directory_path from dba_directories where directory_name='DATA_PUMP_DIR';
DIRECTORY_NAME DIRECTORY_PATH
-------------- ------------------------------------------------------------------
DATA_PUMP_DIR /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
SQL> !
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
[oracle@localhost log]$ ls -lrth
total 84K
-rw-r----- 1 oracle oinstall 116 Aug 11 13:45 dp.log
-rw-r----- 1 oracle oinstall 68K Aug 12 16:28 shoaibtts_metadata.dmp
-rw-r--r-- 1 oracle oinstall 951 Aug 12 16:28 export.log
[oracle@localhost log]$
MOVE THE shoaibtts_metadata.dmp file and DATAFILE shotts01.dbf on the WINDOWS server
C:\>impdp system/oracle dumpfile=shoaibtts_metadata.dmp directory=data_pump_dir transport_datafiles='C:\oracle\product\10.2.0\oradata\orcl\shotts01.dbf'
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition
Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully
loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":
system/******** dumpfile=shoaibtts_metadata.dmp
directory=data_pump_dir
transport_datafiles=
'C:\oracle\product\10.2.0\oradata\orcl\shotts01.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/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at
04:13:32
If you see a message of successfully completed import, the tablespace has been imported. We can check that like this:
C:\>sqlplus scott/scott
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select * from shoaib;
NAME
------------------------------
ABC
XYZ
SQL> select * from shoaibnew;
NAME
------------------------------
ABC
XYZ
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name , file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- ---------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
EXAMPLE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
SHOAIBTTS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SHOTTS01.DBF
6 rows selected.
SQL> select * from transport_set_violations;
VIOLATIONS
-------------------------------------------------------------
Constraint SHO_FK between table SCOTT.SHOAIBNEW in tablespace USERS and table SCOTT.SHOAIB in tablespace SHOAIBTTS
Index SCOTT.SYS_C005398 in tablespace USERS enforces primary constraints of table SCOTT.SHOAIB in tablespace SHOAIBTTS
/*
dbms_tts.transport_set_check is complaining that the child table is in shoaibtts and parent is in users tablespace. Lets go and fix this.
Check again and this time it says that the index behind the parent's table primary key is still in users tablespace.
*/
SQL> conn scott/tiger
Connected.
SQL> alter index SYS_C005398 rebuild tablespace shoaibtts;
Index altered.
from Linux Server (192.168.188.129) to WINDOWS server
root@192.168.188.129's password:
Last login: Sun Aug 12 14:13:26 2012 from 192.168.188.1
[root@localhost ~]# ps -ef | grep pmon
oracle 4745 1 0 11:31 ? 00:00:00 asm_pmon_+ASM
oracle 8046 1 0 12:42 ? 00:00:00 ora_pmon_rcat
root 12725 12683 0 14:58 pts/5 00:00:00 grep pmon
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ export ORACLE_SID=rcat
[oracle@localhost ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 12 14:58:53 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
RCAT READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/rcat/system01.dbf
/u01/app/oracle/oradata/rcat/undotbs01.dbf
/u01/app/oracle/oradata/rcat/sysaux01.dbf
/u01/app/oracle/oradata/rcat/users01.dbf
/u01/app/oracle/oradata/rcat/rmans01.dbf
/u01/app/oracle/oradata/rcat/new01.dbf
6 rows selected.
SQL> create tablespace shoaibtts datafile '/u01/app/oracle/oradata/rcat/shotts01.dbf' size 10m autoextend on next 5m extent management local segment space management auto;
Tablespace created.
SQL> alter user scott quota unlimited on shoaibtts;
User altered.
SQL> conn scott/tiger
Connected.
SQL>
create table shoaib (name varchar2(30) primary key) tablespace users;
insert into shoaib values ('ABC');
insert into shoaib values ('XYZ');
commit;
create table shoaibnew (name varchar2(30)) tablespace shoaibtts;
insert into shoaibnew values ('ABC');
insert into shoaibnew values ('XYZ');
commit;
alter table shoaib add constraint shoaib_fk foreign key (name) references shoaibnew (name);
SQL> conn / as sysdba
Connected.
SQL> begin
2 dbms_tts.transport_set_check(ts_list=>'SHOAIBTTS',incl_constraints=>TRUE);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
SQL> select a.platform_name , b.endian_format from v$database a , v$transportable_platform b where a.platform_name = b.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
------------- --------------
Linux IA (32-bit) Little
SQL> alter tablespace shoaibtts read only;
Tablespace altered.
SQL> host expdp system/oracle dumpfile=shoaibtts_metadata.dmp transport_tablespaces=shoaibtts
Export: Release 10.2.0.1.0 - Production on Sunday, 12 August, 2012 16:27:35
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
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=shoaibtts_metadata.dmp transport_tablespaces=shoaibtts
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/shoaibtts_metadata.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:28:40
SQL> select directory_name , directory_path from dba_directories where directory_name='DATA_PUMP_DIR';
DIRECTORY_NAME DIRECTORY_PATH
-------------- ------------------------------------------------------------------
DATA_PUMP_DIR /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
SQL> !
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
[oracle@localhost log]$ ls -lrth
total 84K
-rw-r----- 1 oracle oinstall 116 Aug 11 13:45 dp.log
-rw-r----- 1 oracle oinstall 68K Aug 12 16:28 shoaibtts_metadata.dmp
-rw-r--r-- 1 oracle oinstall 951 Aug 12 16:28 export.log
[oracle@localhost log]$
MOVE THE shoaibtts_metadata.dmp file and DATAFILE shotts01.dbf on the WINDOWS server
C:\>impdp system/oracle dumpfile=shoaibtts_metadata.dmp directory=data_pump_dir transport_datafiles='C:\oracle\product\10.2.0\oradata\orcl\shotts01.dbf'
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition
Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully
loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":
system/******** dumpfile=shoaibtts_metadata.dmp
directory=data_pump_dir
transport_datafiles=
'C:\oracle\product\10.2.0\oradata\orcl\shotts01.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/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at
04:13:32
If you see a message of successfully completed import, the tablespace has been imported. We can check that like this:
C:\>sqlplus scott/scott
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select * from shoaib;
NAME
------------------------------
ABC
XYZ
SQL> select * from shoaibnew;
NAME
------------------------------
ABC
XYZ
SQL> conn / as sysdba
Connected.
SQL> select tablespace_name , file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------- ---------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
EXAMPLE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
SHOAIBTTS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SHOTTS01.DBF
6 rows selected.
SQL> select * from transport_set_violations;
VIOLATIONS
-------------------------------------------------------------
Constraint SHO_FK between table SCOTT.SHOAIBNEW in tablespace USERS and table SCOTT.SHOAIB in tablespace SHOAIBTTS
Index SCOTT.SYS_C005398 in tablespace USERS enforces primary constraints of table SCOTT.SHOAIB in tablespace SHOAIBTTS
/*
dbms_tts.transport_set_check is complaining that the child table is in shoaibtts and parent is in users tablespace. Lets go and fix this.
Check again and this time it says that the index behind the parent's table primary key is still in users tablespace.
*/
SQL> conn scott/tiger
Connected.
SQL> alter index SYS_C005398 rebuild tablespace shoaibtts;
Index altered.
Denver airport to boulder shuttle I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article.
ReplyDelete