Re: HELP - Drop tablespace fails database file verification check.

In <4ruo8l$9qn_at_client2.news.psi.net>, flippo_at_flink (Flippo) writes:
>Any suggestions on a safe solution to this problem
>will be appreciated:
>
>Tablespace xxxxx was created some time ago. In the process
>of testing a revised DDL script, the xxxxx.dbf file was deleted
>external to Oracle. Oracle would not drop the tablespace
>because the dbf file could not be found. The DDL script was
>invoked again, and a new dbf file got created, but its File_Id
>was different. Now the tablespace cannot be dropped because
>of the error message below. No data has to be preserved, just suggest
>what can be done to clean up the system tables and drop and
>rebuild the tablespace.
>
>Oracle7 Server Release 7.1.6.2.0 - Production Release
>
>SQL> drop tablespace xxxxx;
>drop tablespace xxxxx
>*
>ERROR at line 1:
>ORA-01122: database file 8 failed verification check
>ORA-01110: data file 8: '/NetPath/nfs/xxxxx.dbf'
>ORA-01204: file number is 16 rather than 8 - wrong file
>
>Thanx in advance for your help,
>Don Flippo
>
>Reply or email to flippo_at_laa.com

Don,
I couldn't recreate your exact error message, but I guess the answer will be the same as if you had accidently deleted one of your datafiles. To clean up the system tables etc and get back into a normal state, do the following : NOTE :- this will drop the tablespace therefore you will *LOSE* all data contained therein.. Hope you have a backup !!

sqldba/svrmgrl connect internal
shutdown the database (may have to shutdown abort)

startup mount

alter database datafile '/dir/subdir/dodgyfile.dbf' offline drop;

alter database create datafile '/dir/subdir/dodgyfile.dbf';

• i think oracle recreates the file from control file info etc, but it is not a valid file - it has no data in it
• this will not work if the physical datafile still exists.

shutdown

startup

drop tablespace dodgy;

Now you can recreate the tablespace again as you want, and reimport the data (you may want to take a backup before doing any of the above)

Best of Luck,
Bye,
Steve

| any similarity 'tween my opinions and that |
|  of my employers are purely hypothetical   |
|     and should give no cause for alarm     |
 --------------------------------------------