表分区维护的sql
最近学习了表分区维护 下面是主要的sql 呵呵
-----------包头
create or replace package P_PKG_partition_maintenance AUTHID CURRENT_USER is
www.2cto.com
Const_Partition_DAY_prefix varchar2(10) := 'P_D_';
Const_Partition_WEEK_prefix varchar2(10) := 'P_W_';
Const_Partition_MONTH_prefix varchar2(10) := 'P_M_';
Const_partition_flag_DAY number := 1;
Const_partition_flag_WEEK number := 2;
Const_partition_flag_YEAR number := 3;
/*
partitionflag 分区周期标志, 1 day , 2 week ,3 month 按天、周、月分区
retention_period 保留期限，分别对应到上面的周期，比如3+1
table_name 分区表名 www.2cto.com
*/
function pro_partition_maintenance(v_table_name varchar2,
v_partitionflag varchar2,
v_partition_name varchar2,
v_sysdate varchar2,
v_preprocessflag number,
v_preprocesssql varchar2,
v_postprocessflag number,
v_postprocesssql varchar2) return number;
/*
记录日志
*/ www.2cto.com
PROCEDURE Pro_Record_Log(v_operation in VARCHAR2, v_comments in VARCHAR2);
procedure pro_maintenance;
/*
获取备份表名，v_Partition_prefix 加上原表名的第一个下划线前部分 加上原表名第一个下划线部分替换掉下划线 + 日期
比如:acct_item, 那么
*/ www.2cto.com
function fun_getTableName(tablename varchar2,
partitionflag varchar2,
v_sysdate varchar2) return varchar2;
PROCEDURE Pro_Create_Backup_Part_Indx(v_bk_table_name in varchar2,
v_partitionflag in varchar2,
v_table_name in varchar2,
v_in_date in VARCHAR2);
procedure pro_createbaktable(v_table_name in varchar2,
v_baktable in varchar2);
procedure PRO_DROPBAKTABLE(v_num number);
end P_PKG_partition_maintenance;
------------------包体
create or replace package body P_PKG_partition_maintenance is
--GRANT debug any procedure, debug connect session TO ccv68;
function pro_partition_maintenance(v_table_name varchar2,
v_partitionflag varchar2,
v_partition_name varchar2,
v_sysdate varchar2,
v_preprocessflag number,
v_preprocesssql varchar2,
v_postprocessflag number,
v_postprocesssql varchar2)
return number as
v_sql varchar2(4000);
v_baktable varchar2(30);
www.2cto.com
begin
--获取备份表名
v_baktable := fun_getTableName(v_table_name, v_partitionflag, v_sysdate);
--创建备份表
begin
pro_createbaktable(v_table_name, v_baktable);
exception
when others then
Pro_Record_Log('an error happed when creating ' || v_table_name ||
'''s backup table:' || v_baktable || '.',
'ErrCode:' || SQLCODE || SUBSTR(SQLERRM, 1, 1500));
return - 1;
end;
--创建备份表上的索引，只允许建立分区索引
begin
Pro_Create_Backup_Part_Indx(v_baktable,
v_partitionflag,
v_table_name,
v_sysdate);
exception w