我的编程空间,编程开发者的网络收藏夹
学习永远不晚

Oracle 传输表空间-EXP/IMP

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

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

 

 

将数据库文件和导出的表空间原文件复制到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.

 

 

修改用户默认表空间

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

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

Oracle 传输表空间-EXP/IMP

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

编程热搜

目录