表分区维护的sql(二)
ww.2cto.com
when others then
Pro_Record_Log('an error happend when creating ' || v_baktable ||
'''s index.',
'ErrCode:' || SQLCODE || SUBSTR(SQLERRM, 1, 1500));
return - 2;
end;
--预处理
begin
if (v_preprocessflag = 1) then
v_sql := upper(v_preprocesssql);
v_sql := replace(v_sql, 'V_ORG_TABLENAME', v_table_name);
v_sql := replace(v_sql, 'V_DEST_TABLENAME', v_baktable);
execute immediate v_sql;
commit;
end if;
exception
when others then
Pro_Record_Log('an error happend when preprocessing ' ||
v_baktable || '.', www.2cto.com
'ErrCode:' || SQLCODE || SUBSTR(SQLERRM, 1, 1500) ||
'.sql:' || v_preprocesssql);
return - 4;
end;
--开始执行分区交换
begin
v_sql := 'alter table ' || v_table_name || ' exchange partition ' ||
v_partition_name || ' with table ' || v_baktable ||
' INCLUDING INDEXES update global indexes';
execute immediate v_sql;
exception
when others then
Pro_Record_Log('an error happed when exchanging the partition..',
v_sql || '. ErrCode:' || SQLCODE ||
SUBSTR(SQLERRM, 1, 1500));
return - 3;
end;
--善后处理
begin
if (v_postprocessflag = 1) then
v_sql := upper(v_postprocesssql);
v_sql := replace(v_sql, 'V_ORG_TABLENAME', v_table_name);
v_sql := replace(v_sql, 'V_DEST_TABLENAME', v_baktable);
execute immediate v_sql; www.2cto.com
commit;
end if;
exception
when others then
Pro_Record_Log('exchange partition has sucessful.but an error happend when postprocessing ' ||
v_baktable || '.',
'ErrCode:' || SQLCODE || SUBSTR(SQLERRM, 1, 1500) ||
'.sql:' || v_preprocesssql);
return - 5;
end;
return 0;
EXCEPTION
WHEN OTHERS THEN
www.2cto.com
raise;
end;
PROCEDURE Pro_Record_Log(v_operation in VARCHAR2, v_comments in VARCHAR2) AS
BEGIN
INSERT INTO partition_log
(log_id, Oper_date, operator, COMMENTS)
values
(partition_log_id_seq.Nextval,
sysdate,
substr(v_operation, 1, 255),
substr(v_comments, 1, 4000));
COMMIT;
END;
procedure pro_maintenance as
V_ROW PARTITION_TABLE%rowtype;
TYPE Tcur IS REF CURSOR;
cur_partitiontable Tcur;
v_sql varchar2(4000);
v_comments varchar2(255);
cnt number;
v_sysdate date;
v_partitionchar varchar2(10);
v_day number;
v_rtn number;
v_partition_name varchar2(10);
v_MonthExecution_DAY number;
begin
v_sql := 'select * from partition_table where valid_flag=1';
OPEN cur_partitiontable FOR v_sql; www.2cto.com
loop
<>
fetch cur_partitiontable
into V_ROW;
exit when cur_partitiontable%notfound;
select count(1)
into cnt
from user_tab_partitions
where upper(table_name) = upper(v_row.table_name);
if (cnt = 0) then
v_comments := v_row.table_name ||
' defined in partition_table is not a partiti