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

短命女 2022-10-21 04:55 176阅读 0赞

\[oracle@RHEL5 ~\]$ sqlplus / as sysdba

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

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;

\----------

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;

\----------

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

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

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

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

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