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

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 普通堆表导入为分区表需求

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

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

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

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

实测证明可以。

模拟信息,

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

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

操作过程,

  1. 使用exp导出的数据文件,容量20G左右。
  1. 由于使用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方法。

  1. 测试环境创建间隔分区,

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);
  1. 执行导入

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。

  1. 再次执行,完成导入操作,

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的个人杂货铺,

普通堆表导入为分区表需求 普通堆表导入为分区表需求
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 普通堆表导入为分区表需求


 上一篇
Oracle软件的收费标准 Oracle软件的收费标准
昨天微信群中,有朋友问Oracle的收费价格,eygle发了一份16年版的《Oracle Technology Global Price List》,列出了Oracle几乎所有产品,对应的收费方式以及价格,不仅有数据库,还有Web
下一篇 
统计信息的解锁 统计信息的解锁
测试数据导入,采集统计信息的时候,提示错误信息,使用oerr检索错误号,无此错误号解释。 SQL exec dbms_stats.gather_table_stats(‘USER_A’,’A’,cascade=true);BE