表分区维护的sql(12)
来源：未知 责任编辑：责任编辑 发表时间:2015-10-08 14:16 点击:次
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
<<here>>
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;
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
