No datafile - can't drop/recreate tablespace

[Mary Claire Pollard]

I am installing Oracle 8.1.7 on a HP-UX 11.0 server to use as a test box. When creating one of my table spaces, I found an error in my sizing. No big deal. The server isn't production. I dropped the tablespace and the deleted the datafile. Well, after doing this several times, I got ahead of myself. I deleted the datafile of a tablespace, and now I am unable to drop or recreate that tablespace.

*****
Here's the error:
*****
SQL> drop tablespace advantage_large_2;
drop tablespace advantage_large_2
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oradata/RCCCDBT/advantage4.dbf'
ORA-27041: unable to open file
HP-UX Error: 2: No such file or directory
Additional information: 3

*****
Okay, no problem. I'll just create a blank file with the same name in the same directory. Not so. Here is that error:
*****
SQL> drop tablespace advantage_large_2
2 ;
drop tablespace advantage_large_2
*
ERROR at line 1:
ORA-01115: IO error reading block from file 7 (block # 1)
ORA-01110: data file 7: '/oradata/RCCCDBT/advantage4.dbf'
ORA-27072: skgfdisp: I/O error
Additional information: 1

*****
Then, surely, I can delete the pointer in the Oracle table. I went to DBA_DATA_FILES. I was going to delete the entry from that table. Of course, it couldn't be that easy. Here's the error:

SQL> delete from
2 dba_data_files
3 where file_id = 7
4 ;
dba_data_files
*
ERROR at line 2:
ORA-01732: data manipulation operation not legal on this view
*****

How can I get rid of the tablespace when there is no datafile - oh, and did I mention no backup.

HELP!
Thanks,
Mary

[Brian Crabtree · Accepted solution]

alter database datafile '/oradata/RCCCDBT/advantage4.dbf'
offline drop;

Then issue your drop tablespace command.

Brian

[Mary Claire Pollard]

Worked lovely! You saved me from doing a reinstall! Thank you!

-Mary

[Yogeeraj_1]

Hi mary,

Hmm, fortunately your database did not crash - like what happened to me recently. In which case, you would have to do the following:

1. sqlplus internal
2. startup mount
3. alter database datafile '/oradata/RCCCDBT/advantage4.dbf' offline drop;
4. alter database open;
5. drop tablespace advantage_large_2;

Happy that your problem has been solved.

Cheers
Yogeeraj