表分区维护的sql(四)
ts);
v_rtn := pro_partition_maintenance(v_row.table_name,
v_partitionchar,
v_partition_name,
to_char(v_sysdate, 'YYYYMMDD'),
v_row.preprocess_flag,
v_row.preprocess_sql,
v_row.postprocess_flag,
v_row.postprocess_sql);
if (v_rtn = 0) then
v_comments := 'successful to maintenance table ' ||
v_row.table_name || ', partitionflag:' ||
v_row.partitionflag || ',retention_period:' ||
v_row.retention_period || ',maintenance partiton:' ||
v_partition_name;
else
v_comments := 'fail to maintenance table ' || v_row.table_name ||
', partitionflag:' || v_row.partitionflag ||
',retention_period:' || v_row.retention_period ||
',maintenance partiton:' || v_partition_name;
end if;
Pro_Record_Log(v_comments, v_comments);
end loop;
close cur_partitiontable;
exception
when others then
if cur_partitiontable%isopen then
close cur_partitiontable;
end if;
raise;
end;
function fun_getTableName(tablename varchar2,
partitionflag varchar2,
v_sysdate varchar2) return varchar2 as
v_return_tabname varchar2(30); www.2cto.com
v_tablename varchar2(60);
v_tabprefix varchar2(30);
begin
v_tabprefix := partitionflag;
v_tablename := v_tabprefix ||
substr(tablename, 1, INSTR(tablename, '_')) ||
replace(substr(tablename, INSTR(tablename, '_')),
'_',
'');
v_return_tabname := substr(v_tablename, 0, 30 - length(v_sysdate)) ||
v_sysdate;
return v_return_tabname;
EXCEPTION
WHEN OTHERS THEN
raise;
end;
procedure pro_createbaktable(v_table_name in varchar2,
v_baktable in varchar2) as
v_sql varchar2(32767);
begin
v_sql := 'create table ' || v_baktable || ' tablespace TAB_CC ' ||
' as select a.* from ' || v_table_name || ' a where 1<>1 ';
EXECUTE IMMEDIATE v_sql;
www.2cto.com
insert into partition_baktable
(table_name, create_date, update_date, state)
values
(v_baktable, sysdate, sysdate, 'A');
commit;
end;
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) AS
v_temp_table varchar2(60);
v_index_name varchar2(60);
v_index_col varchar2(60);
v_index_spec varchar2(60); --存放索引的表空间，对于分区表，此字段为空
v_uniqueness varchar2(30);
v_sql varchar2(32767);
TYPE Tcur IS REF CURSOR;
cur_all_index Tcur;
cur_all_index_col Tcur;
www.2cto.com
BEGIN
v_temp_table := v_table_name;
OPEN cur_all_index FOR 'select INDEX_NAME,UNIQUEness,TABLESPACE_NAME from user_indexes US where table_name =upper(''' || v_temp_table || ''') and Partitioned=''YES''';