I'm not a proponent of purging data. Unless of course, you expect to never see it again.
That word 'archive' rolls of the tongues of managers and consultants pretty easily, but what's behind it? There are a few gotchas with purging and archiving. Let's assume you have some 3 year old data that you need to see again, and it has been purged. Here are some of the possible problems: * Your backup tapes are corrupted * Your new backup hardware can't read the old tapes * Your software no longer understands the format that the data is in. * You have the correct software, but it won't work on the current version of OS on your hardware. * The data format/software/whatever is not well documented * The employees that understood the data 3 years ago have been laid off. * ... lots more stuff Read Bryon Bergeron's "Dark Ages II: When the Digital Data Die" http://www.powells.com/cgi-bin/biblio?inkey=2-0130661074-0 Perhaps much better than archiving the data, is to stick with the idea of moving it to another database, and using lots of cheap disk storage (NAS) or a heirarchical file system to store it. The point being that if it's online somewhere, it will be maintained. Don't purge it till Finance, HR, the IRS and any other stakeholder says it's ok. Only then purge it and archive it to offline tape with the knowledge that you may never see that data again. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/15/2002 05:38 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Data Purging - Approaches Dear List, We need to remove data from our database everyday, so we are plannning to have a scheduled process for this. But the case is that we cannot simply remove the data. This data has to be made available at a later time if required. So this is the process that we have designed. 1. The background process would first insert all the required data from the main database to another database. 2. Now if this successfull, it would be deleted from the main database. 3. The selection criteria on which the data to be purged is found is a business requirement. It is based on some date, but we cannot partition the data based on the date, otherwise we could have done with paritioning and dropping the partition could have been easily done. 4. The data in the second database would be archived in a normal sequence 5. If any user request for the data already purged, the data would be read from the second database and shown to him. Now the issue, the data that has to be moved or deleted in such a way would mount to more that 10 GB of data, so is this method a good solution. Can anybody suggest a better approach for doing this. We are using Oracle 9i database, Weblogic Application server and Java client. We have list partitioned our database. Any other data purging techniques would be greatly appreciated. Regards Prem Chandran N -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).