Home » SQL & PL/SQL » SQL & PL/SQL » delete column data every 30 days
delete column data every 30 days [message #376017] Mon, 15 December 2008 12:41 Go to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi,

I got a question for you all. I had a BLOB column in my database. I will insert pdf’s into that BLOB column. If I want to delete the data in that BLOB column for every 30 days, what do I need to do? I am looking for some suggestions.

Thanks
Re: delete column data every 30 days [message #376018 is a reply to message #376017] Mon, 15 December 2008 12:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What do you mean "for every 30 days?"

If you want to delete data older than 30 days, then a simple WHERE clause with the correct condition against one of your table columns is what you need.
Re: delete column data every 30 days [message #376019 is a reply to message #376017] Mon, 15 December 2008 12:52 Go to previous messageGo to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi,

Thanks for the quick reply. The problem is as we are going to store pdf files in BLOB columns, it will occupy lots of space in the database. So, we are planning to delete the data in the BLOB column for every 30 days. That's what our plan is.
So, I dont know, how to achieve my goal. I am waiting for any suggestions.

I heard that, we can use cron jobs for deleting BLOB column data for every 30 days. Is there anything like that or anything other than that.
Re: delete column data every 30 days [message #376020 is a reply to message #376019] Mon, 15 December 2008 13:00 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
vijju56 wrote on Mon, 15 December 2008 13:52
...for deleting BLOB column data for every 30 days.


I do not know what you mean "for every 30 days." Please show an example.
Re: delete column data every 30 days [message #376023 is a reply to message #376020] Mon, 15 December 2008 13:26 Go to previous messageGo to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
I got a table like this:

tmp_pdf
id NUMBER(9,0)
pdf BLOB

Here in our process, user will fill the pdf form online and submit it. we will keep that filled pdf form for 30 days for confirmation details. As our database is small, we want to delete that filled pdf form for every 30 days - to make space for future pdf forms insertion. So, now the question is, how can I delete the data in BLOB column for every 30 days.

Thanks
Re: delete column data every 30 days [message #376027 is a reply to message #376023] Mon, 15 December 2008 13:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You still haven't explained what "for every 30 days." means. It makes no sense.

If you want to delete all rows OLDER than 30 days, then add a date field to your table that you set to sysdate when you insert the row, and then just run a delete every day with the appropriate where-clause to delete all rows with an insert date smaller than sysdate - 30 every day.

[Updated on: Mon, 15 December 2008 13:34]

Report message to a moderator

Re: delete column data every 30 days [message #376031 is a reply to message #376023] Mon, 15 December 2008 13:41 Go to previous messageGo to next message
atric
Messages: 3
Registered: November 2008
Junior Member
Hi,

As far as I understand, you require to delete all pdfs that are more than 30 days old. For this you need to write simple DML statement and schedule this to run at a given time (as per the convenience of the business) every day

You can achieve this either by using Cron job (if the OS is Unix)
OR
DBMS_JOB (if the database version is 9i or below)
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job.htm#ARPLS019
or
DBMS_SCHEDULER (if the database version is 10g or above)
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm
Re: delete column data every 30 days [message #376035 is a reply to message #376031] Mon, 15 December 2008 13:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>For this you need to write simple DML statement and schedule this to run at a given time
If there is no date column, how can you do what OP is requesting?
Re: delete column data every 30 days [message #376037 is a reply to message #376027] Mon, 15 December 2008 13:58 Go to previous messageGo to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi,

Thanks for the reply. Ok, I kept a date column in my table. And will remove the older pdf files which are older than 30 days of the current date. But, we have do that deletions manually. How can we do it dynamically i.e., Is there anything like:
write our program of deleting 30 days data once, and execute it exactly for every 30 days?

Thanks
Re: delete column data every 30 days [message #376038 is a reply to message #376037] Mon, 15 December 2008 14:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
use dbms_job to schedule this within the database
or
just use a cronjob (in unix) or some kind of scheduler you OS supports.
Re: delete column data every 30 days [message #376041 is a reply to message #376017] Mon, 15 December 2008 14:23 Go to previous messageGo to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi atric and all,

Thanks for the replies . I will go through the documentation provided by atric and let you all know if there are any questions.

Thanks
Re: delete column data every 30 days [message #376044 is a reply to message #376041] Mon, 15 December 2008 14:44 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
BLOBS and CLOBS can use a large amount of space whether stored in row (default) or out of line. Be aware of the shrink space command in case you want to recover unused space.

http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html

Previous Topic: Same query different results
Next Topic: Automatic Spool File Generation
Goto Forum:
  


Current Time: Sun Dec 04 13:00:37 CST 2016

Total time taken to generate the page: 0.08579 seconds