例如原始非分区表为
create table TESTUSER.LOG_TEST
(
"id" CHAR(36) primary key,
"created_at" DATE
)一、 选择重定义方法
- By key，选择主键或者所有字段有NOT NULL约束的唯一键用于在线重定义操作。使用这种方法，在线重定义之前和之后表应该有相同的主键字段（默认）
- By rowid，如果没有主键可以使用这种方法，使用这种方法，隐藏字段M_ROW$$被添加到重定义后的表中。
- 我们表有主键(ID列)，选择By key方式
二、 转换前准备
1. 验证表是否可在线重定义
exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TESTUSER','LOG_TEST',DBMS_REDEFINITION.CONS_USE_PK);如果表能作为在线重定义表的候选表，不会有结果返回；如果不能，这个过程会提示一个错误，说明为什么该表不能在线重定义。
2. 建空分区表作为中间表
不要加索引和约束，否则转换时会报错
create table TESTUSER.LOG_PAR
(
"id" CHAR(36),
"created_at" DATE
)partition by range("created_at")
interval (numtodsinterval(1,'day')) --一天
(
partition p_day_1 values less than (to_date('2019-09-20 00:00:00','YYYY-MM-DD HH24:MI:SS'))
);3. 启用会话并行功能（可选，加速）
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;三、 进行转换
1. 开始在线重定义
会将源表复制一份，注意空间使用情况
exec DBMS_REDEFINITION.START_REDEF_TABLE('TESTUSER','LOG_TEST','LOG_PAR');如果START_REDEF_TABLE因为某种原因失败，必须调用ABORT_REDEF_TABLE过程，否则接下来重新定义表将失败。
2. 同步依赖对象
若前面创建了索引或约束，再执行这步就会报错
variable err_num number;
exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'TESTUSER',orig_table=>'LOG_TEST',int_table=>'LOG_PAR',copy_indexes=>dbms_redefinition.cons_orig_params,num_errors=>:err_num);3. 转换期间增量数据同步
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TESTUSER','LOG_TEST','LOG_PAR');4. 完成在线重定义
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TESTUSER','LOG_TEST','LOG_PAR');四、 转换后工作
1. 表结构验证
-- 中间表变为非分区表
select table_name,partition_name from dba_tab_partitions where table_name='LOG_PAR';
-- 目标表变为分区表
select table_name,partition_name from dba_tab_partitions where table_name='LOG_TEST';2. 删除中间表
drop table TESTUSER.LOG_PAR;3. 创建存储过程，清理指定日期前数据
-- 删除p_table_name分区表p_before_date天前的数据
create or replace procedure snpcenter.drop_patition(p_before_date number,
p_table_name varchar2) as
v_sql varchar(400);
v_table_name user_tab_partitions.table_name%type;
v_partition_name user_tab_partitions.partition_name%type;
v_high_value varchar(200);
v_created_date date;
v_req_date date;
cursor cur1 is
select table_name, partition_name, high_value
from user_tab_partitions
where partition_name like 'SYS%' --第一个分区不能被drop
and table_name = p_table_name;
begin
open cur1;
loop
fetch cur1
into v_table_name, v_partition_name, v_high_value;
exit when cur1%notfound;
v_created_date := to_date(substr(v_high_value, 11, 10), 'YYYY-MM-DD');
v_req_date := to_date(to_char(sysdate - p_before_date + 1,
'YYYY-MM-DD'),
'YYYY-MM-DD');
if v_created_date <= v_req_date then
v_sql := 'alter table ' || v_table_name || ' drop partition ' ||
v_partition_name;
execute immediate v_sql;
/* dbms_output.put_line(v_created_date);
dbms_output.put_line(v_req_date);
dbms_output.put_line(v_sql);
dbms_output.put_line('---------------------------------------'); */
end if;
end loop;
close cur1;
end;调用方法
#直接调用
set serveroutput on
EXEC testuser.drop_patition(30,'LOG_TEST');
#job调用，每天凌晨2点删除30天前的分区（注意设置job的时候就会运行一次）
declare
v_job number;
begin
dbms_job.submit(job => v_job,
what => 'testuser.drop_patition(30,''LOG'');',
interval => 'TRUNC(sysdate) + 1 +2/24');
commit;
end;补充脚本，定期移动分区至不同表空间
create or replace package gtadmin.ilm_manage_pkg is
/* p_from_days,p_to_days：移动创建时间在[p_from_days,p_to_days)时间范围内的（子）分区
p_from_tbs：原（子）分区所在表空间
p_to_tbs: 目标表空间
p_owner: 表属主
p_table_name: 待操作表*/
procedure ilm_mv_partition(p_from_days number,
p_to_days number,
p_from_tbs varchar2,
p_to_tbs varchar2,
p_owner varchar2,
p_table_name varchar2);
end ilm_manage_pkg;
/
create or replace package body gtadmin.ilm_manage_pkg is
/*
1. 移动p_before_date天前创建的表分区/子分区至指定表空间
2. 表须以timestamp字段进行分区
3. 执行需要有对应表及表空间权限
*/
procedure ilm_mv_partition(p_from_days number,
p_to_days number,
p_from_tbs varchar2,
p_to_tbs varchar2,
p_owner varchar2,
p_table_name varchar2) as
v_table_name all_tab_partitions.table_name%type;
v_partition_name all_tab_partitions.partition_name%type;
v_sub_par_count all_tab_partitions.SUBPARTITION_COUNT%type;
v_sub_par_name all_tab_subpartitions.SUBPARTITION_NAME%type;
v_high_value varchar(200);
v_sql varchar(400);
v_from_date date;
v_to_date date;
v_create_date date;
cursor cur_partitions is
select a.table_name,
a.partition_name,
a.subpartition_count,
a.high_value,
b.subpartition_name
from all_tab_partitions a, all_tab_subpartitions b
where a.partition_name = b.partition_name
and a.table_name = b.table_name
and a.TABLE_OWNER = upper(p_owner)
and a.table_name = upper(p_table_name)
and (b.TABLESPACE_NAME = upper(p_from_tbs) or (b.TABLESPACE_NAME is null and a.TABLESPACE_NAME = upper(p_from_tbs)));
begin
v_from_date := to_date(to_char(sysdate - p_from_days + 1, 'YYYY-MM-DD'),
'YYYY-MM-DD');
v_to_date := to_date(to_char(sysdate - p_to_days + 1, 'YYYY-MM-DD'),
'YYYY-MM-DD');
open cur_partitions;
loop
fetch cur_partitions
into v_table_name,
v_partition_name,
v_sub_par_count,
v_high_value,
v_sub_par_name;
exit when cur_partitions%notfound;
-- 将v_high_value转换为该分区创建时间
SELECT TO_DATE(TO_CHAR(v_high_value / (1000 * 60 * 60 * 24) +
TO_DATE('1970-01-01 08:00:00',
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD'),
'YYYY-MM-DD')
into v_create_date
FROM DUAL;
-- 创建时间在[p_from_days,p_to_days)范围内
if (v_create_date <= v_from_date) and (v_create_date > v_to_date) then
-- 表无子分区，则仅移动分区
if (v_sub_par_count = 0) then
v_sql := 'alter table ' || p_owner || '.' || v_table_name ||
' MOVE PARTITION ' || v_partition_name || ' TABLESPACE ' ||
p_to_tbs || ' online';
/* dbms_output.put_line(v_create_date);
dbms_output.put_line(v_from_date);
dbms_output.put_line(v_to_date);
dbms_output.put_line(v_sql);
dbms_output.put_line('---------------------------------------'); */
execute immediate v_sql;
-- 表有子分区，则移动子分区
else
v_sql := 'alter table ' || p_owner || '.' || v_table_name ||
' MOVE SUBPARTITION ' || v_sub_par_name ||
' TABLESPACE ' || p_to_tbs || ' online';
/* dbms_output.put_line(v_create_date);
dbms_output.put_line(v_from_date);
dbms_output.put_line(v_to_date);
dbms_output.put_line(v_sql);
dbms_output.put_line('---------------------------------------'); */
execute immediate v_sql;
end if;
end if;
end loop;
close cur_partitions;
end;
end;
/调用
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => 'gtadmin.ilm_manage_pkg.ilm_mv_partition(180,365,''GTADMIN'',''GTADMIN_WARM'',''GTADMIN'',''IM_HISMESSAGE'');',
next_date => to_date('2019-07-10 01:00:00', 'YYYY-MM-DD HH24:MI:SS'),
interval => 'TRUNC(sysdate) + 1 +1/24');
end;
/脚本2
同步旧数据
create or replace procedure gtadmin.data_sync(p_table_name varchar2,
p_begin_date varchar2,
p_end_date varchar2) is
v_begin_msgtime NUMBER(18);
v_end_msgtime NUMBER(18);
v_sql varchar(400);
begin
-- 将输入日期转换为毫秒数
SELECT TO_NUMBER(TO_DATE(p_begin_date, 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000
into v_begin_msgtime
FROM DUAL;
SELECT TO_NUMBER(TO_DATE(p_end_date, 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000+C6
into v_end_msgtime
FROM DUAL;
v_sql := 'insert /*+ append parallel(gtadmin.' || p_table_name ||
',4) */ into gtadmin.' || p_table_name ||
' select /*+ parallel(i,4) */ * from gtadmin.' || p_table_name ||
'@imdb_source i where i.msgtime>=' || v_begin_msgtime || ' and i.msgtime<' ||
v_end_msgtime;
/* dbms_output.put_line(v_begin_msgtime);
dbms_output.put_line(v_end_msgtime);
dbms_output.put_line(v_sql);
dbms_output.put_line('---------------------------------------');*/
dbms_output.put_line(v_sql);
execute immediate v_sql;
commit;
end data_sync;
/调用
ALTER SESSION ENABLE PARALLEL DML;
exec data_sync(p_table_name => 'test0701',p_begin_date => '2019-06-01 00:00:00',p_end_date => '2019-07-01 00:00:00');