表分区维护的sql(五)
LOOP
FETCH cur_all_index
INTO v_index_name, v_uniqueness, v_index_spec;
EXIT WHEN cur_all_index%NOTFOUND;
begin
v_sql := NULL;
OPEN cur_all_index_col FOR 'select column_name from user_ind_columns where index_name=upper(''' || v_index_name || ''') order by column_position asc ';
LOOP
FETCH cur_all_index_col
INTO v_index_col;
EXIT WHEN cur_all_index_col%NOTFOUND;
IF v_sql IS NOT NULL THEN
v_sql := v_sql || ',';
END IF; www.2cto.com
v_sql := v_sql || v_index_col;
END LOOP;
CLOSE cur_all_index_col;
if (v_uniqueness = 'UNIQUE') then
v_sql := 'create unique index ' ||
fun_getTableName(v_index_name,
v_partitionflag,
v_in_date) || ' ON ' || v_bk_table_name ||
' ( ' || v_sql || ') tablespace IDX_CC';
else
v_sql := 'create index ' ||
fun_getTableName(v_index_name,
v_partitionflag,
v_in_date) || ' ON ' || v_bk_table_name ||
' ( ' || v_sql || ') tablespace IDX_CC';
end if;
EXECUTE IMMEDIATE v_sql;
EXCEPTION www.2cto.com
WHEN OTHERS THEN
raise;
end;
END LOOP;
CLOSE cur_all_index;
EXCEPTION
WHEN OTHERS THEN
if (cur_all_index_col%isopen) then
close cur_all_index_col;
end if;
if (cur_all_index%isopen) then
close cur_all_index;
end if;
raise;
END;
procedure pro_Dropbaktable(v_num number) as
V_ROW PARTITION_BAKTABLE%rowtype;
TYPE Tcur IS REF CURSOR;
cur_bakpartitiontable Tcur;
v_sql varchar2(4000);
v_version varchar2(6);
v_d_date varchar2(20);
BEGIN
select to_char(add_months(sysdate, -v_num), 'yyyymmddhh24miss')
into v_d_date www.2cto.com
from dual;
select substr(version, 0, INSTR(version, '.', 1, 1) - 1)
into v_version
from Product_component_version
where substr(product, 1, 6) = '
Oracle';
v_sql := 'select p.* from partition_baktable p,user_tables u ' ||
'where upper(p.TABLE_NAME)=u.TABLE_NAME and p.state=''A'' and to_char(p.create_date,''yyyymmddhh24miss'')<' ||
v_d_date;
OPEN cur_bakpartitiontable FOR v_sql;
loop
<>
fetch cur_bakpartitiontable
into V_ROW;
exit when cur_bakpartitiontable%notfound;
if v_version <> '9' then
v_sql := 'drop table ' || V_ROW.TABLE_NAME || ' purge';
else
v_sql := 'drop table ' || V_ROW.TABLE_NAME;
end if;
EXECUTE IMMEDIATE v_sql;
update partition_baktable
set state = 'X', update_date = sysdate
where table_name = V_ROW.TABLE_NAME;
-- EXECUTE IMMEDIATE v_sql;
end loop; www.2cto.com
CLOSE cur_bakpartitiontable;
commit;
END;
begin
NULL;
end P_PKG_partition_maintenance;