Problem solve Get help with specific problems with your technologies, process and projects.

Error after deleting tablespace

I am using Oracle9i Release 2 in my production database. I have use this command to delete tablespace EXAMPLE:

 drop tablespace EXAMPLE including contents and
Before doing this I just dropped all users that existed in the EXAMPLE tablespace with the cascade option. I forgot to run drop scripts of demo schemas. It deleted all data segments from database, but it can't do a physical deletion of example01.dbf file in the oradata folder. Error exist in trace file as:
 Encountered error when executing: 
 ORA-01259: unable to delete datafile
 ORA-27056: skgfrdel: could not delete file
 OSD-04024: Unable to delete file.
 O/S-Error: (OS 32) The process cannot access the
 file because it is being used by another process. 
I have confusion about whether I should delete manually from the oradata folder -- will it ask for media recovery for not -- in my 24 hours running database? I have also created a backup control file to trace, where I didn't find the EXAMPLE tablespace or its datafile info.

If I were to run into this problem, I'd just issue DROP TABLESPACE ts_name INCLUDING CONTENTS. Skip the 'AND DATAFILES' clause. This will remove it from Oracle. The datafile will still be on the filesystem and you can try to remove it manually. From this point on, Oracle will have no knowledge of the tablespace and datafile.

In your case, since you can't find mention of the tablespace in your control file to trace, then I'd have to assume that the tablespace has been dropped and Oracle will not look for it again. It just couldn't delete the datafile after the DROP TABLESPACE command was issued. You'll have to manually delete the datafile later.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.