PL/sql code to delete rows from table.

 -- DDL TO CREATE DEBUGGING TABLE -----> Monitor First time

-- create TABLE TRACK_TAB(ROWS_DELETED NUMBER,TOTAL NUMBER,START_TIME TIMESTAMP,END_TIME TIMESTAMP,DESC VARCHAR2(60))

-- create index TEST_TAB_INDX on TEST_TAB( CREATION_DATE)

DECLARE

nCount NUMBER:=0;

iCount NUMBER:=0;

 batchSize NUMBER:=5000;

daysOld NUMBER:=30;

 sql1 VARCHAR2(2000);

 starttime timestamp;

BEGIN

sql1 := 'DELETE FROM TEST_TAB WHERE CREATION_DATE < sysdate - ' || daysOld ||' and rownum <= ' || batchSize;

LOOP

starttime:=sysdate;

EXECUTE IMMEDIATE sql1;

nCount := sql%rowcount;

iCount:=iCount+nCount;

insert into TRACK_TAB values (batchSize,iCount,starttime, sysdate,'DELETE old TEST_TAB records ');

commit;

 EXIT WHEN nCount = 0;

END LOOP;

end;