RE: Question on dropping tablespace and transportable tablespaces

From: Michael Schmitt <>
Date: Thu, 5 Sep 2013 19:43:33 +0000
Message-ID: <>

I can't really see standby as an option with the requirement that they do not want the data changing in the underlying tables until they give the green light. They want to present the users with snapshots of the data that they control. I was thinking it might be possible to do somehow using flashback, but have not looked into it in detail yet


-----Original Message-----

From: Grabowy, Chris [] Sent: Thursday, September 05, 2013 2:19 PM To: Michael Schmitt; Subject: RE: Question on dropping tablespace and transportable tablespaces

I guess a read only standby database is not an option?

-----Original Message-----

From: [] On Behalf Of Michael Schmitt Sent: Thursday, September 05, 2013 11:50 AM To:
Subject: EXTERNAL: Question on dropping tablespace and transportable tablespaces

We have a user that wants to use transportable tablespaces to consistently refresh a query database for a set of users. (Oracle, will be 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



-- Received on Thu Sep 05 2013 - 21:43:33 CEST

Original text of this message