Oracle 传输表空间-EXP/IMP
Transport_Tablespace-EXP/IMP
通过传输表空间(EXP/IMP方式)将192.168.3.199数据库下，chenjc用户下的t1表，导入到192.168.3.198数据库下，chenjc用户下；
一 查看操作系统版本，数据库版本
192.168.3.199
[oracle@ogg1~]$ cat /etc/issue
Oracle LinuxServer release 6.3
SQL> select* from v$version where rownum<=2;
BANNER
--------------------------------------------------------------------------------
OracleDatabase 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release11.2.0.3.0 - Production
192.168.3.198
[oracle@ogg2orcl]$ cat /etc/issue
Oracle LinuxServer release 6.3
SQL> select* from v$version where rownum<=2;
BANNER
--------------------------------------------------------------------------------
OracleDatabase 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release11.2.0.3.0 - Production
二 创建测试表空间，测试用户，测试表
192.168.3.199
SQL> createtablespace chenjc datafile '/u01/app/oracle/oradata/orcl/chenjc01.dbf' size 30mautoextend on;
Tablespacecreated.
SQL> createuser chenjc identified by chenjc default tablespace chenjc;
User created.
SQL> grantconnect,resource,dba to chenjc;
Grantsucceeded.
SQL> connchenjc/chenjc
Connected.
SQL> createtable t1 as select level id,sysdate as t_date from dual connect bylevel<=100000;
Table created.
三 检查准备迁移的表空间是否自包含
SQL> conn/as sysdba
Connected.
SQL> executedbms_tts.transport_set_check(ts_list=>'CHENJC',incl_constraints=>TRUE);
PL/SQLprocedure successfully completed.
SQL> select* from transport_set_violations;
no rowsselected
/*无返回记录，说明符合传输表空间条件*/
四 设置准备传输的表空间为只读
SQL> altertablespace chenjc read only;
Tablespacealtered.
五 通过exp工具导出所要传输表空间的原数据
[oracle@ogg1~]$ exp "'sys/oracle as sysdba'" file=chenjc.dmp log=chenjc.logtransport_tablespace=y tablespaces=chenjc
Export:Release 11.2.0.3.0 - Production on Mon Aug 3 09:40:25 2015
Copyright (c)1982, 2011, Oracle and/or its affiliates.All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
Export done inZHS16GBK character set and AL16UTF16 NCHAR character set
Note: tabledata (rows) will not be exported
About toexport transportable tablespace metadata...
For tablespaceCHENJC ...
. exportingcluster definitions
. exportingtable definitions
. . exportingtable T1
. exportingreferential integrity constraints
. exportingtriggers
. endtransportable tablespace metadata export
Exportterminated successfully without warnings.
/*双引号+单引号*/
/*
模拟平台转换（同一平台传输不需要这步）
SQL> col platform_name for a35
SQL> select * from v$transportable_platform order byplatform_id;
RMAN>convert tablespace "TESTSPACE" to platform'Microsoft Windows IA (32-bit)' format 'd:\TESTSPACE01.DBF' --这个是转换的目标地址
*/
六 将数据库文件和导出的表空间原文件复制到192.168.3.198服务器
[oracle@ogg1~]$ scp chenjc.dmp 192.168.3.198:/home/oracle/
[oracle@ogg1~]$ scp /u01/app/oracle/oradata/orcl/chenjc01.dbf 192.168.3.198:/home/oracle/
192.168.3.198
[oracle@ogg2~]$ mv chenjc* /u01/app/oracle/oradata/orcl/
[oracle@ogg2~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@ogg2orcl]$ ll -rth
......
-rw-r--r-- 1oracle oinstall 16K Aug 3 09:43 chenjc.dmp
-rw-r----- 1oracle oinstall 31M Aug 3 09:44 chenjc01.dbf
......
七 目标数据库创建用户，指定表空间(目标数据库不能有和将要传输表空间同名的表空间)
SQL> createuser chenjc identified by chenjc default tablespace users;
User created.
SQL> grantconnect,resource,dba to chenjc;
Grantsucceeded.
八 通过imp工具导入表空间
[oracle@ogg2orcl]$ imp "'sys/oracle as sysdba'" file=chenjc.dmp log=chenjc.log
tablespaces=chenjcdatafiles='/u01/app/oracle/oradata/orcl/chenjc01.dbf' transport_tablespace=y
Import:Release 11.2.0.3.0 - Production on Mon Aug 3 10:14:15 2015
Copyright (c)1982, 2011, Oracle and/or its affiliates.All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
Export filecreated by EXPORT:V11.02.00 via conventional path
About toimport transportable tablespace(s) metadata...
import done inZHS16GBK character set and AL16UTF16 NCHAR character set
. importingSYS's objects into SYS
. importingSYS's objects into SYS
. importingCHENJC's objects into CHENJC
. . importingtable"T1"
. importingSYS's objects into SYS
Importterminated successfully without warnings.
/*datafiles必须绝对路径*/
九 修改用户默认表空间
SQL> alteruser chenjc default tablespace chenjc;
User altered.
十 查看
SQL> selectname from v$dbfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system.dbf
/u01/app/oracle/oradata/orcl/sysaux.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/user01.dbf
/u01/app/oracle/oradata/orcl/ggm01.dbf
/u01/app/oracle/oradata/orcl/chenjc01.dbf
6 rowsselected.
SQL> connchenjc/chenjc
SQL> selectid,to_char(t_date,'yyyy-mm-dd hh34:mi:ss') from t1 where rownum<=3;
ID TO_CHAR(T_DATE,'YYY
-----------------------------
1 2015-08-03 09:27:01
2 2015-08-03 09:27:01
3 2015-08-03 09:27:01
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
