0

I inserted data into a table called cell_local_stage1. With this procedure:

CREATE TABLE cell_local_stage1
    (
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      p_batch VARCHAR2 (50),
      c_batch VARCHAR2 (50),
      
      emptycol VARCHAR2(50),
      ind_var_number NUMBER,
      study_id VARCHAR2 (50),
      char_id NUMBER,
      concentration NUMBER (6, 2),
      valid NUMBER (1) DEFAULT 1,
      endpoint1 NUMBER (1),
      method1 VARCHAR2 (50),
      methodv1 VARCHAR2 (50))
      
      
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE 
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_cell_local_stage1_from_cell_local.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CELL_LOCAL.CSV')
    ) REJECT LIMIT UNLIMITED;

I got a log file that has this information:


 LOG file opened at 08/17/20 22:00:47

Total Number of Files=1

Data File: CELL_LOCAL.CSV

Log File: CELL_LOCAL_STAGE1_4908_22980.log

And when I try to delete that file this messages says that the file is still open:

enter image description here

I was able to delete the CSV files, but not the log files. I didn't know I could even open these log files in Oracle.

Sorry for such a basic question.

5
  • So were you 'inserting into a database', using sqldr to read a csv file? What does CELL_LOCAL_STAGE1_4908_22980 represent? A log file from a sqlldr operation? You need to be much more specific about what you were doing and what that file is.
    – EdStevens
    Commented Aug 18, 2020 at 17:51
  • @EdStevens is this better? Commented Aug 18, 2020 at 18:04
  • Is it better? We now know that some process said it was creating 'CELL_LOCAL_STAGE1_4908_22980.log" as its log file. We don't know what that process was. And it seems interesting that the file you cannot delete is 'CELL_LOCAL_STAGE1_4908_22980' -- without the '.log' extension.
    – EdStevens
    Commented Aug 18, 2020 at 18:29
  • @EdStevens yes, more relevant information is objectively better. I have no idea if the part about the file not having a .log extension is really obviously odd. I just tried right clicking on it and deleting it that way (rather than deleting the entire folder its in), and i was able to delete it. Now the folder is empty, but I can't delete the folder because it says it's open in another program. I had an directory reference that folder yesterday, but I've since recreated that object to reference another directory. Commented Aug 18, 2020 at 18:40
  • From the error message it is apparent that the folder and/or file was opened/created by OracleServiceORCL. It is reasonable to believe, therefore, that it is related to some internal Oracle operation for the external table. Don't be surprised if the log file reappears at some point, or if you are unable to delete the directory until you shutdown Oracle, or if the directory (or another one almost exactly the same) reappears when Oracle restarts and the external table is used again.
    – pmdba
    Commented Aug 18, 2020 at 18:44

2 Answers 2

1

Well, its Oracle on Windows, such issues are not uncommon there.

Similar, but not exactly your issue:

Bug 30191227 - HANDLE LEAK ON EXTERNAL TABLE LOG FILE IN WINDOWS PLATFORM

No fix exists yet for it, not even in 19c. Suggested workaround:

WORKAROUND:
-----------
Manually release the file handle using Windows process explorer.

Of course database restart works as well (Oracle on Windows runs as a single process).

Process Explorer

0

Go to services (start - type service) - look for OracleServiceOrcl - right click and Stop; File can then be deleted

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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