Oracle IMPDP如何改变索引表空间

a8w5a9d55w

转自  http://blog.sina.com.cn/s/blog_81095bbb01014d63.html

背景说明：根据数据库最佳实践应该把应用数据的表与索引放在不同的表空间，现在需要用exp/imp转移用户，同时将之前错误地放入数据库表空间的索引挪到独立的索引表空间。因为数据量较大，exp/imp已经占用大量停机时间，所以不能通过rebuild来修改表空间。我的想法是通过利用impdp的特性（remap_tablespace）来实现，分阶段导入数据，把索引导入独立进行并remap tablespace。实践过程中发现一个问题，主键索引是随表导入的，不能通过remap来修改，所以才有预创建空表来更改主键索引的表空间，再导入数据。

下面是实验过程：

【实验准备】

    建实验用的表空间：DAT/IDX

$ sqlplus "/as sysdba"
数据表空间DAT：SQL> Create tablespace DAT datafile '/oracle/oradata_10g/test/dat01.dbf' size 1m;
索引表空间IDX：SQL> Create tablespace IDX datafile '/oracle/oradata_10g/test/idx01.dbf' size 1m;

    建实验用户：test/test，默认表空间：DAT

SQL> create user test identified by "test" default tablespace DAT;
SQL> grant dba to test;

    建实验数据表：

sqlplus test/test
SQL> create table users(id number(8,0) primary key, name varchar(10) not null);
SQL> create index idx_users_name on users(name);
SQL> insert into users values(1,'A');
SQL> insert into users values(2,'B');
SQL> commit;

查看索引：
SQL> select owner,index_name,tablespace_name from dba_indexes where owner='TEST';
OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ----------------
TEST                           IDX_USERS_NAME                 DAT
TEST                           SYS_C0018263                   DAT
可见表users有两个索引，一个主键索引，一个普通索引idx_users_name.都默认位于DAT表空间内。

【导出数据】
SQL> create directory DUMP_DIR1 as '/oracle/oradata_10g/test/dump';
$ cd /oracle/oradata_10g/test/dump
$ expdp schemas=test dumpfile=test.dmp directory=DUMP_DIR1 logfile=exp_test.log
用户：/ as sysdba

【导入数据】

    先清空旧数据：

SQL> drop table users;
确认：SQL> select * from dba_objects where owner='TEST'

    重建表，修改了主键的表空间定义：

查看创建表的DDL语句：
select dbms_metadata.get_ddl('TABLE','USERS','TEST') from dual;

创建表：create table users(id number(8,0) primary key using index tablespace IDX, name varchar(10) not null);
主键索引表空间已修改，如上红色字体所示。


    导入表（不含索引）：

cd /oracle/oradata_10g/test/dump
impdp schemas=test dumpfile=test.dmp directory=DUMP_DIR1 logfile=imp_test_table.log exclude=INDEX,INDEX_STATISTICS TABLE_EXISTS_ACTION=APPEND
因为表已创建，所以只能用APPEND模式来导入数据，这是必需的参数，否则默认会忽略该表的数据导入。

    导入索引（仅索引，不含表）：

impdp schemas=test include=INDEX,INDEX_STATISTICS dumpfile=test.dmp directory=DUMP_DIR1 logfile=imp_test_index.log REMAP_TABLESPACE=DAT:IDX
remap修改了非主键索引的表空间。

    查看索引：

select * from dba_indexes where owner='TEST'
可以看到索引全部表迁移到IDX表空间，而数据表仍然在DAT表空间。

【清除实验数据】
drop user test cascade;
drop tablespace DAT;
drop tablespace IDX;

【补充说明】
如果数据量较小，或者不用计较太长的停机时间，那么可先用常规方法导入数据，然后用下面语句重建索引并修改表空间即可：
alter index IDX_USERS_NAME rebuild tablespace IDX;
alter index SYS_C0018263 rebuild tablespace IDX;