CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
Peter Penzov
Peter Penzov

Reputation: 1680

Delete Oracle rows based on size

I have this Oracle table which I want to clean from time to time when I reach 2000 rows of data:

CREATE TABLE AGENT_HISTORY(
  EVENT_ID INTEGER NOT NULL,
  AGENT_ID INTEGER NOT NULL,
  EVENT_DATE DATE NOT NULL
)
/

How I can delete the oldest row from the table when the table reaches 2000 rows?

Upvotes: 1

Views: 158

Answers (3)

Shriram M.
Shriram M.

Reputation: 383

Create a DBMS_JOB or DBMS_SCHEDULER, that kicks off after certain interval and call a procedure. In that procedure check the count and delete the rows based on event_date.

Sorry, I didn't see your comment until now. Here is the code you were looking for. Make sure you have the grants to create scheduler program and jobs. This code assumes that the event_id is a sequence of #s and keeps up with the event_date. Otherwise change the rank based on both time and id or of your choice. Also you can change time interval. Check DBMS_SCHEDULER package documentation for any errors and corrections.

create or replace procedure proc_house_keeping is
begin
  delete
    from (
    select rank() over (order by event_id desc) rnk
      from agent_history 
   )
    where rnk > 2000;
  commit;
end;
/

begin
  dbms_scheduler.create_program(
  program_name   => 'PROG_HOUSE_KEEPING',
  program_type   => 'STORED_PROCEDURE',
  program_action => 'PROC_HOUSE_KEEPING',
  number_of_arguments => 0,
  enabled        => FALSE,
  comments       => 'Procedure to delete rows greater than 2000');
end;
/

begin
  dbms_scheduler.create_job(
      job_name => 'table_house_keeping',
      program_name => 'PROG_HOUSE_KEEPING',
      start_date => dbms_scheduler.stime,
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
      end_date => dbms_scheduler.stime+1,
      enabled => false,
      auto_drop => false,
      comments => 'table house keeping, runs every minute');
end;
/

Upvotes: 1

Aleksej
Aleksej

Reputation: 22959

An approach may be adding a trigger to your table, so that it checks and deletes the oldest rows at every INSERT statement; for example, assuming not more than 3 rows:

CREATE OR REPLACE TRIGGER DELETE_3
AFTER INSERT ON AGENT_HISTORY
DECLARE
    vNum    number;
    minDate date;
BEGIN

    delete AGENT_HISTORY
    where (event_id, agent_id, event_date) in
             (  select event_id, agent_id, event_date
                from (        
                        select event_id, agent_id, event_date, row_number() over (order by event_date desc) num
                        from AGENT_HISTORY
                     )
                where num > 3 /* MAX NUMBER OF ROWS = 3*/
             );
END;

Say we insert 5 rows:

SQL> begin
  2      insert into AGENT_HISTORY(EVENT_ID , AGENT_ID, EVENT_DATE) values ( 1, 1, sysdate);
  3      dbms_lock.sleep(1);
  4      insert into AGENT_HISTORY(EVENT_ID , AGENT_ID, EVENT_DATE) values ( 2, 2, sysdate);
  5      dbms_lock.sleep(1);
  6      insert into AGENT_HISTORY(EVENT_ID , AGENT_ID, EVENT_DATE) values ( 3, 3, sysdate);
  7      dbms_lock.sleep(1);
  8      insert into AGENT_HISTORY(EVENT_ID , AGENT_ID, EVENT_DATE) values ( 4, 4, sysdate);
  9      dbms_lock.sleep(1);
 10      insert into AGENT_HISTORY(EVENT_ID , AGENT_ID, EVENT_DATE) values ( 5, 5, sysdate);
 11      commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

we only have the newest 3:

SQL> select * from  AGENT_HISTORY;

  EVENT_ID   AGENT_ID EVENT_DATE
---------- ---------- ---------------------------------------------------------------------------
         3          3 18-FEB-16 17:05:24,000000
         4          4 18-FEB-16 17:05:25,000000
         5          5 18-FEB-16 17:05:26,000000

Upvotes: 0

John Fowler
John Fowler

Reputation: 3281

You can delete all but the newest 2000 rows with the following query:

DELETE FROM agent_history a
 WHERE 2000 < ( SELECT COUNT(1) cnt FROM agent_history b WHERE b.event_date < a.event_date )

The query checks every row in the table (a) to see how many rows have an event_date LESS than that row. If there are more than 2000 rows less than it, then it will delete that row.

Let me know if this doesn't work.

Upvotes: 3

Related Questions