oracle 传输表空间

来源：互联网发布：移动卡无法访问网络编辑：程序博客网时间：2024/05/30 23:34

传输表空间：注意事项：要传输的表空间只读，字符集一致，endian_format 格式一致，元数据在system表空间。表空间的自包含，要传输的表空间里的对象不能依赖于其他表空间的对象。

select *fromV$transportable_Platform --ENDIAN_FORMAT格式要一致

先查看两个平台的endian_format

SQL> conn sys/oracle@orclutf as sysdba

已连接。

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROMV$TRANSPORTABLE_PLATFORM tp, V$

ATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME

-------------------------------------------------------------------------------

ENDIAN_FORMAT

----------------------------

Microsoft Windows IA (32-bit)

Little

SQL> conn sys/oracle@ocp as sysdba

已连接。

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROMV$TRANSPORTABLE_PLATFORM tp, V$D

ATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME

--------------------------------------------------------------------------------

ENDIAN_FORMAT

----------------------------

Linux 64-bit for AMD

Little

创建测试环境

1、建立表空间

sys@OCP> CREATE TABLESPACE test_transport

2 datafile'/u01/app/oracle/oradata/OCP/datafile/test_transport.dbf' size 1M autoextend onnext 1M;

2、创建用户

CREATE USER test_user IDENTIFIED BY test_user

DEFAULT TABLESPACE test_transport TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON test_transport

3、授权用户

sys@OCP> GRANT CREATE SESSION,CREATE TABLE TO test_user;

4、创建表

1 CREATE TABLE test_tab(

2 id NUMBER,

3 descriprion VARCHAR2(30),

4 CONSTRAINT pk_test_tab PRIMARY KEY (id)

5* )

test_user@OCP> /

5、插入数据

1 INSERT /*+ APPEND */ INTO test_tab (id,descriprion)

2 SELECT level,

3 'Description for ' || level

4 FROM dual

5* CONNECT BY level <=10000

test_user@OCP> /

test_user@OCP> commit;

6、源数据库端检查：

检查表空间里的对象是否自包含

idle> conn / as sysdba

Connected.

sys@OCP> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_TRANSPORT', incl_constraints =>TRUE);

PL/SQL procedure successfully completed.

7、查询是否有违反

sys@OCP> SELECT * FROM transport_set_violations;

no rows selected

8、检查成功后，切换表空间为只读

sys@OCP> ALTER TABLESPACE test_transport READ ONLY;

Tablespace altered.

9、先进行文件格式转换再导出表空间的源数据，源数据在system表空间上，使用expdp必须创建目录对象

RMAN> convert tablespace test_transport to platform 'MicrosoftWindows IA (32-bit)' format '/home/oracle/tbs_transport.dbf' ;

sys@OCP> CREATE OR REPLACE DIRECTORY dir1 as'/u01/app/oracle/ocp/directories/dir1';

Directory created.

sys@OCP> GRANT READ,WRITE ON DIRECTORY dir1 to system;

现在开始导出表空间的源数据

[oracle@station10 dir1]$ expdp userid=system/oracle directory=dir1transport_tablespaces=test_transport dumpfile=test_transport1.dmplogfile=test_transport.log

Export: Release 10.2.0.1.0 - 64bit Production on Saturday, 03August, 2013 14:59:49

Connected to: Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": userid=system/******** directory=dir1transport_tablespaces=test_transport dumpfile=test_transport1.dmplogfile=test_transport.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfullyloaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

/u01/app/oracle/ocp/directories/dir1/test_transport.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"successfully completed at 15:00:18

如果你使用的是exp那么就不用目录对象了直接使用下面语句就可以

exp userid='system/oracle as sysdba' transport_tablespace=y tablespaces=test_transport   file=test_data.dmp log=test_data_exp.log

10、目标数据库：

因为要传输的表空间属于test_user用户所有所以，目标数据库必须也要有存在一个叫做test_user的用户。

QL> conn sys/oracle@orclutf

SQL> conn sys/oracle@orclutf as sysdba

SQL> CRATE USER test_user identified by test_user;

SQL> CREATE USER test_user identified by test_user;

SQL> GRANT CREATE SESSION, CREATE TABLE TO test_user;

11、创建目录对象授权，将文件传到目标数据库的目录对象下

SQL> CREATE OR REPLACE DIRECTORY dir1 as 'c:\ dir2';

目录已创建。

SQL> GRANT READ,WRITE ON directory dir1 to system;

授权成功。

11开始导入表空间

导入成功后检查数据

SQL> conn test_user/test_user@orclutf

已连接。

SQL> select count(*) from test_tab;

COUNT(*)

----------

10000

12、将目标数据库和源数据库的表空间改为读写

查询表空间是否是导入的可以查看dba_tablespaces plugged_in 字段

SQL> select tablespace_name,plugged_in from dba_tablespaces;

TABLESPACE_NAME PLUGGE

------------------------------------------------------------ ------

SYSTEM NO

UNDOTBS1 NO

SYSAUX NO

TEMP NO

USERS NO

TEST_TRANSPORT YES

SQL> conn sys/oracle@orclutf as sysdba

已连接。

SQL> select status from dba_tablespaces;

STATUS

------------------

ONLINE

READ ONLY

已选择6行。

SQL> alter tablespace test_transport read write;

表空间已更改。

sys@OCP> alter tablespace test_transport read write;

Tablespace altered.