exp/imp表空间传输

wreqwrqw

生产中为了方便数据库迁移，oracle提供了表空间传输的方式传输数据库，现在测试通过工具exp、imp迁移表空间

建立表空间及实验数据，完成后设置表空间为read only 模式

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

SQL> col file_name for a50    SQL>  select file_id,file_name,tablespace_name from dba_data_files;    FILE_ID FILE_NAME                                          TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------          4 /u01/app/oracle/oradata/prod/users01.dbf           USERS          3 /u01/app/oracle/oradata/prod/undotbs01.dbf         UNDOTBS1          2 /u01/app/oracle/oradata/prod/sysaux01.dbf          SYSAUX          1 /u01/app/oracle/oradata/prod/system01.dbf          SYSTEM          5 /u01/app/oracle/oradata/test01_new.dbf             TEST          6 /u01/app/oracle/oradata/prod/test02.dbf            TEST          7 /u01/app/oracle/oradata/prod/test1.dbf             TEST1 7 rows selected. SQL> create tablespace exptest datafile '/u01/app/oracle/oradata/prod/exptest01.dbf' size 100m;   Tablespace created. SQL> conn scott/oracle Connected. SQL> create table t01 (id int) tablespace exptest; Table created. SQL> insert into t01 values (1);   1 row created. SQL>  insert into t01 values (2); 1 row created. SQL> insert into t01 values (3);    1 row created. SQL> commit; Commit complete.                                                                                                                SQL> conn / as sysdba Connected. SQL> alter tablespace exptest read only; Tablespace altered.

查看表空间是自否包含

1 2 3 4 5 6 7

SQL> exec sys.dbms_tts.transport_set_check('exptest',true); PL/SQL procedure successfully completed. SQL> select * from sys.transport_set_violations; no rows selected

导出可传输表空间（必须以sysdba权限）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[oracle@ora11g tstbs]$  exp userid=\'sys/oracle as sysdba\' file=exptest.dmp log=exptest.log transport_tablespace=y tablespaces=exptest Export: Release 11.2.0.4.0 - Production on Sat Jul 4 12:30:18 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace EXPTEST ... . exporting cluster definitions . exporting table definitions . . exporting table                            T01 . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings.

查看要导入的目标库另外一个数据库

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

[oracle@ora11g tstbs]$ sqlplus sys/oracle@catdb as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 12:37:50 2015 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS CATALOGTBS 6 rows selected.

在目标数据库下做imp操作，导入表空间

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

[oracle@ora11g tstbs]$ cp /u01/app/oracle/oradata/prod/exptest01.dbf /u01/app/oracle/oradata/catdb/     -------------将表空间数据文件从源数据库拷贝到目标数据库下 [oracle@ora11g tstbs]$  imp userid=\'sys/oracle@catdb as sysdba\' tablespaces=exptest     file=exptest.dmp transport_tablespace=y datafiles=/u01/app/oracle/oradata/catdb/exptest01.dbf Import: Release 11.2.0.4.0 - Production on Sat Jul 4 12:42:30 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SYS's objects into SYS . importing SYS's objects into SYS . importing SCOTT's objects into SCOTT . . importing table                          "T01" . importing SYS's objects into SYS Import terminated successfully without warnings.

验证：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

[oracle@ora11g tstbs]$ sqlplus sys/oracle@catdb as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 12:43:05 2015 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select tablespace_name from dba_tablespaces;    TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS CATALOGTBS EXPTEST 7 rows selected. SQL> select * from scott.t01;

设置表空间为read write

1 2 3

SQL> alter tablespace test read write;                                                                                           Tablespace altered.

完成传输表空间