Drop deleted tablespace from dictionary

I deleted a directory which contained the default permanent tablespace.  There was nothing on it that is needed.  Now the database wil not do a normal shutdown.  I've created a new default permanent tablespace but still get the error when I try to get rid of the old tablespace name.
Is there a way other than:
      shutdown abort
      startup mount
     alter database datafile '/u01/app/oracle/product/9.2/dbs/scratch.dbf' offline drop;  ?

Wen i try with database still running:
SQL> alter database datafile '/u01/app/oracle/product/9.2/dbs/scratch.dbf' offline drop;
alter database datafile '/u01/app/oracle/product/9.2/dbs/scratch.dbf' offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"/u01/app/oracle/product/9.2/dbs/scratch.dbf"
xoxomosAsked:
Who is Participating?
 
DauheeConnect With a Mentor Commented:
ok if the original file cannot be retrieved then the database is DEAD. Recreating another one will not work as it will have diffenet object_id and the original one will still be expected. To prevent further complications it would be best to shut down the db ASAP as oracle will not be able to roll SCM to keep all files consistent and could screw things up further if other changes are being applied.

any command you try issue via oracle that attempts to do anything to the original datafile will fail as oracle needs to "touch" it to complete the operation.

If you have a backup (please say you do), then just pop the file back in and recover the database with archive logs (please say you have that turned on also).

If not you could try shut the whole server down and attempt a file recovery from the disk. It won't matter too much if sections get corrupted as all you need is the header information to be intact so that you can drop it properly via oracle.

FYI - if there is data in other datafiles in you database then . . .
If you don't have any backups then ignore the comment about shutting down and keep up so you can attempt to export as much data as possible - would then need to rebuild new db and import. If the export is failing - objects may be striped in different tablespaces then you will still have to shut the db down and attempt data extraction with a tool such as DUDE where it can go in outside of oracle and extract data - this will cost you however as they won't do it for free!!
0
 
DrSQLCommented:
xoxomos,
   If you're trying to drop the tablespace, why are you dropping the file?  Wouldn't it be better to try:

drop tablespace scratch /* or, whatever its name is */ including contents;

Good luck!
0
 
xoxomosAuthor Commented:

SQL> drop tablespace SCRATCH including contents;
drop tablespace SCRATCH including contents
*
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/product/9.2/dbs/scratch'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
xoxomosAuthor Commented:
What happened is, i deleted a directory '/u01/app/oracle/product/9.2/dbs/scratch'
which held the SCRATCH tablespace.
0
 
DrSQLCommented:
Did you try making the tablespace offline?

alter tablespace scratch offline;

then try one of these:
drop tablespace SCRATCH including contents;
drop tablespace SCRATCH including contents keep datafiles;
drop tablespace SCRATCH including contents and datafiles;

Good luck!
0
 
xoxomosAuthor Commented:
The tablespace won't go offline since Oracle cannot find the file.
0
 
xoxomosAuthor Commented:

SQL> alter tablespace SCRATCH offline;
alter tablespace SCRATCH offline
*
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/product/9.2/dbs/scratch'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

0
 
DrSQLConnect With a Mentor Commented:
Did you create a new, default tablespace?  That has to be there before you can drop the old one.  The original command you used SHOULD have worked (if you have the file name exactly correct):

alter database datafile '/u01/app/oracle/product/9.2/dbs/scratch' offline drop;

then you drop the tablespace (as I gave you).  Metalink has the same solution.  If that doesn't work, you may need to contact support.

Good luck!

0
 
xoxomosAuthor Commented:
Yes,i did create a new default tablespace
0
 
vishal68Connect With a Mentor Commented:
Hi
There is a discrepency in the file name that you are using. In the alter database command
alter database datafile '/u01/app/oracle/product/9.2/dbs/scratch.dbf' offline drop
The file name is scratch.dbf
whereas
in the Oracle error message for drop table the file name is coming as
ORA-01110: data file 3: '/u01/app/oracle/product/9.2/dbs/scratch'
i.e. only scratch

NOTICE THERE IS NO .dbf EXTENSION.

Try the alter database command with file name as scratch and not as scratch.dbf
try the following command
alter database datafile '/u01/app/oracle/product/9.2/dbs/scratch' offline drop

To recheck the file name you can query v$datafile view.

HTH
Vishal
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.