-
Partition Table
Hi,
We have this requirement
We have a partition table that contains current data and previous year data. The data for each year is in different partition and different tablespace. Only the curreny year partition tablespace is in read and write mode only. The partition tablespace for the earlier year is in read mode only so that the user can view the data but not change the data.
We want to drop partition having data that is two year old. We also need some mechanism such that we are able to put back the partition if the user wished to view the data again.
We are dropping the partition using the below mechanism
Alter table drop partition;
alter tablespace offline; (Reover the space)
drop tablespace test including contents; (Recover the space)
The problem now is two fold.
1) How do we restore back the tablespace, if the user now wants to view the old data.
2) If the table structure of the original partition table is changed due to some enhancement, will there be a any problem when the tablespace is recovered and the dropped
partition is added back to the table.
Waiting for your expert opnion
Thanks
-
If the user wishes to view the data again why would you drop the partition?
what is the goal of dropping the partition?
how large is such a partition?
-
Yeah, this requirement is a bit weird for my taste. What is the benefit of dropping the old data?
-
Hi,
I understand, but we have a limitation in term of disk space. The customer does not wish to purchase new disk.
Tabiul
-
Originally Posted by tabiul
Hi,
I understand, but we have a limitation in term of disk space. The customer does not wish to purchase new disk.
Tabiul
So where are you going to keep the data that you don't want, so that you can restore it later?
How about some really cheap, slow disks, attached by the cheapest method? No RAID, no fuss, just grab some 100GB+ disks and plug them in anywhere. It'd still be cheaper and less hassle.
-
Originally Posted by tabiul
Hi,
I understand, but we have a limitation in term of disk space. The customer does not wish to purchase new disk.
Tabiul
If they don't have enough space now, then how are they going to put the old data back if they want it?
You might want to look at transportable tablespaces. If it is a read only tablespace, you may be able to move it off to some off-line media (CD DVD?), then transport it back in from the CD when/if they want it. Never tried anything like that, but it may work.
John
-
as far as I knwo you can only use transpoprtable tablespace (TT) for self containend partitiones. It's not possible to use TT with a tablespace that contains only some of the partitions of a table.
A workaround may be to execute an alter table exchange partition before making use of TT.
Last edited by mike9; 06-24-2005 at 04:14 AM.
-
In case you are using Oracle9ir2 or Oracle10g you should have a look at table compression. With the same disk space it would probably be possible to store up to 3 times more data.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|