Question on dropping tablespace and transportable tablespaces
Date: Thu, 5 Sep 2013 15:49:54 +0000
Message-ID: <1184E7EFAB1D1C47A5038D06F64BE926118FD806_at_XM-MBX-02-PROD.ad.uchicago.edu>
Hello,
We have a user that wants to use transportable tablespaces to consistently refresh a query database for a set of users. (Oracle 11.2.0.1, will be 11.2.0.4 soon)
For example, they have 3 tablespaces in instance A that they do different transformation work on. At different points in the week they will have each of the tablespaces moved over to instance B that the users are running reports against. I would suspect that something like Active Data Guard might be the best solution for this, but they only want the users to see the data when they have completed their work on instance A and are good with it (they do not want the data changing, but just want to allow the users snapshots of data)
Transportable tablespaces seem pretty straight forward, but I am having some questions about what the challenges might be when running dropping and then re-adding the tablespace into a live system. With some quick testing, it seems like I am able to take the tablespace offline (any users running queries would receive an error). The drop of the tablespace proceeds as well with the users receiving an object does not exist. Once the tablespace has been added, then users are able to run selects again.
Is the only impact of this operation that user queries will fail? Is there anything else that I will need to consider? Is there any operation that might cause the 'offline' statement to fail?
Thanks in advance for your help
Mike
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 05 2013 - 17:49:54 CEST