I have two databases, one production and one development, that have different content and different file systems. However, I want to take a tablespace named 'PRODDTA' from the production database and bring it online in the development database by overwriting a current tablespace named 'TESTDTA'. We were thinking we could take a cold backup of data files from the production box for the 'PRODDTA' tablespace (around 65 GB of data) and place them on the development box, rename the files and SOMEHOW tell the development database to recognize these files as the files we want it to use for the 'TESTDTA' tablespace. Is there a way to get the development database to recognize these unknown files that were never a part of its system? Can we edit a control file backup that has been backed up to trace and simply replace the control file? Has anyone attempted this? The reason we are not considering an export at this time is due to the extraordinary size of the data.
You cannot add a datafile to a database and have the data in that datafile be available to your applications. The closest you can do is to use Oracle's Transportable Tablespace (TTS) feature. With TTS, you copy READ ONLY datafiles from a source database and also create a metadata file describing the contents of the TTS. You can plug the tablespace into the destination database. Oracle TTS is the method that most matches your requirements. Oracle TTS is first available in Oracle 9i. There are two restrictions with this method of moving data. First, both platforms must be the same OS. Second, you cannot rename the tablespace. Oracle 10g removes the first restriction. Oracle 10g also makes available a command to rename tablespaces.
Dig Deeper on Oracle database backup and recovery
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.