Windows – How to delete a file that has been locked by UTL_FILE

oracleplsqlwindows

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type).

Now I can't delete the file. Short of restarting the instance or rebooting the box, is there anything I can run within Oracle to clear the handle? I've system user access to the box in question.

Update: Oracle is running on Windows.

Best Answer

Is the session dead?

SELECT sid, serial# FROM v$session WHERE /* conditions */;

-- ALTER SYSTEM KILL SESSION '<sid>, <serial>';
ALTER SYSTEM KILL SESSION '123, 456';

If killing the session doesn't work, you can explicitly close the file handle with a SysInternals utility called Handle.

https://technet.microsoft.com/en-us/sysinternals/bb896655

I also found a SuperUser thread on this which mentions LockHunter and Process Explorer as other tools that can do the job:

https://superuser.com/questions/425727/how-to-unlock-file-without-kill-related-process

Related Question