【DP】数据泵重新映射表空间

我们都知道system表空间我们最好是不要使用,可是万一就有些表不小心使用了system表空间,那么该如何处理呢?

方法有很多,刚刚测试了一下利用数据泵重新映射表空间的功能,还是很方便的。

 

1:创建测试用户及相应测试表(测试表T1使用system表空间,T2使用users表空间)

SYS@ORCL>grant connect,resource to xxf identified by oracle;

 

Grant succeeded.

 

SYS@ORCL>conn xxf/oracle

Connected.

XXF@ORCL>create table t1 tablespace system as select * from all_objects;

 

Table created.

 

XXF@ORCL>create table t2 tablespace users as select * from all_objects;

 

Table created.

 

XXF@ORCL>create table t3 tablespace example as select * from all_objects;

 

Table created.

 

XXF@ORCL>select table_name,tablespace_name from user_tables;

 

TABLE_NAME                     TABLESPACE_NAME

—————————— ——————————

T1                             SYSTEM

T3                             EXAMPLE

T2                             USERS

 

XXF@ORCL>

 

2:准备数据泵环境,创建目录并将目录的相应权限赋予xxf用户

XXF@ORCL>conn / as sysdba

Connected.

SYS@ORCL>create or replace directory my_dir as ‘/home/oracle';

 

Directory created.

 

SYS@ORCL>grant read,write on directory my_dir to xxf;

 

Grant succeeded.

 

SYS@ORCL>

 

3:利用数据泵导出schema

[oracle@ocmu ~]$ expdp xxf/oracle directory=my_dir dumpfile=xxf.dmp schemas=xxf

 

Export: Release 10.2.0.1.0 – Production on Thursday, 24 January, 2013 23:05:37

 

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

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

Starting “XXF”.”SYS_EXPORT_SCHEMA_01″:  xxf/******** directory=my_dir dumpfile=xxf.dmp schemas=xxf

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 15 MB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . exported “XXF”.”T1″                             3.858 MB   40690 rows

. . exported “XXF”.”T2″                             3.858 MB   40689 rows

. . exported “XXF”.”T3″                             3.858 MB   40691 rows

Master table “XXF”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

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

Dump file set for XXF.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/xxf.dmp

Job “XXF”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 23:05:48

 

[oracle@ocmu ~]$

 

4:删除测试用户xxf,并重新创建xxf用户,同时将目录的权限赋予xxf用户

SYS@ORCL>drop user xxf cascade;

User dropped.

SYS@ORCL>grant connect,resource to xxf identified by oracle;

Grant succeeded.

SYS@ORCL>grant read,write on directory my_dir to xxf;

Grant succeeded.

SYS@ORCL>

 

5:利用数据泵,将dmp文件导入刚刚创建的xxf用户(注意,本次导入使用了表空间的映射)

[oracle@ocmu ~]$ impdp xxf/oracle directory=my_dir dumpfile=xxf.dmp REMAP_TABLESPACE=system:users

 

Import: Release 10.2.0.1.0 – Production on Thursday, 24 January, 2013 23:06:35

 

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

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

Master table “XXF”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded

Starting “XXF”.”SYS_IMPORT_FULL_01″:  xxf/******** directory=my_dir dumpfile=xxf.dmp REMAP_TABLESPACE=system:users

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported “XXF”.”T1″                             3.858 MB   40690 rows

. . imported “XXF”.”T2″                             3.858 MB   40689 rows

. . imported “XXF”.”T3″                             3.858 MB   40691 rows

Job “XXF”.”SYS_IMPORT_FULL_01″ successfully completed at 23:06:39

 

[oracle@ocmu ~]$

 

6:验证,system表空间已经完美的转换为users表空间

XXF@ORCL>select TABLE_NAME,TABLESPACE_NAME from user_tables;

 

TABLE_NAME                     TABLESPACE_NAME

—————————— ——————————

T2                             USERS

T1                             USERS

T3                             EXAMPLE

 

XXF@ORCL>

 

小结:

1:这种方法比较适合数据相对较多的数据迁移场景。

2:另外转换表空间的方法很多,比如可以通过exp导出,然后在目标库先把所有的创建上,在创建的时候,指定正确的表空间,在imp的时候,使用ignore=y来忽略创建错误

3:如果网络是连通的,也可以通过dblink直接写入等等,不过这些办法都适合那些数据相对较少的场景

4:往往还有这种情况,不是数据迁移,而是当前环境中的某些表使用了system表空间,那么可以采用move的方式转换表空间(不过需要注意的是,move以后,行地址都发生了变化,需要重新rebuild索引,还有,move的时候,会影响表的正常使用),当然,10g以后还有一个功能叫在线重定义,很好的解决了move的缺陷,不过相对move来说,使用起来不如move方便。

 

其实,这些问题最好的解决办法是从根上解决,那就是,在创建用户的时候,一定要指定SYSAUXSYSTEM表空间的限额为0,这样,就彻底解决了误使用SYSTEM尴尬情况。