Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    VA, USA
    Posts
    2

    Angry Unanswered: Auto delete outdated records ? maybe using trigger in oracle

    I was wondering if there is a way to auto delete records (by a date field)? I've exhausted database forums and have yet to find an answer. My situation is that I have a 120 GB storage hard drive which logs records on a monthly basis. After a month, I backup the data and wipe the hard drive (for next month storage).

    Here are the restrictions which prevent simple solutions. Due to customer requirements, I do not have access to the hard drive during the month, so I can't manually delete records or administer the database. Another restriction by the customer is that due to physical space limitation, I cannot put a larger hard drive into the system or daisy chain more hard drives (to help extend tablespace).

    Once the designated tablespace runs out, oracle output a message "ORA-01653: Unable to extend table by 128 in tablespace DATAFILE"

    My questions are:
    1. Is there a way a trigger can be written to trigger for the ORA-01653 error, and then PL/SQL statements written to delete the oldest records (by a date record I have in the table). If so how?

    2. Is there a simpler solution, given my restrictions?

    THANKS IN ADVANCE FOR ANY ADVICE !!! :-)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Auto delete outdated records ? maybe using trigger in oracle

    You could use DBMS_JOB to set up a scheduled process that deletes records older than a certain date on a daily/weekly/whatever basis.

    Or rather than delete them, you could partition the table by date and then periodically remove the oldest partition and add a new one.

  3. #3
    Join Date
    Oct 2003
    Location
    VA, USA
    Posts
    2

    Re: Auto delete outdated records ? maybe using trigger in oracle

    Originally posted by andrewst
    You could use DBMS_JOB to set up a scheduled process that deletes records older than a certain date on a daily/weekly/whatever basis.

    Or rather than delete them, you could partition the table by date and then periodically remove the oldest partition and add a new one.

    Thanks for the quick reply. I will certainly look into the scheduled processes. The reason I wanted to use a trigger instead of a routine periodic deletion is because we want to maintain as much data as we can, and ONLY delete old data when necessary. So, if say 80 GB worth of data was logged for the month, we'd want to keep all that without deleting anything. On the other hand, if say 140 GB worth of data needed to be logged for the month, we would like to delete the oldest 20 GB worth of data. There is no way to predict how much data will be logged each month as that is dynamic. In this case, a schedule will ensure that the tablespace does not overflow, but it also might erase data which we could use for analysis. Would there be another solution in addition to static scheduled jobs? THANK YOU!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Auto delete outdated records ? maybe using trigger in oracle

    I suppose the scheduled job could determine by examining the system catalog how much data is currently stored, and decide whether to delete anything, and if so how much.

    Mind you, the idea of any automated process deleting records to save space sounds scary - what if someone wants to see those records again?

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Why not create a cronjob / Win-Scheduler that checks the space on the disk, then depending on the size, exports the data, zips it up, FTP's the file to a different server, then deletes the appropriate data off the table in the database.

    Once you verify the FTP was sucessful, you can delete the export file off the original server.

    I would recommend establishing rules in which the company agrees such as:

    1. You can delete any data older than 30 days
    2. If disk is 90% full and data is not older than 30 days, then delete all data older than 20 days

    That is definitely something you can work with I think.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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