这个包提供了将普通表转换为分区表的自动化过程，包括重命名原表、创建CTAS表、按月切分分区、添加表和列注释、处理索引和主键等步骤。此外，还包含了一个主程序`p_main`和一个执行封装`p_do`，以适应不同的执行需求和参数检查。
梁敬彬梁敬弘兄弟出品
1. 前言
随着数据库中数据量的不断增长，将普通表改造为分区表成为优化性能的常见需求。然而，这一过程往往面临多重挑战，包括保留原表的约束、索引、注释等元数据以及确保数据完整性。
本文介绍的PKG_DEAL_PART_TAB包提供了一套完整的解决方案，能够将普通表无缝转换为基于时间范围的分区表，同时保留原表的所有元数据特性。
2. 完整实现代码
2.1 包规范
create or replace package pkg_deal_part_tab
Authid Current_User
as
/*
功能： 实现普通表到分区表自动改造工作
*/
procedure p_rename_001 (p_tab in varchar2 );
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);
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);
procedure p_tab_comments_004 (p_tab in varchar2,p_deal_flag in number);
procedure p_col_comments_005 (p_tab in varchar2,p_deal_flag in number);
procedure p_defau_and_null_006 (p_tab in varchar2,p_deal_flag in number);
procedure p_check_007 (p_tab in varchar2,p_deal_flag in number );
procedure p_index_008 (p_tab in varchar2,p_deal_flag in number,p_idx_tablespace IN VARCHAR2);
procedure p_pk_009 (p_tab in varchar2,p_deal_flag in number,p_idx_tablespace IN VARCHAR2);
procedure p_constraint_010 (p_tab in varchar2,p_deal_flag in number);
/*procedure p_main (p_tab in varchar2,
p_deal_flag in number default 0,
p_parallel in number default 4,
p_part_colum in varchar2,
p_part_nums in number default 24,
p_struct_only in number default 0,
p_tab_tablespace IN VARCHAR2,
p_idx_tablespace IN VARCHAR2);*/
procedure p_do (p_tab in varchar2,
p_deal_flag in number default 0,
p_parallel in number default 4,
p_part_colum in varchar2,
p_part_nums in number default 24,
p_struct_only in number default 0,
p_tab_tablespace IN VARCHAR2,
p_idx_tablespace IN VARCHAR2);
end pkg_deal_part_tab;
/
2.2 包体
create or replace package body pkg_deal_part_tab
as
--YYYYMMDD VARCHAR2(50) :=TO_CHAR(TRUNC(SYSDATE),'yyyymmdd'); --全局生效，很多过程需要引用
YYYYMMDD VARCHAR2(50) :=TO_CHAR(TRUNC(SYSDATE),'mmdd'); --考虑到有的表名比较长，修改为mmdd
procedure p_create_log_tab
as
/*
功能：创建分区日志记录表，以方便记录操作的步骤到part_tab_log表中
*/
v_sql_p_create_log_tab varchar2(4000);
begin
v_sql_p_create_log_tab:=
' create table PART_TAB_LOG
(
TAB_NAME VARCHAR2(200),
DEAL_MODEL VARCHAR2(200),
SQL_TEXT VARCHAR2(4000),
DEAL_TIME DATE,
remark VARCHAR2(4000),
exec_order1 number,
exec_order2 number
)';
execute immediate v_sql_p_create_log_tab;
dbms_output.put_line('程序已经自动完成了PART_TAB_LOG表的建立');
end p_create_log_tab;
procedure p_insert_log(p_tab in varchar2,v_deal_model in varchar2,v_sql_text in varchar2,v_remark in varchar2 default null,v_exec_order1 number default 0,v_exec_order2 number default 0)
as
/*
功能：记录日志，将各步骤中拼的SQL保存起来
难点：因为part_tab_log是程序自己建的，所以需要改造为如下动态SQL，构造中要考虑单引号的替换
*/
v_sql_p_insert_log varchar2(4000);
v_sql_text1 varchar2(4000);
begin
--insert into PART_TAB_LOG (TAB_NAME ,deal_model,SQL_TEXT,DEAL_TIME,REMARK,exec_order1,exec_order2) values (p_tab,v_deal_model,v_SQL_text,SYSDATE,v_remark,v_exec_order1,v_exec_order2);
v_sql_text1 := REPLACE(v_sql_text,'''',''''''); ---先将字符串中的单引号用双引号代替
v_sql_p_insert_log:= 'insert into PART_TAB_LOG (TAB_NAME ,deal_model,SQL_TEXT,DEAL_TIME,REMARK,exec_order1,exec_order2) values ('||''''||p_tab||''''||','||''''||v_deal_model||''''||','||''''||v_SQL_text1||''''||','||''''||SYSDATE||''''||','||''''||v_remark||''''||','||v_exec_order1||','||v_exec_order2||')';
--DBMS_OUTPUT.PUT_LINE( v_sql_p_insert_log);--调试使用
/*
仅仅调试使用，有的时候由于ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line导致DBMS_OUTPUT不可用，所以第一建立一个仅有一个VARCHAR2(4000)字段的表，将字符串插入其中来调试
INSERT INTO TEST VALUES (v_sql_p_insert_log);
COMMIT;
*/
execute immediate v_sql_p_insert_log;
commit;
end p_insert_log;
procedure p_if_judge(v_sql in varchar2,p_deal_flag in number )
as
/*
功能：在获取到V_SQL的动态SQL后，是否EXECUTE IMMEDIATE执行前做一个判断，
根据p_deal_flag的入参而定，0为不执行，非0为执行
*/
begin
if p_deal_flag=0 then
null;
else
execute immediate(v_sql);
end if;
end p_if_judge;
procedure p_rename_001 (p_tab in varchar2)
as
/*
功能：将原表重命名为_yyyymmdd格式的表名
完善点： 要考虑RENMAE的目标表已存在的情况，先做判断
*/
V_CNT_RE_TAB NUMBER(9) :=0;
v_sql_p_rename varchar2(4000);
begin
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
procedure p_main (p_tab in varchar2, --需要进行分区改造的表名
p_deal_flag in number default 0, --0为不执行，只将脚本记录进part_tab_log的sql_text字段中，1为不仅记录，而且执行！
p_parallel in number default 4, --并行度设置
p_part_colum in varchar2, --需要分区的列（时间范围分区）
p_part_nums in number default 24, --需要分区的分区数
p_struct_only in number default 0, --新表是否是只建表结构不导入数据，0为只建结构，非0如1等值，为导数据
p_tab_tablespace IN VARCHAR2, --分区的数据表空间
p_idx_tablespace IN VARCHAR2) --分区的索引表空间
as
/*
功能：主程序，调用前面的各个子程序
完善点：1.既要考虑只建新表结构的情况，也要考虑建新分区表同时将数据导入的情况；
2.既要考虑只记录日志将来手工执行的情况，也要考虑直接执行的情况
3.分区字段必须是时间字段，因为本程序主要是针对时间来做范围分区的。
*/
v_sql_p_main varchar2(4000);
v_data_type varchar2(40);
v_cnt_tab_log varchar2(40);
begin
select DATA_TYPE into v_data_type from user_tab_columns where table_name=upper(p_tab) and column_name=upper(p_part_colum);--用来后续判断分区列是否是时间字段（注意大写）
SELECT COUNT(*) INTO v_cnt_tab_log FROM USER_TABLES WHERE TABLE_NAME='PART_TAB_LOG';
IF v_data_type ='DATE' THEN ---开始对输入分区列是否是时间类型进行判断
if v_cnt_tab_log=0 then
p_create_log_tab;---日志表不存在则创建日志表
end if;
v_sql_p_main :='delete from part_tab_log where tab_name='||''''||p_tab||'''';
--DBMS_OUTPUT.PUT_LINE(v_sql);--调试使用
execute immediate v_sql_p_main ;
commit;
p_rename_001 (p_tab); ---将原表先RENAME为YYYYMMDD的后缀名的表
p_ctas_002 (p_tab, p_struct_only ,p_deal_flag, p_part_colum,p_parallel ,p_tab_tablespace ); ---CTAS建立除部分分区的分区表
p_split_part_003 (p_tab,p_deal_flag,p_part_nums ,p_tab_tablespace); ---将分区表分区完整
p_tab_comments_004 (p_tab,p_deal_flag); --增加分区表的表注释
p_col_comments_005 (p_tab,p_deal_flag); --增加分区表的列注释
p_defau_and_null_006 (p_tab,p_deal_flag); --增加分区表的默认值
p_check_007 (p_tab,p_deal_flag); --增加分区表的CHECK
p_index_008 (p_tab,p_deal_flag,p_idx_tablespace); --增加分区表的索引
p_pk_009 (p_tab,p_deal_flag,p_idx_tablespace); --增加分区表的主键
p_constraint_010 (p_tab,p_deal_flag );---增加外键
if p_deal_flag=0 then
DBMS_OUTPUT.PUT_LINE('请特别留意!!!，以上只是对'||p_tab ||'进行生成脚本动作，未真正执行分区改造等执行动作，表也未真正执行RENAME动作，具体脚本可从part_tab_log中获取从而手工执行,具体如下：');
dbms_output.put_Line('select sql_text'||'||'||''';'''||' from part_tab_log t where tab_name='||''''||p_tab||''''||' order by exec_order1,exec_order2;');
DBMS_OUTPUT.PUT_LINE('如果需要真正完成执行动作，请将pkg_deal_part_tab.p_main的 p_deal_flag参数值设置为非0值，如1');
execute immediate 'rename '||P_TAB||'_'||YYYYMMDD ||' to '||P_TAB; --即便只获取脚本而无需执行，前面RENAME的动作也必须真实完成，因为很多数据字典信息要取自_YYYYMM表的信息，因此前面必须执行，只好在这里最后再RENAME替换回去
ELSE
DBMS_OUTPUT.PUT_LINE('操作步骤结束----对'||p_tab ||'已经真正完成了操作分区改造等执行步骤，脚本可参看part_tab_log表');
end IF;
ELSE
RAISE_APPLICATION_ERROR(-20066,'分区字段'||p_part_colum||'必须是时间字段');
END IF;
end p_main;
procedure p_do (p_tab in varchar2, --需要进行分区改造的表名
p_deal_flag in number default 0, --0为不执行，只将脚本记录进part_tab_log的sql_text字段中，1为不仅记录，而且执行！
p_parallel in number default 4, --并行度设置
p_part_colum in varchar2, --需要分区的列（时间范围分区）
p_part_nums in number default 24, --需要分区的分区数
p_struct_only in number default 0, --新表是否是只建表结构不导入数据，0为只建结构，非0如1等值，为导数据
p_tab_tablespace IN VARCHAR2, --分区的数据表空间
p_idx_tablespace IN VARCHAR2) --分区的索引表空间
as
/*
功能：将主程序p_main做一层封装，将输入表名，输入数据表空间和输入索引表空间这三个参数是否
正确做了判断。由于p_main里调用的001-010这10个模块是DDL语句，不能在一个事务中，所以尽量考虑
逻辑在前面判断正确后再执行这个P_MAIN
*/
v_cnt_p_tab number;
v_cnt_tab_tablespace varchar2(40);
v_cnt_idx_tablespace varchar2(40);
begin
select count(*) into v_cnt_p_tab from user_tables where table_name=upper(p_tab);
select count(*) into v_cnt_tab_tablespace from user_tablespaces where tablespace_name=upper(p_tab_tablespace);
select count(*) into v_cnt_idx_tablespace from user_tablespaces where tablespace_name=upper(p_idx_tablespace);
if v_cnt_p_tab>0 then
if v_cnt_tab_tablespace>0 then
if v_cnt_idx_tablespace>0 then
p_main(p_tab,p_deal_flag, p_parallel,p_part_colum,p_part_nums, p_struct_only,p_tab_tablespace,p_idx_tablespace);
else
RAISE_APPLICATION_ERROR(-20067,'输入的索引表空间'||p_idx_tablespace||'不存在，请仔细检查输入的索引表空间名');
end if;
else
RAISE_APPLICATION_ERROR(-20068,'输入的数据表空间'||p_tab_tablespace||'不存在，请仔细检查输入的数据表空间名');
end if;
else
RAISE_APPLICATION_ERROR(-20069,'输入参数的表名'||p_tab||'不存在，请仔细检查表名');
end if;
end p_do;
end pkg_deal_part_tab;
/
3. 使用方法
3.1 部署包
将上述包规范和包体部署到目标数据库后，确认包创建成功：
SELECT object_name, status FROM user_objects
WHERE object_type = ‘PACKAGE’ AND object_name = ‘PKG_DEAL_PART_TAB’;
3.2 执行转换
使用p_do过程执行普通表到分区表的转换：
-- 设置输出缓冲区
SET SERVEROUTPUT ON SIZE 1000000
-- 执行转换
BEGIN
pkg_deal_part_tab.p_do(
p_tab => '表名', -- 要转换的表名
p_deal_flag => 1, -- 1=执行转换，0=仅生成脚本
p_parallel => 4, -- 并行度
p_part_colum => '分区列名', -- 分区键列名（必须是DATE类型）
p_part_nums => 24, -- 分区数量（按月）
p_struct_only => 1, -- 1=迁移数据，0=仅结构
p_tab_tablespace => '表空间', -- 表数据表空间
p_idx_tablespace => '表空间' -- 索引表空间
);
END;
/
参数说明：
p_tab：需要转换的表名
p_deal_flag：0=仅生成脚本不执行，1=执行转换
p_parallel：数据转换的并行度
p_part_colum：用于分区的DATE类型列名
p_part_nums：要创建的分区数量（默认24个月）
p_struct_only：0=仅创建结构，1=创建结构并迁移数据
p_tab_tablespace：表数据表空间
p_idx_tablespace：索引表空间
3.3 实际应用案例
创建测试环境：
-- 创建测试表
CREATE TABLE T1 (ID1 NUMBER, ID2 NUMBER, PRIMARY KEY (ID1, ID2));
CREATE TABLE T3 (ID1 NUMBER, PRIMARY KEY (ID1));
CREATE TABLE T2 (
T2ID NUMBER DEFAULT 8,
T1ID1 NUMBER NOT NULL,
T1ID2 NUMBER,
deal_date DATE,
PRIMARY KEY (T2ID, T1ID1),
FOREIGN KEY (T1ID1, T1ID2) REFERENCES T1(ID1, ID2),
FOREIGN KEY (T2ID) REFERENCES T3(ID1)
);
-- 添加约束和索引
ALTER TABLE T2 ADD CONSTRAINT CKC_T2 CHECK (T1ID1 >= 0);
COMMENT ON TABLE T2 IS '分区测试表';
COMMENT ON COLUMN T2.T2ID IS '标识';
CREATE INDEX idx_t1id2 ON t2(t1id1);
CREATE UNIQUE INDEX idx_t2id2 ON t2(t1id2);
-- 插入测试数据
INSERT INTO t1 SELECT rownum, rownum+1 FROM dual CONNECT BY level <= 200;
INSERT INTO t3 SELECT rownum FROM dual CONNECT BY level <= 200;
INSERT INTO t2 SELECT rownum, rownum+1, rownum+2, SYSDATE FROM dual CONNECT BY level <= 100;
COMMIT;
执行转换：
-- 执行转换
BEGIN
pkg_deal_part_tab.p_do(
p_tab => 'T2',
p_deal_flag => 1,
p_parallel => 4,
p_part_colum => 'DEAL_DATE',
p_part_nums => 24,
p_struct_only => 1,
p_tab_tablespace => 'USERS',
p_idx_tablespace => 'USERS'
);
END;
/
4. 总结
PKG_DEAL_PART_TAB包提供了一个高效、安全的解决方案，用于将Oracle普通表转换为分区表。它通过精心设计的步骤，确保元数据的完整迁移和数据的安全转换，特别适合用于生产环境中的表结构优化工作。
通过这个工具包，DBA可以大大简化普通表到分区表的转换过程，减少人为错误，并确保转换的完整性和可靠性。
三分钟讲述个人感悟——感恩，回馈
公众号：收获不止数据库
扫一扫
615
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
