Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

No datafile - can't drop/recreate tablespace

SOLVED
Go to solution
Mary Claire Pollard
Occasional Advisor

No datafile - can't drop/recreate tablespace

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
3 REPLIES
Brian Crabtree
Honored Contributor
Solution

Re: No datafile - can't drop/recreate tablespace

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

Then issue your drop tablespace command.

Brian
Mary Claire Pollard
Occasional Advisor

Re: No datafile - can't drop/recreate tablespace

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

-Mary
Yogeeraj_1
Honored Contributor

Re: No datafile - can't drop/recreate tablespace

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)