Solved

Drop deleted tablespace from dictionary

Posted on 2008-06-20
10
640 Views
Last Modified: 2013-12-19
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"
0
Comment
Question by:xoxomos
10 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 21833908
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
 

Author Comment

by:xoxomos
ID: 21833935

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
 

Author Comment

by:xoxomos
ID: 21833949
What happened is, i deleted a directory '/u01/app/oracle/product/9.2/dbs/scratch'
which held the SCRATCH tablespace.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21834076
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
 

Author Comment

by:xoxomos
ID: 21834185
The tablespace won't go offline since Oracle cannot find the file.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:xoxomos
ID: 21834200

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
 
LVL 22

Assisted Solution

by:DrSQL
DrSQL earned 50 total points
ID: 21834322
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
 

Author Comment

by:xoxomos
ID: 21834502
Yes,i did create a new default tablespace
0
 
LVL 7

Accepted Solution

by:
Dauhee earned 200 total points
ID: 21837671
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
 
LVL 7

Assisted Solution

by:vishal68
vishal68 earned 50 total points
ID: 21844259
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now