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.
I understand, but we have a limitation in term of disk space. The customer does not wish to purchase new disk.
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.