普通表转换分区表操作步骤

普通表转换分区表操作步骤

1. 转分区表原因
生产数据库，一张表，一亿多行数据，绝大部分查询按月为维度做时间范围查询，未分区状态下，查询IO量大，
计划以分区截剪的方式减少IO量，提升前前台查询性能
2. 实施目的
将未分区的xx表以字段为分区键，以在定义的方式转换成按月分区表的时间范围分区表，利且在线重定义的特性，保留权限的不漏赋以及极少时间的影响生产。
3. 注意事项
(1) 数据库若是双节点，只需在其中一个节点的数据库上实施。
(2) 实施中部分命令执行时间较长，命令一旦执行后请不要中断命令的执行一条命令执行完毕后，不能再次重复执行该命令。

4.前期准备 

1）.先用户授权 SQL> grant execute on DBMS_REDEFINITION to ;

dba用户授予权限
SQL> GRANT ALL ON SYS.DBMS_REDEFINITION TO TEST;
Grant succeeded.
SQL>  GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE TO TEST;
Grant succeeded.
回到test用户继续验证

2）.获取原来表的元数据。可通过dbms_metadata.get_ddl获取元数据.
3）.需要确保预先创建的分区能够存放所有已有数据，否则重定义过程会出错。
查看数据时间，重要维度等
--  select min(CREATE_TIME), max(CREATE_TIME) from TAB;
--  select count(*) from TAB where ID is null;

--step 1: 创建中间表
中间表即分区表与原来表的数据结构相同，表名不同而已。可以按时间分区，如数据按月分区。
create table P_T_TAB_UNPART
(
   ID        VARCHAR2(32)         not null,
   CREATE_TIME          TIMESTAMP,
   MODIFY_TIME          TIMESTAMP
)
partition by range(CREATE_TIME)
interval(numtoyminterval (1,'month')) –-11g新特性 间隔分区
(
   partition P201601 values less than (to_date('20160201','yyyyMMdd'))
);

-- step 2:预检，执行校验 ，验证表TAB_UNPART是否可以在线重定义,如果不可以会给出建议
--说明：第一个参数: schema
--      第二个参数: 原表名
--      第三个参数: 常量
set serveroutput on
prompt "exec dbms_redefinition.CAN_REDEF_TABLE"    
exec dbms_redefinition.CAN_REDEF_TABLE('TESTDATA', 'TAB_UNPART', options_flag=>dbms_redefinition.cons_use_pk);


dbms_redefinition.start_redef_table中参数options_flag有
1.dbms_redefinition.cons_use_pk 使用cons_use_pk重定义的时候创建的物化视图是基于普通的刷新模式.
2.dbms_redefinition.cons_use_rowid 使用cons_use_rowid重定义的时候创建的物化视图是基于ROWID刷新的。


--step 3:启动在线重定义过程，执行在线重定义
--说明：需要拷贝数据，可能需要较长时间
--      第一个参数: schema 
--      第二个参数: 原表名
--      第三个参数: 临时表名
--      第四个参数: 常量
prompt "exec dbms_redefinition.START_REDEF_TABLE" 

exec dbms_redefinition.START_REDEF_TABLE('TESTDATA','TAB_UNPART','P_T_TAB_UNPART',

OPTIONS_FLAG=>dbms_redefinition.cons_use_pk);

--step 4: 为每一个索引注册依赖对象 
-- 说明：第一个参数: schema
--       第二个参数: 原表名
--       第三个参数: 临时表名
--       第四个参数: 常量
--       第五个参数: schema
--       第六个参数: 原表索引名
--       第七个参数: 临时表索引名
--
--index: PK_ID
BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
   uname         => 'TESTDATA',
   orig_table    => 'TAB_UNPART',
   int_table     => 'P_T_TAB_UNPART',
   dep_type      => DBMS_REDEFINITION.CONS_INDEX,
   dep_owner     => 'TESTDATA',
   dep_orig_name => 'PK_ID',
   dep_int_name  => 'PK_P_ID');
END;
/

-- step 5: 拷贝依赖对象
-- 说明：第一个参数: schema
--       第二个参数: 原表名
--       第三个参数: 临时表名
--       其它参数: 常量
declare
    num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
   'TESTDATA', 
   'TAB_UNPART',
   'P_T_TAB_UNPART',
   DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors, TRUE, TRUE);
END;
/


-- step 6: 同步数据，执行重定义后的分区数据同步
-- 说明：第一个参数: schema
--       第二个参数: 原表名
--       第三个参数: 临时表名
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TESTDATA', 'TAB_UNPART', 'P_T_TAB_UNPART');
END;
/


-- step 7: 结束重定义过程
--a.检查有没有该表上的事物,如有则等事物结束
--b.检查mv日志，如有很多记录，则再次执行sync过程
--c.专人监控DB情况，此语句超过5s，就要判断是否停止


BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('TESTDATA', 'TAB_UNPART', 'P_T_TAB_UNPART');
END;
/


--BEGIN
--  DBMS_REDEFINITION.ABORT_REDEF_TABLE('TESTDATA', 'TAB_UNPART', 'P_T_TAB_UNPART');
--END;

--/

如果执行在线重定义的过程中出错
可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里
执行：DBMS_REDEFINITION.abort_redef_table('TESTDATA', 'TAB_UNPART', 'P_T_TAB_UNPART')以放弃执行在线重定义。

-- step 8: 事后处理
-- 主键索引处理方法：
-- 1. 原有字段+分区字段，添加local关键字。
-- 2. 索引名字不能和原有索引名字重复

prompt "本次主键ID字段上创建GLOBAL Index"

--step 9: 检查是否成功
select table_name,partitioning_type,partition_count
        From dba_part_tables where table_name='TAB_UNPART';
select partition_name,high_value,tablespace_name
       from dba_tab_partitions where table_name='TAB_UNPART'
       order by partition_position;

-- step 10: 校验完数据，删掉清空中间表
drop table P_T_TAB_UNPART;



-- Step 11: 收集统计信息
begin
  dbms_stats.gather_table_stats(
       ownname=>'TESTDATA',
       tabname=>'TAB_UNPART',                               
       method_opt=>'FOR ALL COLUMNS SIZE AUTO',                       
       degree=>DBMS_STATS.AUTO_DEGREE,
       granularity=>'ALL',
       cascade=>FALSE);
end;
/

Spool off