Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Drop deleted tablespace from dictionary

Posted on 2008-06-20
10
Medium Priority
?
726 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 200 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 800 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 200 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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