When you create a tablespace, Oracle lets you specify the option to REUSE an existing datafile. However, this option wipes out the contents of that datafile. If the file exists and you do not specify the REUSE option, Oracle will throw an error when you create the tablespace. The REUSE option is simply a way of letting Oracle overwrite the file without throwing an error.
If you want to add a tablespace to the database that is not part of the database, then the correct functionality to use is Oracle's Transportable Tablespaces (TTS -- described in detail in the Oracle 9i Administrator's Guide). The TTS functionality lets you copy a file from one Oracle database to another. Along the way, you'll use Oracle's export utility to create a dump file which describes the contents of the tablespace. To plug in the tablespace to the destination database, you use the import utility. The import utility is told the location of the datafile and the metadata file. In just a few seconds, the tablespace is added to the database.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.