CREATE TABLE SI_MESSAGE_AUDIT
   (AUDIT_ID NUMBER(10,0), 
	BUSINESS_SERVICE_ID VARCHAR2(45 BYTE), 
	MSG_RECIEVED_TIMESTAMP TIMESTAMP (6), 
	MESSAGE_TYPE VARCHAR2(20 BYTE), 
	MESSAGE_IDENTIFIER VARCHAR2(500 BYTE), 
	MESSAGE_PAYLOAD CLOB, 
	MESSAGE_PAYLOAD_CCSID NUMBER(4,0), 
	BROKER_NAME VARCHAR2(50 BYTE), 
	MESSAGE_FLOW_NAME VARCHAR2(100 BYTE), 
	EXECUTION_GROUP_NAME VARCHAR2(50 BYTE), 
	INSERT_TIMESTAMP TIMESTAMP (6), 
	UPDATE_TIMESTAMP TIMESTAMP (6), 
	MESSAGE_CORRELATION_ID VARCHAR2(60 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
	  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
	  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	  TABLESPACE WMBDATA 
	 LOB (MESSAGE_PAYLOAD) STORE AS BASICFILE (
	  TABLESPACE WDATA ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
	  NOCACHE LOGGING 
	  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
	PARTITION BY RANGE (Insert_Timestamp)
	INTERVAL (Numtodsinterval(1,'day')) 
	(PARTITION p_first_JUL_PART VALUES LESS THAN (TO_DATE('01-AUG-2013','DD-MON-YYYY')));

--------------------------------------------------------
--  DDL for Index PK_SI_MSG_AUDIT
--------------------------------------------------------

  CREATE UNIQUE INDEX PK_SI_MSG_AUDIT ON SI_MESSAGE_AUDIT (AUDIT_ID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE WDATA ;

create or replace procedure purge_partition(i_tablename varchar2,i_noofdays number) as

v_totpartitions number;
v_high_value varchar2(1024);
v_date date;
v_sql varchar2(300);
v_count number:=0;
v_date_cut date:=sysdate - i_noofdays-1;
v_code number;
v_errm VARCHAR2(1000);
begin

   DBMS_STATS.gather_table_stats(USER,i_tablename);

   select count(*) into v_totpartitions
   FROM USER_TAB_PARTITIONS
   where table_name = upper('i_tablename');


	FOR I IN (SELECT HIGH_VALUE, PARTITION_NAME
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME=i_tablename
    AND PARTITION_POSITION<>1
    ) LOOP

		execute immediate 'SELECT ' ||I.high_value || ' from dual' INTO v_date;
	   IF V_DATE < v_date_cut THEN

      v_count:=v_count +1;
      v_sql:='ALTER TABLE '|| i_tablename||' DROP PARTITION '|| I.PARTITION_NAME || ' update global indexes';
     
       execute immediate v_sql;
	   END IF;
	END LOOP;
 
	 insert into AUDIT_LOG (JOB_NAME, REC_COUNT, DELETED_DATE) 
	 VALUES ('DROPPING BEFORE '||v_date_cut||' DAYS PARTITIONS  FROM '||i_tablename, v_count, sysdate);
 
	COMMIT; 
	
   EXCEPTION
      WHEN OTHERS THEN
         v_code := SQLCODE;
         v_errm := SUBSTR(SQLERRM, 1 , 64);
         insert into audit_log values ('Error while dropping Partition from '||i_tablename||' '|| v_errm,v_code, sysdate);
         commit;
  end purge_partition;
/

PROMPT
PROMPT CREATING SCHEDULER JOB for SI_MESSAGE_AUDIT
PROMPT ===========================================
PROMPT

BEGIN
  DBMS_SCHEDULER.DROP_JOB ('Purging_Audit_data');
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   JOB_NAME           =>  'Purging_Audit_data',
   JOB_TYPE           =>  'plsql_block',
   job_action         =>  'begin purge_partition(''SI_MESSAGE_AUDIT'',14); end;',
   START_DATE         =>   systimestamp-1,
   repeat_interval    =>  'FREQ=DAILY; INTERVAL=1; BYHOUR=23; BYMINUTE=59', /* Runs every day at 11:59 PM*/
   auto_drop          =>   FALSE,
   enabled            =>   true,
   COMMENTS           =>  'Dropping the Daily Audit partitions');
END;
/