传输表空间

1）在Prod数据库下
SQL> select segment_name from dba_segments where tablespace_name='OLTP';


no rows selected


SQL> select username from dba_users;


USERNAME
------------------------------
SH
OUTLN
DBSNMP
HR
SYS
SYSTEM
TSMSYS
DIP


8 rows selected.


SQL> create user oltp identified by oltp;


User created.


SQL> grant dba to oltp;


Grant succeeded.


SQL> alter user oltp default tablespace oltp;


User altered.


SQL> conn oltp/oltp
Connected.
SQL> desc t
ERROR:
ORA-04043: object t does not exist




SQL> create table t(id int,name varchar2(10));


Table created.


SQL> insert into t values(1,'a');


1 row created.


SQL> insert into t values(2,'b');


1 row created.


SQL> commit;


Commit complete.


SQL> select * from t;


        ID NAME
---------- ----------
         1 a
         2 b


SQL> alter tablespace oltp read only;


Tablespace altered.


SQL> exec dbms_tts.transport_set_check('OLTP',true);
BEGIN dbms_tts.transport_set_check('OLTP',true); END;


      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_TTS.TRANSPORT_SET_CHECK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored




SQL> exit                         
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@ocm1 ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 10:50:08 2014


Copyright (c) 1982, 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


SQL> exec dbms_tts.transport_set_check('OLTP',TRUE,TRUE);


PL/SQL procedure successfully completed.


SQL> select * from transport_set_violations;


no rows selected




[oracle@ocm1 ~]$ exp userid=\"oracle as sysdba\" tablespaces=oltp transport_tablespace=y file=oltp.dmp


Export: Release 10.2.0.1.0 - Production on Mon Mar 10 11:00:59 2014


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Password: 


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace OLTP ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              T
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.


[oracle@ocm1 ~]$ ls
1.txt                       a.txt   c.bad                    COUNTRIES_EXT_13150.log  EMP_LOAD2_23401.log  EMP_LOAD4_23683.log  flash      oltp.dmp           sqlldr.log
agentDownload10.2.0.1.0Oui  backup  c.dat                    EMP_LOAD1_22959.log      EMP_LOAD3_23401.log  EMP_LOAD5_23683.log  info1.dat  restart_db_script
agentDownload.linux         bak     COUNTRIES_EXT_12800.log  EMP_LOAD1_23401.log      EMP_LOAD4_23401.log  external.dat         info.dat   sqlldr.ctl


SQL> select file_name from dba_data_files where tablespace_name='OLTP';


FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/prod/Disk1/oltp01.dbf


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@ocm1 ~]$ cd /u01/oracle/oradata/prod/Disk1/
[oracle@ocm1 Disk1]$ ls
control01.ctl  indx01.dbf  redo101.log  redo301.log  redo501.log         system01.dbf  temp03.dbf   users01.dbf
example01.dbf  oltp01.dbf  redo201.log  redo401.log  REGISTRATION01.DBF  temp02.dbf    tools01.dbf


[oracle@ocm1 Disk1]$ pwd
/u01/oracle/oradata/prod/Disk1
[oracle@ocm1 Disk1]$ cp oltp01.dbf /u01/oracle/oradata/emerp


[oracle@ocm1 Disk1]$ cd ..
[oracle@ocm1 prod]$ cd ..
[oracle@ocm1 oradata]$ ls
emerp  emerpbak  prod  prodbak
[oracle@ocm1 oradata]$ cd prod
[oracle@ocm1 prod]$ ls
Disk1  Disk2  Disk3  Disk4  Disk5
[oracle@ocm1 prod]$ cd ..
[oracle@ocm1 oradata]$ cd emerp
[oracle@ocm1 emerp]$ ls
control01.ctl  control03.ctl  mgmt_ecm_depot1.dbf  RC_DATA01.DBF  redo02.log  sysaux01.dbf  temp01.dbf
control02.ctl  mgmt.dbf       oltp01.dbf           redo01.log     redo03.log  system01.dbf  undotbs01.dbf


2)在emerp数据库下
[oracle@ocm1 emerp]$ imp userid=\"oracle as sysdba\" tablespaces=oltp transport_tablespace=y file=/home/oracle/oltp.dmp datafiles=/u01/oracle/oradata/emerp/oltp01.dbf fromuser=oltp touser=oltp


Import: Release 10.2.0.1.0 - Production on Mon Mar 10 11:06:36 2014


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Password: 


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing OLTP's objects into OLTP
. . importing table                            "T"
Import terminated successfully without warnings.


[oracle@ocm1 emerp]$ echo $ORACLE_SID
emerp
[oracle@ocm1 emerp]$ sqlplus oltp/oltp


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 10 11:07:28 2014


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> select name from v$database;


NAME
---------
EMERP


SQL> select * from t;     


        ID NAME
---------- ----------
         1 a
         2 b




在两个数据库下分别做alter tablespace oltp read write
SQL> alter tablespace oltp read write;


Tablespace altered.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26084062/viewspace-1104412/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26084062/viewspace-1104412/