Solved

Drop deleted tablespace from dictionary

Posted on 2008-06-20
10
686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

695 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