Prem - Since you seem to just be asking for ideas, the following are some random thoughts for your consideration. - I wouldn't give up on partitioning so easily. I felt stymied by partitions for several years. I had a table that I needed to partition, but couldn't figure it out. Finally I made another attempt and was successful. Now we are getting considerable benefit from partitioning. Frankly, there aren't a lot of resources that really show you what partitioning is capable of. The manual examples are rudimentary, to be kindly. - 10-gig/day is a daunting amount of data, any way you cut it. - Deleting 10-gig of data each day will take a terrific amount of resources, which points back to partitioning. - The best way to move this amount of data will be transportable tablespaces, hence take another look at partitioning. Also, you will need to delete this amount of data - You could consider Standby Database to move the data. - Your next problem is that you will need to delete the same amount of data from the second database. - I am assuming that both databases will run on the same platform type so transportable tablespaces are a possibility. - In the end, I think you will discover that, like most archive/purging solutions, this one will prove to be more effort and expense than the benefits provided. - Consider whether the second database is okay with more summarized data. I'm guessing that 10-gig of data represents some pretty detailed data. I find it hard to believe that you need the same amount of data in both locations. Surely one side could get by with summarized data, and use the other system when details are required. This would reduce the processing burden greatly.
Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Tuesday, October 15, 2002 7:38 AM To: Multiple recipients of list ORACLE-L 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: DENNIS WILLIAMS 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).
