Problem solve Get help with specific problems with your technologies, process and projects.

Can't see contents of datafile

In my Oracle9i database, I made a copy of the existing datafile and created a new tablespace. I am unable to see the contents of the datafile.

Hi, I am new to Oracle database administration. In my Oracle9i database, I made a copy of the existing datafile and created a new tablespace, referring the tablespace to this copied datafile. I also created a new user for this tablespace. But still I was unable to see the contents of the datafile. Am I missing any steps here or am I doing anything wrong?

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.