The procedure should be flexible enough to accept different tables in different schemas, and there should be some basic logging, too.
Below is how I solved it.
First, grant permission, setup the logging table and add a sequence:
connect / as sysdba grant select on dba_tab_partitions to scott; connect scott/tiger whenever sqlerror continue drop sequence part_maint_seq; create sequence part_maint_seq increment by 1 start with 1 nomaxvalue nominvalue nocycle; drop table partition_maintenance; create table partition_maintenance( opid number not null, table_owner varchar2(8), table_name varchar2(30), partition_name varchar2(30), operation varchar2(10), success char(1), exe_date date, exec_by varchar2(30) default 'automatic', errormsg varchar2(512) ); alter table partition_maintenance add constraint part_maint_pk primary key (opid);
Then, create the stored procedure that will do the job. It requires two parameters to be passed to it: table owner and table name. Note that the procedure will need to be called with invoker's right, since it is accessing DBA_TAB_PARTITION:
connect scott/tiger set serveroutput on CREATE OR REPLACE PROCEDURE DROP_PARTITIONS ( towner IN VARCHAR2, tname IN VARCHAR2) AUTHID CURRENT_USER AS PNAME DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE; L_DATA LONG; V_HVD DATE; V_PURGE_DATE DATE; SQLTORUN VARCHAR2(200); CURSOR PART_NAME IS SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = UPPER(tname) AND TABLE_OWNER = UPPER(towner) AND INTERVAL='YES' ORDER BY partition_position; BEGIN OPEN PART_NAME; FETCH PART_NAME INTO PNAME; IF PART_NAME%ROWCOUNT = 0 THEN dbms_output.put_line('no such table as ' || UPPER(towner) ||'.'||UPPER(tname) ); INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG) VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','N', SYSDATE, 'No table such as '|| towner || '.' || tname ); COMMIT; END IF; WHILE PART_NAME%FOUND LOOP --DBMS_OUTPUT.PUT_LINE( pname ); SELECT HIGH_VALUE INTO L_DATA FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER= UPPER(towner) AND TABLE_NAME = UPPER(tname) AND PARTITION_NAME = PNAME; --dbms_output.put_line('partition name: ' || pname || ' ' || trim (substr ( l_data, 12, 10 ) ) ); V_HVD := TO_DATE(TRIM (SUBSTR ( L_DATA, 12, 10 )) ,'YYYY-MM-DD'); -- dbms_output.put_line('high value date: ' || v_hvd); V_PURGE_DATE := TRUNC(SYSDATE)-35; --dbms_output.put_line('purge date: ' || v_purge_date); IF V_HVD < V_PURGE_DATE THEN DBMS_OUTPUT.PUT_LINE('partition ' || PNAME ||' too old, high value date for partition is: ' || V_HVD); -- drop statement goes here SQLTORUN := 'alter table ' || towner || '.' || tname || ' drop partition ' || PNAME || ' UPDATE INDEXES'; DBMS_OUTPUT.PUT_LINE(SQLTORUN); EXECUTE IMMEDIATE SQLTORUN; -- logging to a separate table goes here INSERT INTO PARTITION_MAINTENANCE (OPID, TABLE_OWNER,TABLE_NAME,PARTITION_NAME, OPERATION, SUCCESS, EXE_DATE, ERRORMSG) VALUES (PART_MAINT_SEQ.NEXTVAL,UPPER(towner),UPPER(tname),PNAME ,'DROP','Y', SYSDATE, 'Partition ' || pname || ' of table ' || towner || '.' || tname || ' was dropped.'); COMMIT; END IF; FETCH PART_NAME INTO PNAME; END LOOP; CLOSE PART_NAME; END; /Finally, create a program of type "STORED_PROCEDURE", define its arguments, and create a job that executes the program on a regular basis:
whenever sqlerror continue set serveroutput on BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => 'DROP_PARTITION_5WEEK' ); DBMS_SCHEDULER.DROP_PROGRAM( program_name => 'DROP_PARTITION_PROG' ); DBMS_SCHEDULER.create_program ( program_name => 'DROP_PARTITION_PROG', program_type => 'STORED_PROCEDURE', program_action => 'DROP_PARTITIONS', number_of_arguments => 2, enabled => FALSE, comments => 'Program to drop partitions with contents older than 35 days..'); DBMS_SCHEDULER.define_program_argument ( program_name => 'DROP_PARTITION_PROG', argument_name => 'towner', argument_position => 1, argument_type => 'VARCHAR2', default_value => 'SCOTT'); DBMS_SCHEDULER.define_program_argument ( program_name => 'DROP_PARTITION_PROG', argument_name => 'tname', argument_position => 2, argument_type => 'VARCHAR2', default_value => 'SALES'); DBMS_SCHEDULER.enable( name => 'DROP_PARTITION_PROG'); DBMS_SCHEDULER.CREATE_JOB ( job_name => 'DROP_PARTITION_5WEEK', program_name => 'DROP_PARTITION_PROG', start_date => TO_TIMESTAMP_TZ('2015/05/26 21:45:00.000000 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'), repeat_interval => 'FREQ=WEEKLY;', end_date => NULL, enabled => TRUE, comments => 'Job will execute the program drop_partitions.'); END; /
Description/Documentation of Invoker's Rights vs Definer's Rights.
ReplyDeletehttps://docs.oracle.com/database/121/DBSEG/dr_ir.htm#DBSEG663