oracle 数据库通过job定时删除表分区
1、创建测试环境 test_part 表，并插入数据。
CREATE TABLE test_part
(
create_time TIMESTAMP
)
PARTITION BY RANGE (create_time) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION create_time_other VALUES LESS THAN (TIMESTAMP' 2019-09-01 00:00:00')) ;
insert into test_part values(to_date('2019-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into test_part values(to_date('2019-09-02 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into test_part values(to_date('2019-09-03 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into test_part values(to_date('2019-09-04 00:00:00','YYYY-MM-DD HH24:MI:SS'));
commit;
2、编写删除过程。
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;
3、添加定时任务，每天1 点调用。
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'delpart_job',
job_type => 'PLSQL_BLOCK',
job_action => 'delPart;',
start_date => SYSDATE,
repeat_interval => 'TRUNC(sysdate+1)+1/24');
END;
4、检劫畜查job是否添加成功
select * from user_scheduler_jobs ;
5、手工调用job，检查job是否能够调用 delPart 过程，并删除分区。
begin
dbms_scheduler.run_job('DELPART_JOB') ;
end;
6、检查job调用日志
select * from dba_scheduler_job_log where job_name='DELPART_JOB';
7、检查分区是否删除。
select OWNER,OBJECT_NAME,SUBOBJECT_NAME,CREATED,created
from dba_objects
where owner='SYS'
and object_type ='TABLE PARTITION'
and GENERATED ='Y'
and object_name in('TEST_PART')
and created<=TRUNC(SYSDATE+3);
