普通堆表导入为分区表需求

生产某张表数据量1.5亿，因为历史原因，普通堆表，未设置分区表，性能自然有问题，现在需要一套分区测试表，用于性能压测。

一种方案，从生产库导数据至测试，由于原表非分区，需要使用在线重定义，将其定义分区表。这样做有个弊端，就是需要两倍的空间，因为在线重定义，实际使用了物化视图，转换瞬间，才会有锁，因此保证业务的持续，但是测试空间非常有限，不能存储另一张表数据。

设想一下，既然导入的就是数据，如果表已创建，再执行数据导入，是否可以直接进入相应分区？

实测证明可以。

模拟信息，

1. 生产库用户名user_pro，测试库用户名user_a。

2. 表名称为A，生产库为普通表，测试库需要以月为单位，来存储为分区表，此处采用11g的间隔分区，自动创建子分区。

操作过程，

1. 使用exp导出的数据文件，容量20G左右。

2. 由于使用imp，没有数据泵可用的remap参数，且此测试环境，存在和生产库同名的表空间，例如tbs_pro，但user_b默认表空间为tbs_a。若此时用imp执行导入操作，则数据会进入tbs_pro表空间中，而不是tbs_a表空间。因此需要特殊处理一下，

SQL revoke unlimited tablespace from user_a;
Revoke succeeded.

SQL alter user user_a quota 0 on tbs_pro; User altered.

SQL alter user user_a quota unlimited on tbs_a; User altered.

原理其实很简单，创建用户，一般授予resource角色，他会隐含授予unlimited tablespace，需要首先revoke unlimited tablespace，并且让其在tbs_pro中无配额，让其在表空间tbs_a中有unlimited tablespace配额，这样执行imp，由于和生产库同名的表空间，user_a无配额因此无法使用，只能进入user_a默认表空间，间接实现数据泵remap方法。

3. 测试环境创建间隔分区，

create table a (   id                 NUMBER not null,   a_date         DATE not null,   insert_time  TIMESTAMP(6) not null ) partition by range (a_date) interval(numtoyminterval(1,'month')) (partition p0 values less than (to_date('2015-12-01', 'yyyy-mm-dd'))) tablespace tbs_a;

alter table a add constraint pk_a primary key (id);

exec dbms_stats.gather_table_stats(USER, 'A', cascade=true);

4. 执行导入

nohup imp system/oracle file=/home/oracle/a.dmp log=/home/oracle/a.log fromuser=user_pro touser=user_a ignore=y &

Export file created by EXPORT:V11.02.00 via conventional path import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export client uses US7ASCII character set (possible charset conversion) . importing USER_PRO's objects into USER_A . . importing table               "A"

之所以这里用了&，让导入进程可以后台执行，是因为一旦前台执行，远程终端就不能关闭，否则网络中断，执行就会影响，如果使用impdp，则不需要担心，因为他会在Oracle后台以job执行，不受前台进程的影响。

奇怪的是，执行了几个小时，imp进程仍有，于是看了一下alert.log，

ORA-1688: unable to extend table USER_A.A partition SYS_P182 by 128 in                 tablespace TBS_A ORA-1688: unable to extend table USER_A.A partition SYS_P182 by 1024 in                 tablespace TBS_A

原来是回滚表空间满了，

sh orachk.sql tsfree

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M) ------------------------------ ------------ ---------- ------------- ------------ -------------

UNDOTBS1                            5068.63     648784       100.00         1.86       0.00

其实使用imp，可以有一些参数，例如COMMIT，默认值是N，表示完成一张表的导入，才会执行commit，但是这是一张1.5亿数据表，不能一次性提交，

结合COMMIT参数，还有一个BUFFER参数，决定一次导入的行数，不至于仅用COMMIT=Y一条执行一次COMMIT，参数值计算可以用如下方法，

通过user_tables视图，这张测试表行长97字节，一次提交10000条数据，因此设置BUFFER=970000。

5. 再次执行，完成导入操作，

nohup imp system/oracle file=/home/oracle/a.dmp log=/home/oracle/a.log fromuser=user_pro touser=user_a ignore=y commit=y buffer=970000 &

子分区信息可以检索user_tab_partitions视图，local分区索引，可以检索user_ind_partitions视图。

12c之前，对于分区索引，最关键的问题，就是索引的选择，这就要看实际业务需求，比如是否有不带分区键，进行全分区检索的需求，如果有则需要创建全局索引，而且若是需要做分区管理，例如分区删除，则需要注意更新全局索引，否则索引会失效，这是比较麻烦的地方，更新索引过程中，可能会影响业务，如果仅需要本地索引，则相对简单一些，其实通过分区键，已经能限制一定的数据量，如果有一些合适的索引，则会提升检索的效率，当然这是一项系统的工程，需要平衡，毕竟索引多了，检索可能快了，但增删改操作就会慢了，而且磁盘空间要求更高，这和业务系统的实际需求，以及业务场景的关注点，紧密联系，因此需要认真评估和考虑。

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，