Home » RDBMS Server » Server Administration » Table with a retention period
Table with a retention period [message #203871] Thu, 16 November 2006 14:13 Go to next message
vatsanc
Messages: 1
Registered: November 2006
Location: New York
Junior Member
Is there a facility in Oracle 10g to retain data in a table for a period (say 6 months) and then drop the data after that ?

Basically, the use case is like this: we want to store historical data going back 6 months. If possible, we want the database server to continuously drop off the data outside the 6 month window. Is this possible with Oracle 10g ?

Thanks.

Report message to a moderator

Re: Table with a retention period [message #203875 is a reply to message #203871] Thu, 16 November 2006 14:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Not by default.
You can write a script that will purge data based on a date column and schedule it to run frequently or even archive the old data by partitioning or something like that.

Report message to a moderator

Re: Table with a retention period [message #205545 is a reply to message #203871] Sat, 25 November 2006 17:18 Go to previous message
oranje66
Messages: 4
Registered: November 2006
Location: Banking Industry, Germany
Junior Member
Hi, consider Oracle's Partitioning Option : Range Partitioning by date. And, drop the partition of data you do not wanna hold anymore.

Only limitition for always available : no global indexes permitted.

I've designed 3 DW with this featuere: we almost have no impact on the running system when dropping a partition.

Good luck

Report message to a moderator

Previous Topic: Problem in deallocating unused space within space
Next Topic: DBMS_JOB : every minute <99.99% "do nothing"> Job disappears
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 06:38:55 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.