oracle 传输表空间

传输表空间

1.   创建表空间，作为实验传输表空间

[oracle@edt3r10p1~]$ sqlplus / as sysdba

 

SQL*Plus:Release 11.2.0.3.0 Production on Wed Mar 5 15:49:09 2014

 

Copyright (c)1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database1100M Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With thePartitioning, Automatic Storage Management, OLAP, Data Mining

and RealApplication Testing options

 

SQL> createtablespace trans_tbs datafile '/home/oracle/trans01.dbf' size 20M;

 

Tablespacecreated.

2.   创建实验用户，用于验证数据。

SQL> createuser trans identified by trans

  2 default tablespace trans_tbs

  3 temporary tablespace temp;

 

User created.

3.   修改用户权限

SQL> alteruser trans quota unlimited on trans_tbs;

 

User altered.

 

SQL> grantconnect to trans;

 

Grant succeeded.

SQL>  grant create table to trans;

 

Grant succeeded.

 

SQL>  grant select on hr.employees  to trans;

 

Grant succeeded.

4.   创建实验表

SQL>  create table trans_tb tablespace trans_tbs

  2    as

  3   select * from hr.employees;

Table created.

SQL> selectcount(*) from trans.trans_tb;

  COUNT(*)

----------

       107

5.   将移动表空间修改为只读模式

SQL> conn /as sysdba

Connected.

SQL> altertablespace trans_tbs read only;

Tablespacealtered.

6.   使用数据泵导出移动表空间元数据

[oracle@edt3r10p1~]$expdpsystem/oracle directory=lin_dir  dumpfile=trans.dumpTRANSPORT_TABLESPACES=trans_tbs

 

Export: Release11.2.0.3.0 - Production on Wed Mar 5 15:57:06 2014

Copyright (c)1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to:Oracle Database 1100M Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With thePartitioning, Automatic Storage Management, OLAP, Data Mining

and RealApplication Testing options

Starting"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=lin_dirdumpfile=trans.dump TRANSPORT_TABLESPACES=trans_tbs

Processingobject type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processingobject type TRANSPORTABLE_EXPORT/TABLE

Processingobject type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

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

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

Dump file setfor SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

  /home/oracle/trans.dump

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

Datafilesrequired for transportable tablespace TRANS_TBS:

  /home/oracle/trans01.dbf

Job"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfullycompleted at 15:58:10

7.   删除表空间（在一个数据库做实验，模拟另外一个数据库）

SQL> droptablesap pace trans_tbs including contents;

Tablespacedropped.

SQL> conntrans/trans

Connected.

SQL> selectcount(*) from trans_tb;

select count(*)from trans_tb

                     *

ERROR at line 1:

ORA-00942: tableor view does not exist

注意：不要同时删除数据文件。

8.   使用数据泵倒入移动表空间元数据

[oracle@edt3r10p1~]$impdp system/oracle directory=lin_dir dumpfile=trans.dump transport_datafiles='/home/oracle/trans01.dbf'

Import: Release11.2.0.3.0 - Production on Wed Mar 5 16:07:57 2014

Copyright (c)1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to:Oracle Database 1100M Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With thePartitioning, Automatic Storage Management, OLAP, Data Mining

and RealApplication Testing options

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

Starting"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=lin_dirdumpfile=trans.dump transport_datafiles=/home/oracle/trans01.dbf

Processingobject type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processingobject type TRANSPORTABLE_EXPORT/TABLE

Processingobject type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfullycompleted at 16:08:01

9.   验证数据

SQL> conn /as sysdba

Connected.

SQL> selectname from v$tablespace;

NAME

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

SYSTEM

SYSAUX

TRANS_TBS

USERS

TEMP

EXAMPLE

TEMP1

UNDOTBS2

8 rows selected.

SQL> conntrans/trans

Connected.

SQL> selectcount(*) from trans.trans_tb;

  COUNT(*)

----------

       107