中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

Oracle 傳輸表空間-EXP/IMP

發布時間:2020-08-04 23:38:59 來源:ITPUB博客 閱讀:145 作者:dbcloudy 欄目:關系型數據庫

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 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

 

 

創建測試表空間,測試用戶,測試表

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.

 

檢查準備遷移的表空間是否自包含

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

/*無返回記錄,說明符合傳輸表空間條件*/

 

設置準備傳輸的表空間為只讀

SQL> alter tablespace chenjc read only;

Tablespace altered.

 

通過exp工具導出所要傳輸表空間的原數據

[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.

/*雙引號+單引號*/

 

/*

模擬平臺轉換(同一平臺傳輸不需要這步)

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'  --這個是轉換的目標地址

*/

 

將數據庫文件和導出的表空間原文件復制到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@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

......

 

目標數據庫創建用戶,指定表空間(目標數據庫不能有和將要傳輸表空間同名的表空間)

SQL> create user chenjc identified by chenjc default tablespace users;

User created.

 

SQL> grant connect,resource,dba to chenjc;

Grant succeeded.

 

通過imp工具導入表空間

[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必須絕對路徑*/

 

修改用戶默認表空間

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

向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

东莞市| 宁德市| 乐平市| 金川县| 平原县| 广元市| 土默特右旗| 宁德市| 平湖市| 彝良县| 仁寿县| 正安县| 辉县市| 瑞昌市| 江城| 武冈市| 焦作市| 江西省| 广丰县| 宝山区| 会东县| 梅河口市| 千阳县| 南通市| 南雄市| 崇文区| 鄂托克旗| 梧州市| 甘洛县| 吐鲁番市| 山阴县| 称多县| 湾仔区| 容城县| 根河市| 深水埗区| 京山县| 淄博市| 广丰县| 涞源县| 常山县|