6

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.

  • 2
    Which OS are you on? I'm guessing Windows, as filesystems on Unix-like OSes generally let you delete a file even if opened by another process. – Colin 't Hart Jan 23 '14 at 13:05
  • if you know which session has oened the file you could kill this session. At least on Linux this would work. – miracle173 Mar 26 '14 at 3:36
  • @Colin 't Hart : in Unix you can delete a directory entry while it is open but cannot delete the file until the last file handle is closed. So you have a similar problem. – miracle173 Mar 26 '14 at 3:41
  • Similar thing happened to me just the other day. You should just be able to close the process that opened the connection (and hence the file) and all should be good. Mine was through SQLDeveloper; I disconnected the session and was then able to delete the file. – BriteSponge Mar 9 '16 at 13:19
1

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

| improve this answer | |
0

If you just want to forcefully close the file, you could use a tool which lets you close open files.

Googling I found OpenFilesView. See http://www.nirsoft.net/utils/opened_files_view.html

| improve this answer | |
0

Use UTL_FILE.fclose_all(); in your script and comment everything else out, and then re-execute your script.

The file must be closed from the same session where you opened it. The above command closes all file opened from the session the script was executed from.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.