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 tab造婷用痃le_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 点调用。BEGINDBMS_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);
