RE: copy tablespace in one database
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <wojciech.skrzynecki@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- 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.
Regards,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wojciech Skrzynecki
Sent: Tuesday, March 25, 2008 4:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: copy tablespace in one database
Hello
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:
First
I have to copy tablespace to the new with new name plus date e.g. tablespace
"name" to the new name_date.
Second:
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.
Rgds.
Wojtek
Other related posts:
