【expdp】数据泵重新映射表空间
我们都知道system表空间我们最好是不要使用,可是万一就有些表不小心使用了system表空间,那么该如何处理呢?
方法有很多,刚刚测试了一下利用数据泵重新映射表空间的功能,还是很方便的。
1:创建测试用户及相应测试表(测试表T1使用system表空间,T2使用users表空间)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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用户
1 2 3 4 5 6 7 8 9 10 11 12 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[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用户
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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用户(注意,本次导入使用了表空间的映射)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[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表空间
1 2 3 4 5 6 7 8 9 |
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方便。
其实,这些问题最好的解决办法是从根上解决,那就是,在创建用户的时候,一定要指定SYSAUX和SYSTEM表空间的限额为0,这样,就彻底解决了误使用SYSTEM的“尴尬”情况。