有一系列普通表都有几十到几百GB这么大，数据从几亿到几十亿，现在想将这些表改造成分区表，用其中的时间或者其他字段来做分区，允许有一段停机时间来停这些表相关的应用，该如何做呢？
思路：
新建一张分区表，按日期建分区，确保分区表各字段和属性都和普通表一样。然后停应用，将普通表记录插入到分区表中。然后将普通表重命名，分区表命名成原表的名字，完成任务。
将原表重命名为_yyyymmdd格式的表名：
create or replace procedure p_rename_001(p_tab in varchar2) as
/*
功能：将原表重命名为_yyyymmdd格式的表名
完善点：要考虑RENMAE的目标表已存在的情况，先做判断
*/
v_cnt_re_tab number(9) := 0;
v_sql_p_rename varchar2(4000);
yyyymmdd varchar2(8);
begin
select to_char(sysdate, 'yyyymmdd') into yyyymmdd from dual;
select count(*)
into v_cnt_re_tab
from user_objects
where object_name = upper(p_tab || '_' || yyyymmdd);
if v_cnt_re_tab = 0 then
v_sql_p_rename := 'rename ' || p_tab || ' to ' || p_tab || '_' ||
yyyymmdd;
-- DBMS_OUTPUT.PUT_LINE(v_sql_p_rename);--调试使用
p_insert_log(p_tab,
'P_RENAME',
v_sql_p_rename,
'完成原表的重命名，改为_YYYYMMDD形式',
1);
execute immediate (v_sql_p_rename); --这里无须做判断，rename动作真实完成！如果后续只是为生成脚本而不是真实执行分区操作，最后再把这个表RENAME回去！
else
raise_application_error(-20066,
'备份表' || p_tab || '_' || yyyymmdd ||
'已存在,请先删除或重命名该备份表后再继续执行！');
-- DBMS_OUTPUT.PUT_LINE('备份表'||P_TAB||'_'||YYYYMMDD||'已存在');
end if;
dbms_output.put_line('操作步骤1(备份原表)-------将' || p_tab || ' 表RENMAE成 ' ||
p_tab || '_' || yyyymmdd || '，并删除其约束索引等');
end p_rename_001;用CREATE TABLE AS SELECT 的方式从RENAME的_yyyymmdd表中新建出一个只有MAXVALUE的初步分区表：
create or replace procedure p_ctas_002
(
p_tab in varchar2,
p_struct_only in number,
p_deal_flag in number,
p_part_colum in varchar2,
p_parallel in number default 4,
p_tablespace in varchar2
) as
/*
功能：用CREATE TABLE AS SELECT 的方式从RENAME的_yyyymmdd表中新建出一个只有MAXVALUE的初步分区表
完善点：要考虑并行，nologging 的提速方式，也要考虑最终将NOLOGGING和PARALLEL恢复成正常状态
*/
v_sql_p_ctas varchar2(4000);
begin
v_sql_p_ctas := 'create table ' || p_tab || ' partition by range ( ' ||
p_part_colum || ' ) (' ||
' partition P_MAX values less than (maxvalue))' ||
' nologging parallel 4 tablespace ' || p_tablespace ||
' as select /*+parallel(t,' || p_parallel || ')*/ *' ||
' from ' || p_tab || '_' || yyyymmdd;
if p_struct_only = 0 then
v_sql_p_ctas := v_sql_p_ctas || ' where 1=2';
else
v_sql_p_ctas := v_sql_p_ctas || ' where 1=1';
end if;
--DBMS_OUTPUT.PUT_LINE(v_sql_p_ctas);--调试使用
p_insert_log(p_tab, 'p_ctas', v_sql_p_ctas, '完成CTAS建初步分区表', 2, 1);
p_if_judge(v_sql_p_ctas, p_deal_flag);
v_sql_p_ctas := 'alter table ' || p_tab || ' logging';
p_insert_log(p_tab,
'p_ctas',
v_sql_p_ctas,
'将新分区表修改回LOGGING属性',
2,
2);
p_if_judge(v_sql_p_ctas, p_deal_flag);
v_sql_p_ctas := 'alter table ' || p_tab || ' noparallel';
p_insert_log(p_tab,
'p_ctas',
v_sql_p_ctas,
'将新分区表修改回NOPARALLEL属性',
2,
3);
p_if_judge(v_sql_p_ctas, p_deal_flag);
dbms_output.put_line('操作步骤2(建分区表)-------通过CTAS的方式从 ' || p_tab || '_' ||
yyyymmdd || ' 中新建' || p_tab || '表，完成初步分区改造工作');
end p_ctas_002;对分区表进行分区SPLIT工作
create or replace procedure p_split_part_003
(
p_tab in varchar2,
p_deal_flag in number,
p_part_nums in number default 24,
p_tab_tablespace in varchar2
) as
/*
功能：用CREATE TABLE AS SELECT 的方式新建出一个只有MAXVALUE的初步分区表进行SPLIT，
按月份进行切分，默认p_part_nums产生24个分区，构造2年的分区表，第一个分区为当前月的
上一个月
*/
v_first_day date;
v_next_day date;
v_prev_day date;
v_sql_p_split_part varchar2(4000);
begin
select to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd')
into v_first_day
from dual;
for i in 1 .. p_part_nums loop
select add_months(v_first_day, i) into v_next_day from dual;
select add_months(v_next_day, -1) into v_prev_day from dual;
v_sql_p_split_part := 'alter table ' || p_tab ||
' split partition p_MAX at ' || '(to_date(''' ||
to_char(v_next_day, 'yyyymmdd') ||
''',''yyyymmdd''))' || 'into (partition PART_' ||
to_char(v_prev_day, 'yyyymm') || ' tablespace ' ||
p_tab_tablespace || ', partition p_MAX)';
-- DBMS_OUTPUT.PUT_LINE(v_sql_p_split_part);--调试使用
p_insert_log(p_tab,
'p_split_part',
v_sql_p_split_part,
'分区表完成分区SPLIT工作',
3,
i);
p_if_judge(v_sql_p_split_part, p_deal_flag);
end loop;
dbms_output.put_line('操作步骤3(分区操作)-------对新建的' || p_tab ||
'分区表完成分区SPLIT工作');
end p_split_part_003;从_YYYYMMDD备份表中得到表的注释，为新分区表的表名增加注释
create or replace procedure p_tab_comments_004
(
p_tab in varchar2,
p_deal_flag in number
) as
/*
功能：从_YYYYMMDD备份表中得到表的注释，为新分区表的表名增加注释
*/
v_sql_p_tab_comments varchar2(4000);
v_cnt number;
begin
select count(*)
into v_cnt
from user_tab_comments
where table_name = upper(p_tab) || '_' || yyyymmdd
and comments is not null;
if v_cnt > 0 then
for i in (select *
from user_tab_comments
where table_name = upper(p_tab) || '_' || yyyymmdd
and comments is not null) loop
v_sql_p_tab_comments := 'comment on table ' || p_tab || ' is ' || '''' ||
i.comments || '''';
-- DBMS_OUTPUT.PUT_LINE(v_sql_p_deal_tab_comments);--调试使用
p_insert_log(p_tab,
'p_deal_comments',
v_sql_p_tab_comments,
'将新分区表的表的注释加上',
4,
1);
p_if_judge(v_sql_p_tab_comments, p_deal_flag);
end loop;
dbms_output.put_line('操作步骤4(表的注释)-------对' || p_tab || '表增加表名的注释内容');
else
dbms_output.put_line('操作步骤4(表的注释)-------' || upper(p_tab) || '_' ||
yyyymmdd || '并没有表注释!');
end if;
end p_tab_comments_004;从_YYYYMMDD备份表中得到表和字段的注释，为新分区表的表名和字段增加注释
create or replace procedure p_col_comments_005
(
p_tab in varchar2,
p_deal_flag in number
) as
/*
功能：从_YYYYMMDD备份表中得到表和字段的注释，为新分区表的表名和字段增加注释
*/
v_sql_p_col_comments varchar2(4000);
v_cnt number;
begin
select count(*)
into v_cnt
from user_col_comments
where table_name = upper(p_tab) || '_' || yyyymmdd
and comments is not null;
if v_cnt > 0 then
for i in (select *
from user_col_comments
where table_name = upper(p_tab) || '_' || yyyymmdd
and comments is not null) loop
v_sql_p_col_comments := 'comment on column ' || p_tab || '.' ||
i.column_name || ' is ' || '''' || i.comments || '''';
p_insert_log(p_tab,
'p_deal_col_comments',
v_sql_p_col_comments,
'将新分区表的列的注释加上',
5,
1);
p_if_judge(v_sql_p_col_comments, p_deal_flag);
end loop;
dbms_output.put_line('操作步骤5(列的注释)-------对' || p_tab || '表增加列名及字段的注释内容');
else
dbms_output.put_line('操作步骤5(列的注释)-------' || upper(p_tab) || '_' ||
yyyymmdd || '并没有列注释!');
end if;
end p_col_comments_005;从_YYYYMMDD备份表中得到原表的DEFAULT值，为新分区表的表名和字段增加DEFAULT值
create or replace procedure p_defau_and_null_006
(
p_tab in varchar2,
p_deal_flag in number
) as
/*
功能：从_YYYYMMDD备份表中得到原表的DEFAULT值，为新分区表的表名和字段增加DEFAULT值
*/
v_sql_defau_and_null varchar2(4000);
v_cnt number;
begin
select count(*)
into v_cnt
from user_tab_columns
where table_name = upper(p_tab) || '_' || yyyymmdd
and data_default is not null;
if v_cnt > 0 then
for i in (select *
from user_tab_columns
where table_name = upper(p_tab) || '_' || yyyymmdd
and data_default is not null) loop
v_sql_defau_and_null := 'alter table ' || p_tab || ' modify ' ||
i.column_name || ' default ' ||
i.data_default;
p_insert_log(p_tab,
'p_deal_default',
v_sql_defau_and_null,
'将新分区表的默认值加上',
6);
p_if_judge(v_sql_defau_and_null, p_deal_flag);
end loop;
dbms_output.put_line('操作步骤6(空和默认)-------对' || p_tab ||
'表完成默认DEFAULT值的增加');
else
dbms_output.put_line('操作步骤6(空和默认)-------' || upper(p_tab) || '_' ||
yyyymmdd || '并没有DEFAULT或NULL值!');
end if;
end p_defau_and_null_006;从_YYYYMMDD备份表中得到原表的CHECK值，为新分区表增加CHECK值
create or replace procedure p_check_007
(
p_tab in varchar2,
p_deal_flag in number
) as
/*
功能：从_YYYYMMDD备份表中得到原表的CHECK值，为新分区表增加CHECK值
另注：
user_constraints已经进行了非空的判断，可以略去如下类似的从user_tab_columns获取非空判断的代码编写来判断是否
for i in (select * from user_tab_columns where table_name=UPPER(P_TAB)||'_' ||YYYYMMDD and nullable='N') loop
v_sql:='alter table '||p_tab||' modify '||i.COLUMN_NAME ||' not null';
*/
v_sql_p_check varchar2(4000);
v_cnt number;
begin
select count(*)
into v_cnt
from user_constraints
where table_name = upper(p_tab) || '_' || yyyymmdd
and constraint_type = 'C';
if v_cnt > 0 then
for i in (select *
from user_constraints
where table_name = upper(p_tab) || '_' || yyyymmdd
and constraint_type = 'C') loop
v_sql_p_check := 'alter table ' || p_tab || '_' || yyyymmdd ||
' drop constraint ' || i.constraint_name;
p_insert_log(p_tab,
'p_deal_check',
v_sql_p_check,
'将备份出来的原表的CHECK删除',
7,
1);
p_if_judge(v_sql_p_check, p_deal_flag);
v_sql_p_check := 'alter table ' || p_tab || ' ADD CONSTRAINT ' ||
i.constraint_name || ' CHECK (' ||
i.search_condition || ')';
p_insert_log(p_tab,
'p_deal_check',
v_sql_p_check,
'将新分区表的CHECK加上',
7,
2);
p_if_judge(v_sql_p_check, p_deal_flag);
end loop;
dbms_output.put_line('操作步骤7(check约束)-------对' || p_tab || '完成CHECK的约束');
else
dbms_output.put_line('操作步骤7(check约束)-----' || upper(p_tab) || '_' ||
yyyymmdd || '并没有CHECK!');
end if;
end p_check_007;从_YYYYMMDD备份表中得到原表的索引信息，为新分区表增加普通索引（唯一和非唯一索引，函数索引暂不考虑），并删除旧表索引
create or replace procedure p_index_008
(
p_tab in varchar2,
p_deal_flag in number,
p_idx_tablespace in varchar2
) as
/*
功能：从_YYYYMMDD备份表中得到原表的索引信息，为新分区表增加普通索引（唯一和非唯一索引，函数索引暂不考虑），并删除旧表索引
难点：需要考虑联合索引的情况
*/
v_sql_p_normal_idx varchar2(4000);
v_cnt number;
begin
select count(*)
into v_cnt
from user_indexes
where table_name = upper(p_tab) || '_' || yyyymmdd
and index_type = 'NORMAL'
and index_name not in (select constraint_name from user_constraints);
if v_cnt > 0 then
for i in (with t as
(select c.*, i.uniqueness
from user_ind_columns c,
(select distinct index_name, uniqueness
from user_indexes
where table_name = upper(p_tab) || '_' || yyyymmdd
and index_type = 'NORMAL'
and index_name not in
(select constraint_name from user_constraints)) i
where c.index_name = i.index_name)
select index_name,
table_name,
uniqueness,
max(substr(sys_connect_by_path(column_ name, ','), 2)) str ---考虑组合索引的情况
from (select column_name,
index_name,
table_name,
row_number() over(partition by index_name, table_name order by column_name) rn,
uniqueness
from t) t
start with rn = 1
connect by rn = prior rn + 1
and index_name = prior index_name
group by index_name, t.table_name, uniqueness) loop
v_sql_p_normal_idx := 'drop index ' || i.index_name;
p_insert_log(p_tab,
'p_deal_normal_idx',
v_sql_p_normal_idx,
'删除原表索引',
8,
1);
p_if_judge(v_sql_p_normal_idx, p_deal_flag);
dbms_output.put_line('操作步骤8(处理索引)-------将' || i.table_name || '的' ||
i.str || '列的索引' || i.index_name || '删除完毕');
if i.uniqueness = 'UNIQUE' then
v_sql_p_normal_idx := 'CREATE UNIQUE INDEX ' || i.index_name ||
' ON ' || p_tab || '(' || i.str || ')' ||
' tablespace ' || p_idx_tablespace;
elsif i.uniqueness = 'NONUNIQUE' then
v_sql_p_normal_idx := 'CREATE INDEX ' || i.index_name || ' ON ' ||
p_tab || ' (' || i.str || ')' ||
' LOCAL tablespace ' || p_idx_tablespace;
end if;
p_insert_log(p_tab,
'p_deal_normal_idx',
v_sql_p_normal_idx,
'将新分区表的索引加上',
8,
2);
p_if_judge(v_sql_p_normal_idx, p_deal_flag);
dbms_output.put_line('操作步骤8(处理索引)-------对' || p_tab || '新分区表' ||
i.str || '列增加索引' || i.index_name);
end loop;
else
dbms_output.put_line('操作步骤8(处理索引)-------' || upper(p_tab) || '_' ||
yyyymmdd || '并没有索引(索引模块并不含主键判断)!');
end if;
end p_index_008;从_YYYYMMDD备份表中得到原表的主键信息，为新分区表增加主键值，并删除旧表主键
create or replace procedure p_pk_009
(
p_tab in varchar2,
p_deal_flag in number,
p_idx_tablespace in varchar2
) as
/*
功能：从_YYYYMMDD备份表中得到原表的主键信息，为新分区表增加主键值，并删除旧表主键
难点：需要考虑联合主键的情况
*/
v_sql_p_pk varchar2(4000);
v_cnt number;
begin
select count(*)
into v_cnt
from user_ind_columns
where index_name in (select index_name
from sys.user_constraints t
where table_name = upper(p_tab) || '_' || yyyymmdd
and constraint_type = 'P');
if v_cnt > 0 then
for i in (with t as
(select index_name, table_name, column_name
from user_ind_columns
where index_name in
(select index_name
from sys.user_constraints t
where table_name = upper(p_tab) || '_' || yyyymmdd
and constraint_type = 'P'))
select index_name,
table_name,
max(substr(sys_connect_by_path(column_name, ','), 2)) str
from (select column_name,
index_name,
table_name,
row_number() over(partition by index_name, table_name order by column_name) rn
from t) t
start with rn = 1
connect by rn = prior rn + 1
and index_name = prior index_name
group by index_name, t.table_name) loop
v_sql_p_pk := 'alter table ' || i.table_name || ' drop constraint ' ||
i.index_name || ' cascade';
p_insert_log(p_tab,
'p_deal_pk',
v_sql_p_pk,
'将备份出来的原表的主键删除',
9,
1);
p_if_judge(v_sql_p_pk, p_deal_flag);
dbms_output.put_line('操作步骤9(处理主键)-------将备份出来的原表' || i.table_name || '的' ||
i.str || '列的主键' || i.index_name || '删除完
毕！'); ---放在FOR循环中效率没问题，因为主键只有一个，只会循环一次
v_sql_p_pk := 'ALTER TABLE ' || p_tab || ' ADD CONSTRAINT ' ||
i.index_name || ' PRIMARY KEY (' || i.str || ')' || ' using index tablespace
' || p_idx_tablespace;
p_insert_log(p_tab,
'p_deal_pk',
v_sql_p_pk,
'将新分区表的主键加上',
9,
2);
p_if_judge(v_sql_p_pk, p_deal_flag);
dbms_output.put_line('操作步骤9(处理主键)-------对' || p_tab || '表的' || i.str ||
'列增加主键' || i.index_name); ---放在FOR循环中效率没问题，因为主键只有一个，只会循环一次
end loop;
else
dbms_output.put_line('操作步骤9(处理主键)-------' || upper(p_tab) || '_' ||
yyyymmdd || '并没有主键!');
end if;
end p_pk_009;从_YYYYMMDD备份表中得到原表的外键或主键约束，为新分区表增加外键或主键，并删除旧表的外键或主键
create or replace procedure p_constraint_010
(
p_tab in varchar2,
p_deal_flag in number
) as
/*
功能：从_YYYYMMDD备份表中得到原表的约束，为新分区表增加约束值，并删除旧表约束
难点：需要考虑联合外键REFERENCE的情况
*/
v_sql_p_constraint varchar2(4000);
v_cnt number;
begin
select count(*)
into v_cnt
from user_constraints
where table_name = upper(p_tab) || '_' || yyyymmdd
and constraint_type = 'R';
if v_cnt > 0 then
for i in (with t1 as
(select /*+no_merge */
position,
t.owner,
t.constraint_name as constraint_name1,
t.table_name as table_name1,
t.column_name as column_name1
from user_cons_columns t
where constraint_name in
(select constraint_name
from user_constraints
where table_name = upper(p_tab) || '_' || yyyymmdd
and constraint_type = 'R')),
t2 as
(select /*+no_merge */
t.position,
c.constraint_name constraint_name1,
t.constraint_name as constraint_name2,
t.table_name as table_name2,
t.column_name as column_name2,
max(t.position) over(partition by c.constraint_name) max_position
from user_cons_columns t, user_constraints c
where c.table_name = upper(p_tab) || '_' || yyyymmdd
and t.constraint_name = c.r_constraint_name
and c.constraint_type = 'R'),
t3 as
(select t1.*,
t2.constraint_name2,
t2.table_name2,
t2.column_name2,
t2.max_position
from t1, t2
where t1.constraint_name1 = t2.constraint_name1
and t1.position = t2.position)
select t3.*,
substr(sys_connect_by_path(column_name1, ','), 2) as fk,
substr(sys_ connect_by_path(column_name2, ','), 2) as pk
from t3
where position = max_position
start with position = 1
connect by constraint_name1 = prior constraint_name1
and position = prior position + 1) loop
v_sql_p_constraint := 'alter table ' || p_tab || '_' || yyyymmdd ||
' drop constraint ' || i.constraint_name1;
p_insert_log(p_tab,
'p_deal_constraint',
v_sql_p_constraint,
'删除原表FK外键',
10,
1);
p_if_judge(v_sql_p_constraint, p_deal_flag);
dbms_output.put_line('操作步骤10(处理外键)------将备份出来的' || i.table_name1 || '表的' ||
i.column_name1 || '列的外键' || i.constraint_name1 ||
'删除完毕!');
v_sql_p_constraint := 'alter table ' || p_tab || ' add constraint ' ||
i.constraint_
name1 || ' foreign key ( ' || i.fk ||
') references ' || i.table_name2 || ' (' || i.pk || ' )';
p_insert_log(p_tab,
'p_deal_constraint',
v_sql_p_constraint,
'将新分区表的外键加上',
10,
2);
p_if_judge(v_sql_p_constraint, p_deal_flag);
dbms_output.put_line('操作步骤10(处理外键)------对' || p_tab || '表的' ||
i.column_
name1 || '列增加外键' || i.constraint_name1);
end loop;
else
dbms_output.put_line('操作步骤10(处理外键)------' || upper(p_tab) || '_' ||
yyyymmdd || '并没有外键!');
end if;
end p_constraint_010;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
