Limit days stored in DB

Thread Solved

Join Date: Oct 2008
Posts: 17
Reputation: vimotaru is an unknown quantity at this point 
Solved Threads: 1
vimotaru vimotaru is offline Offline
Newbie Poster

Limit days stored in DB

 
0
  #1
Mar 6th, 2009
Hello everybody

My problem is that I'm quite new in oracle admin/dev so I need some help pointing me how to limit the number of days of data stored in a table.
I have some datasources that come in files periodically. I'd like to limit the days stored for each table.

My system is Oracle 10g over Solaris 10.

I though in partitions, but I don't know how to refresh date in them to limit data to 'last 5 days' for example.

Please, if you have some link, example or doc it would be great. I don't know what to search in google to solve this...

Thanks in advance.
Reply With Quote Quick reply to this message  
Join Date: Feb 2007
Posts: 2,104
Reputation: debasisdas will become famous soon enough debasisdas will become famous soon enough 
Solved Threads: 128
debasisdas's Avatar
debasisdas debasisdas is offline Offline
Postaholic

Re: Limit days stored in DB

 
0
  #2
Mar 9th, 2009
The simplest method will be create a view based on the table with last five days record. That will be always refreshed every time you run any query on the view.
Share your Knowledge.
Reply With Quote Quick reply to this message  
Join Date: Oct 2008
Posts: 17
Reputation: vimotaru is an unknown quantity at this point 
Solved Threads: 1
vimotaru vimotaru is offline Offline
Newbie Poster

Re: Limit days stored in DB

 
0
  #3
Mar 9th, 2009
But that doesn't limit the amount of space occupied by the main table. I'd like the disk space remain limited by the number of days and avoiding reject records by not enough space error.

Thanks!!
Reply With Quote Quick reply to this message  
Join Date: Feb 2007
Posts: 2,104
Reputation: debasisdas will become famous soon enough debasisdas will become famous soon enough 
Solved Threads: 128
debasisdas's Avatar
debasisdas debasisdas is offline Offline
Postaholic

Re: Limit days stored in DB

 
0
  #4
Mar 9th, 2009
then you need to populate records from the source to a staging (intermediate) table before actually inserting them to your main table.

so with each load you need to truncate your table and load only the required records.
Share your Knowledge.
Reply With Quote Quick reply to this message  
Join Date: Oct 2008
Posts: 17
Reputation: vimotaru is an unknown quantity at this point 
Solved Threads: 1
vimotaru vimotaru is offline Offline
Newbie Poster

Re: Limit days stored in DB

 
0
  #5
Mar 9th, 2009
Thanks for the quick response. I'm developing a PL/SQL for doing that over a staging table.

I think is the best way to control the data loaded and del the old data.

Thanks!!
Reply With Quote Quick reply to this message  
Reply

This thread has been marked solved.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC