Oracle傳輸表空間-EXP/IMP
Transport_Tablespace-EXP/IMP
創(chuàng)新互聯(lián)建站于2013年創(chuàng)立，先為江寧等服務(wù)建站，江寧等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢(xún)服務(wù)。為江寧企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
通過(guò)傳輸表空間(EXP/IMP方式)將192.168.3.199數(shù)據(jù)庫(kù)下，chenjc用戶(hù)下的t1表，導(dǎo)入到192.168.3.198數(shù)據(jù)庫(kù)下，chenjc用戶(hù)下；
一查看操作系統(tǒng)版本，數(shù)據(jù)庫(kù)版本
192.168.3.199
[oracle@ogg1 ~]$ cat /etc/issue
Oracle Linux Server release 6.3
SQL> select * from v$version where rownum<=2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
192.168.3.198
[oracle@ogg2 orcl]$ cat /etc/issue
Oracle Linux Server release 6.3
SQL> select * from v$version where rownum<=2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
二創(chuàng)建測(cè)試表空間，測(cè)試用戶(hù)，測(cè)試表
192.168.3.199
SQL> create tablespace chenjc datafile '/u01/app/oracle/oradata/orcl/chenjc01.dbf' size 30m autoextend on;
Tablespace created.
SQL> create user chenjc identified by chenjc default tablespace chenjc;
User created.
SQL> grant connect,resource,dba to chenjc;
Grant succeeded.
SQL> conn chenjc/chenjc
Connected.
SQL> create table t1 as select level id,sysdate as t_date from dual connect by level<=100000;
Table created.
三檢查準(zhǔn)備遷移的表空間是否自包含
SQL> conn /as sysdba
Connected.
SQL> execute dbms_tts.transport_set_check(ts_list=>'CHENJC',incl_constraints=>TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
/*無(wú)返回記錄，說(shuō)明符合傳輸表空間條件*/
四設(shè)置準(zhǔn)備傳輸?shù)谋砜臻g為只讀
SQL> alter tablespace chenjc read only;
Tablespace altered.
五通過(guò)exp工具導(dǎo)出所要傳輸表空間的原數(shù)據(jù)
[oracle@ogg1 ~]$ exp "'sys/oracle as sysdba'" file=chenjc.dmp log=chenjc.log transport_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 the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace CHENJC ...
. exporting cluster definitions
. exporting table definitions
. . exporting table T1
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
/*雙引號(hào)+單引號(hào)*/
/*
模擬平臺(tái)轉(zhuǎn)換（同一平臺(tái)傳輸不需要這步）
SQL> col platform_name for a35
SQL> select * from v$transportable_platform order by platform_id;
RMAN>convert tablespace "TESTSPACE" to platform 'Microsoft Windows IA (32-bit)' format 'd:\TESTSPACE01.DBF' --這個(gè)是轉(zhuǎn)換的目標(biāo)地址
*/
六將數(shù)據(jù)庫(kù)文件和導(dǎo)出的表空間原文件復(fù)制到192.168.3.198服務(wù)器
[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@ogg2 orcl]$ ll -rth
......
-rw-r--r-- 1 oracle oinstall 16K Aug 3 09:43 chenjc.dmp
-rw-r----- 1 oracle oinstall 31M Aug 3 09:44 chenjc01.dbf
......
七目標(biāo)數(shù)據(jù)庫(kù)創(chuàng)建用戶(hù)，指定表空間(目標(biāo)數(shù)據(jù)庫(kù)不能有和將要傳輸表空間同名的表空間)
SQL> create user chenjc identified by chenjc default tablespace users;
User created.
SQL> grant connect,resource,dba to chenjc;
Grant succeeded.
八通過(guò)imp工具導(dǎo)入表空間
[oracle@ogg2 orcl]$ imp "'sys/oracle as sysdba'" file=chenjc.dmp log=chenjc.log
tablespaces=chenjc datafiles='/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 the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing CHENJC's objects into CHENJC
. . importing table "T1"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
/*datafiles必須絕對(duì)路徑*/
九修改用戶(hù)默認(rèn)表空間
SQL> alter user chenjc default tablespace chenjc;
User altered.
十查看
SQL> select name 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 rows selected.
SQL> conn chenjc/chenjc
SQL> select id,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
新聞名稱(chēng)：Oracle傳輸表空間-EXP/IMP
本文URL：http://m.jiaotiyi.com/article/ijpgis.html