ORA-20100 and FIND_FILE

BEGIN
*
ERROR at line 1:
ORA-20006: ORA-20100: File l0005230.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.OUR_CUSTOM_CODE", line 949
ORA-06512: at line 2

Thats how I started my day. To say I hate ORA-20100 errors would not accurately portray my feelings. However, after reading Tom Kytes blog posting titled "Why do people do this?" I know why. Guess what is on line 949. ;)

Unlike previous occurrences of this error, its a concurrent manager temp file. So I know right away that the directory in question is pointed to by the environment variable APPLPTMP.

We have hit this error before and usually its the utl_file_dir database parameter. I guess some of us DBA's have fat fingers and even tho we double check this parameter, sometimes a typo sneaks through during cloning. I have a script which writes to each of our utl file directories. So I could rule this out as being a problem.

I can't remember where I obtained the code below, but here is what I use. I write to a file, then try and read from it. Exceptions below give me a good indication of what the problem may be.

CREATE OR REPLACE  PROCEDURE utl_test IS
             file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
             col1  NUMBER;                   -- C1 retrieved from testtab table
             retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file
  BEGIN
  file_handle := UTL_FILE.FOPEN('dir1', 'filetest.txt', 'W');
  UTL_FILE.PUT_LINE(file_handle, 'This is a test');
  UTL_FILE.FCLOSE(file_handle);
  file_handle :=utl_file.fopen('dir1', 'filetest.txt', 'R');
  UTL_FILE.GET_LINE(file_handle, retrieved_buffer);
  dbms_output.put_line(retrieved_buffer);
  utl_file.fclose(file_handle);
          EXCEPTION
             WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('no_data_found');
                UTL_FILE.FCLOSE(file_handle);
             WHEN UTL_FILE.INVALID_PATH THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
                UTL_FILE.FCLOSE(file_handle);
             WHEN UTL_FILE.READ_ERROR THEN
                DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
                UTL_FILE.FCLOSE(file_handle);Troubleshooting ORA-20100 on Concurrent Processing
             WHEN UTL_FILE.WRITE_ERROR THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
                UTL_FILE.FCLOSE(file_handle);
 END;

The next common resolution was to check the location to which the APPLPTMP environment variable points. We have multiple environments on our test dev servers and its not uncommon for one environment to own a tmp file, thus blocking another environment from writing to it. In this case, the file l0005230.tmp existedand was owned by the proper user but was zero size.

-rw-r--r-- 1 oravis dba 0 Feb 5 10:06 l0005230.tmp

We have hit OS fragmentation issues in the past. Just to be safe I copied a large file to the tmp directory without a problem. We have had partitions with 20GB free and have been unable to create a 100k file. A system reboot usually fixed that.

I was out of ideas. I have never gotten this far with this particular error before. So I fired up Metalink. Note:261693.1 Troubleshooting ORA-20100 on Concurrent Processing, listed the options I have tried above and a few more. One suggestion is to use FND_FILE which is the package used by the application to write to OS files.

SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

If everything is setup correctly, this will write to a file in the directory pointed to by APPLPTMP. Don't go to this directory looking for a file named FND_FILE.LOG. You won't find it. The data, "THIS IS A TEST", will be written to a temp file. So look at tmp files created around the time you executed that command.

$cat l0006340.tmp
THIS IS A TEST

So at this point, none of the suggested troubleshooting steps have pinpointed the problem. To summarize, we have verified:

• that the UTL_FILE_DIR init parameter is correct and each location is writeable.
  
• that the application should be able to write to the OS using FND_FILE. The file does get created but the app can't write data to it.
  

The only thing left was to try restarting services. A bounce of the concurrent managers didn't resolve the issue. So we bounced the entire application and database. This finally resolved the issue. Why? No idea. There were a bunch of processes that didn't shutdown properly so we killed them. Maybe one of those had a resource open that was blocking. If you have encountered this before and found the root cause, feel free to share the information. ;)