Deletes at timed intervals
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Deletes at timed intervals

  1. #1
    Join Date
    Aug 2000
    Posts
    132
    Hi All,

    One of my developers has asked me to come up with a way to delete rows based on a timestamp. He wants the row to be deleted after 48 hours. I was thinking a stored procedure with DBMS_JOB running it at certain intervals. Is this overkill could I use a trigger - after insert if list_dt > SysDate + 48 then delete from...I'm not familar with stored procedures or DBMS_JOB so I don't want to bit off more than I have to. Thanks for the help.

  2. #2
    Join Date
    Jan 2001
    Posts
    28
    From what I understand, you need to delete all records over 48 hours, but do you have to that at a specified time interval ? Because if that is the case, then the trigger during a insert can be ruled out unless you insert records every day.
    I am not sure about the dbms_job package. You could write a small daemon in Pro*C that wakes up every day in the morning and does the cleanup job, else on a unix box, you can put an entry in the chron jobs and have it run at a specific time.
    I think there is a place in Oracle too, where you can specify it, but I am not sure on the name of the file.
    - Mayur.

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    With DBMS_JOBS you can can schedule PL/SQL procedures at a predefined time or a specific interval. With DBMS_JOB you can delete the rows at timed intervals. With triggers you don't know at what time they are fired as tmnath mentioned.

  4. #4
    Join Date
    Jun 2000
    Location
    Conway,AR,USA
    Posts
    29
    If you are using Unix as the OS then you may call the delete script in a cron job and run the cron job at the desired interval. You can run the cron job at the pre determined interval(like first Monday of the month).
    Soumya
    still learning

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width