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 datafiles;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: DROP VIEW QS.aq$AQ$_MEM_MC_R ORA-01259: unable to delete datafile D:ORACLEORADATA\EXAMPLE01.DBF 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.