Re: Dropping a datafile from test db (ORA-01111)

"Syltrem" <syltremzulu_at_videotron.ca> wrote:

>Hi
>
>I copied (part of) a database into another db for a test environment.
>Fact is, I only copied certain datafiles/tablespaces, created a new
>controlfile to build a new database from these copied datafiles, and
>performed a point-in-time recovery.
>
>Of course the new database's alert log file contains messages like this one:
>Tablespace 'HRDATA_IFB' #19 found in data dictionary,
>but not in the controlfile. Adding to controlfile.
>
>But that's ok.
>Now that the db is open, I would like to get rid of those files, as I don't
>need them in the controlfile (or anywhere at all).
>
>SQL> drop tablespace HRDATA_IFB including contents;
>drop tablespace HRDATA_IFB including contents
>*
>ERROR at line 1:
>ORA-00604: error occurred at recursive SQL level 2
>ORA-01135: file 10 accessed for DML/query is offline
>ORA-01111: name for data file 10 is unknown - rename to correct file
>ORA-01110: data file 10: 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.'
>
>Even after an
>alter database datafile 'IVA$EXE_ALPHA:[ORACLE8174]MISSING00010.' offline
>drop;
>I still cannot drop the tablespace.
>
>What am I doing wrong, or can it be done at all ?
>I need to create the new db with only a subset of the original db's
>tablespaces.
>
>Thanks

You did most things wrong, IMHO, or at least the hard way - think of data not tablespaces...You do not want, or need, the tablespaces, just the data that the tables. etc using that tablespace have..Unless you created them as transportable tablespaces, copying them is not a good idea ( and copying only a part of the associated data files is even a worse idea).

Start Over..

Export just the tables you want from your original database and import them into the new one..

Precreate the tablespaces using the same name as the originals, and size them how you like..

The import will attempt to place the table data into the same tablespace name as it was exported from. Received on Wed Nov 10 2004 - 13:59:03 CST