Just sharing this as a lesson learned...

On Windows, when using DROP TABLESPACE INCLUDING CONTENT AND DATAFILES, the actual files on the harddrive don't get removed. Sometimes (not always) after a reboot, they get deleted. And sometimes (most of the time not) you can delete them manually.

Most of the time tough, they just stay there, and Oracle keeps a lock on them, preventing you from deleting them. Why is this? What can you do about it?

So, what I've been doing to delete these files is the following...

Once I've made sure that the datafile is no longer known by Oracle, and there is no I/O going on on the datafile, I find and close all open Handles.

To do this, I use Process Explorer (run as administrator) I go to 'find', 'Handle or DLL', and then search for the filename I want to delete. Most of the time there are two handles open to the specific file. Sometimes 4, and 2 close after about 20 seconds. If more handles are open, I would double check there is no I/O, and Oracle has no knowlege of this file. Now, just right click the handles and close them. You can now delete the file.

I've been using this method for years, and never had a problem... Yet, I have no clue why those handles stay open, and what any possible side effects might be.

  • Hey @Phil, I'm not sure I understand the SE "answer your own question" feature correctly. Why do you think my solution should not be posted as an answer? I was trying to do this: blog.stackoverflow.com/2011/07/… Agreed, there might be better answers, but still, it's a usable answer to my question... – Wouter Jul 7 '16 at 9:29
  • 1
    " I have no clue why those handles stay open, and what any possible side effects might be" -- Someone else will have a clue, hence me editing your answer into your question :-) . Apologies if that wasn't your intention. – Philᵀᴹ Jul 7 '16 at 9:33
  • 1
    Ah yes, my question is indeed twofold "what's the cause" and "what can you do about it". My answer only adresses the latter. It would indeed be nice if we can draw attention towards the first question. I suppose people looking for this will also find my solution this way... so, thx for the edit :) – Wouter Jul 7 '16 at 9:36
  • Had the same issue with one database on Windows. Scheduler job drops tablespace and datafile, but files stay on OS as Windows has lock on them. I've tested the same procedure on Linux OS with the same version of database and the same data, and datafiles get deleted without problem. Guess it's OS issue not Oracle issue. – mutap Jul 8 '16 at 19:51
  • 1
    did you take the tablespace offline first? if not, oracle will hold the files until sessions that are or have accessed objects in that tablespace close their current open transactions. perhaps that is the cause in your case? – Raj Jul 12 '16 at 3:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.