oracle数据磅导入,expdp/impdp完成传输表空间

[oracle@RHEL5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 7 12:39:19 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

1、创建表空间

SQL> create tablespace impdp_trans datafile '/u01/app/oracle/oradata/shujukuai/impdp_trans01.dbf' size 5m autoextend on;

Tablespace created.

2、创建表

SQL> create table t(x int) tablespace impdp_trans;

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

X

----------

1

3、验证自包含

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('impdp_trans',true);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS

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

Sys owned object  T in tablespace IMPDP_TRANS not allowed in pluggable set                           看来得换个用户来搞咯

SQL> create user impdp_trans identified by impdp_trans default tablespace impdp_trans;

User created.

SQL> grant connect,resource to impdp_trans;

Grant succeeded.

SQL> conn impdp_trans/impdp_trans

Connected.

SQL> create table t(x int) tablespace impdp_trans;

Table created.

SQL> insert into t values(111);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

X

----------

111

SQL> conn / as sysdba

Connected.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('impdp_trans',true);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS

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

Sys owned object  T in tablespace IMPDP_TRANS not allowed in pluggable set

SQL> drop table t;

Table dropped.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('impdp_trans',true);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> alter tablespace impdp_trans read only;

Tablespace altered.

SQL> create directory dir_home as '/u02/backup_files';

Directory created.

SQL> grant read,write on directory dir_home to public;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

4、数据磅导出

[oracle@RHEL5 ~]$ expdp system/root directory=dir_home dumpfile=impdp_trans.dmp  transport_tablespaces=impdp_trans transport_full_check=y

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 07 June, 2010 12:57:06

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dir_home dumpfile=impdp_trans.dmp transport_tablespaces=impdp_trans transport_full_check=y

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

/u02/backup_files/impdp_trans.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:57:29

5、复制数据文件

[oracle@RHEL5 ~]$ cp /u01/app/oracle/oradata/shujukuai/impdp_trans01.dbf /u01/app/oracle/oradata/shujukuai/impdp_trans02.dbf

[oracle@RHEL5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 7 13:03:12 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop tablespace impdp_trans including contents and datafiles;

Tablespace dropped.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

6、传进去

[oracle@RHEL5 ~]$ impdp system/root dumpfile=impdp_trans.dmp directory=dir_home transport_datafiles=/u01/app/oracle/oradata/shujukuai/impdp_trans02.dbf remap_schema=impdp_trans:test

Import: Release 10.2.0.4.0 - 64bit Production on Monday, 07 June, 2010 13:36:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=impdp_trans.dmp directory=dir_home transport_datafiles=/u01/app/oracle/oradata/shujukuai/impdp_trans02.dbf remap_schema=impdp_trans:test

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:36:39

[oracle@RHEL5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 7 13:37:38 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER TABLESPACE impdp_trans read write;

Tablespace altered.

SQL> conn test/test

Connected.

SQL> select * from t;

X

----------

111