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.
dwilliams_at_lifetouch.com
-----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.
- The background process would first insert all the required data from
the main database to another database.
- Now if this successfull, it would be deleted from the main database.
- 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.
- The data in the second database would be archived in a normal
sequence
- 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: DWILLIAMS_at_LIFETOUCH.COM
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: ListGuru_at_fatcity.com (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).
Received on Tue Oct 15 2002 - 09:13:28 CDT
