"N-C-C-1-7-0-1 ... No bloody A, B, C or D" - Captain Montgomery Scott, Star Trek: TNG - 'Relics'
With the topic of this post, a starting quote from Scotty himself seemed apropos.
However, this post has nothing to do with Star Trek at all. Today we'll be talking about Transportable Tablespaces in an Oracle database.
Why do this?
I have a database that I'm responsible for that has three primary schemas inside. When refreshing test from prod, I have to replace everything except one, that contains some sort of metadata for the others, that's specific to the test instance. Replacing this metadata breaks the application in test.
Up until recently, I had been doing an export out of prod and then truncate / import into test. This method is extremely time consuming and I thought that there had to be a better way.
This is, of course, extensively documented in the Oracle Documentation and there are some limitations to this (refer to the link) - but for what I need, this turns out to be an excellent way to get a refresh done in a much faster timeframe.
Setup
The first thing we need to do is to make sure that the tablespaces we're going to be moving are self consistent within the tablespace set. You can't have a dependency in a tablespace that's not a part of the set of tablespaces being moved.
Oracle has a built in procedure to check this for us.
EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TS1_NAME, TS2_NAME, TS3_NAME, ...', TRUE);
The first parameter is a comma separated list of tablespace names, enclosed in single quotes. The second parameter is TRUE or FALSE indicating whether or not referential integrity constraints should be checked (default is false) and a third boolean parameter (default FALSE) indicating whether a "full check" should be performed (refer the documentation for full details).
Once complete, check to see if there are any inconsistencies:
select * from transport_set_violations;
There should be no rows returned.
My next step at this point was to check the owners of the objects in the tablespaces I was moving, since the object owners need to be defined during the import of the tablespaces.
select distinct owner from dba_tables where tablespace_name in ('TS1_NAME','TS2_NAME','TS3_NAME');
select distinct owner from dba_indexes where tablespace_name in ('TS1_NAME','TS2_NAME','TS3_NAME');
Write these down and keep them until later.
The Copy
Next step is to recover the production database to a dummy database on the test DB server. Once the recovery is complete, set the tablespaces being moved into readonly mode.
alter tablespace [name] read only;
Once complete for each tablespace, we need to export the tablespace meetadata:
$ORACLE_HOME/bin/exp transport_tablespace=y tablespaces=ts1_name,ts2_name,ts3_name triggers=y constraints=y grants=y statistics=none file=exp_ts.dmp
When prompted, enter "/ as sysdba" as the username (you'll need to be logged in as the Oracle owner to the operating system).
In my test database that I'm importing to, we need to get rid of the existing tablespaces.
Copy the datafiles from the temporary copy that we recovered above into place for the test database we want to copy into, renaming the files at the OS level if needed.
drop tablespace [name] including contents and datafiles;
The Import
Now, import the tablespace metadata:
$ORACLE_HOME/bin/imp file=exp_ts.dmp tablespaces=TS1_NAME,TS2_NAME,TS3_NAME tts_owners=user1,user2,user3 datafiles=datafile1,datafile2,datafile3 transport_tablespace=y
TTS_OWNERS is the list of schema owners from the earlier query.
When prompted, enter "/ as sysdba" as the username (you'll need to be logged in as the Oracle owner to the operating system).
When the import is complete, for each tablespace do:
alter tablespace [name] read write.
Conclusion
This method, for a non trivially sized database is much faster that import and export and the whole process completes in about a third of the time.
