RE: copy tablespace in one database
Date: Tue, 25 Mar 2008 18:49:04 -0400
The relative number of rows to be retained versus the number of rows to be deleted (and preserved by you) and a few other things about the data cycle will guide you to the best solution.
For example, if this is purging a tiny fraction of the data that represents transactions that have been "completed" for the longest, then you probably do want either make use of row movement driving updates or actual row copies followed by deletes.
If this represents getting rid of all the data that has been "handled" today leaving behind only the incompletes to be worked on together with new data added tomorrow, then you're almost certain to be better off to rename today's table and create new tables and copy the data back that is *not* to be purged. Then you have all the data to dispose of as you see fit, including just applying a view that supresses the data by whatever criteria was used to copy it back to the "current" data sets.
Frankly we would need to know a lot more about your information flow to make good suggestions. Things like whether the nightly wave of data needs to be blended together with older data, for example. Do you have support for partitioning? What is the purpose of the nightly purging?
How big is the data to be moved? Is it plausible to create the indexes you need after loading the data? Do your requirements for retaining the data mean you want the same indexes as "current", or would a subset or completely different indexes be better? Often data that is "purged" like this is no longer allowed to be change, so sometimes heavier indexing to support queries more directly is a good choice since you don't have repetitive changes to process for the indexes.
And you have to ask yourself whether effort to make the process fast and efficient is worthwhile, given the plummeting cost of hardware.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Wojciech Skrzynecki
Sent: Tuesday, March 25, 2008 4:51 PM
Subject: copy tablespace in one database
I plan to copy data from all tables to the new tables in new tablespace every night. Why? Old tablespaces will be purged every night and I have to have old date
I think that I have two options:
I have to copy tablespace to the new with new name plus date e.g. tablespace "name" to the new name_date.
Create tables in new tablespace "as select" but what can I do with indexes?
I do not know if first options is possible.
Maybe you have any idea to help me.
WojtekReceived on Tue Mar 25 2008 - 17:49:04 CDT