oracle 定时器时间分区_oracle 数据库通过job定时删除表分区

编写删除过程。

create or replace procedure delPart as

table_owner varchar2(50); --存储拥有者

table_name varchar2(100); --存储表名

table_subobject_name varchar2(100); --存储分区表名

v_SqlExec varchar2(200); --存储拼接后的语句

cursor fetch_cursor is select OWNER,OBJECT_NAME,SUBOBJECT_NAME,CREATED

from dba_objects

where owner='SYS'

and  object_type ='TABLE PARTITION'

and GENERATED ='Y' --创建表时候创建的第一个分区(GENERATED ='N')是不允许被删除

and object_name in('TEST_PART')

and created<=TRUNC(SYSDATE+3);

begin

for get_cursor in fetch_cursor loop

table_owner:=get_cursor.OWNER;

table_name:=get_cursor.OBJECT_NAME;

table_subobject_name:=get_cursor.SUBOBJECT_NAME;

--拼接删除分区语句

v_SqlExec:='alter table '|| table_owner ||'.'||table_name||' DROP PARTITION '||table_subobject_name;

--打印语句

DBMS_OUTPUT.PUT_LINE('删除分区'||v_SqlExec);

--执行语句

execute immediate v_SqlExec;

end loop;

end;