external table issue

[Database Administrator]

Hi all,
I am running Oracle 9.2.0.5 on Win2003 and having a problem deleting
badfile created by load from external table. I get ORA-29291 and if I
try to delete if manually, then I am getting an OS error: "cannot
delete... being used by another program...".
What am I doing wrong???

Here is the code used:
------------------------------------------------------
CREATE TABLE UB_CASE_EXT
(
CASE_NUMBER VARCHAR2(18 BYTE),
UPC_ID VARCHAR2(12 BYTE),
STYLE_ID VARCHAR2(14 BYTE),
COLOR_ID VARCHAR2(6 BYTE),
IDENTIFIER_ID VARCHAR2(10 BYTE),
SIZE_ID VARCHAR2(5 BYTE),
DIMENSION_ID VARCHAR2(5 BYTE),
PROPERTY_MARK VARCHAR2(15 BYTE),
ETA_DATE DATE,
CASE_QUANTITY NUMBER(9),
CASE_VOLUME NUMBER(9),
CASE_WEIGHT NUMBER(7,2),
SC_FILE_ID NUMBER(10)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORALOAD
ACCESS PARAMETERS
( records delimited BY newline
NOLOGFILE
badfile ORALOAD:UB_CASE
FIELDS TERMINATED BY '|' LRTRIM
MISSING FIELD VALUES ARE NULL
(case_number,
upc_id,
style_id,
color_id,
identifier_id,
size_id,
dimension_id,
property_mark,
eta_date,
case_quantity,
case_volume,
case_weight,
sc_file_id)
)
LOCATION (ORALOAD:'case_file.txt')
)
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE 4 INSTANCES 1 )
NOMONITORING;


PROCEDURE LOG_BAD_CASES_P IS
vExists BOOLEAN;
vFile_l NUMBER; -- file lenght
vFile_bs NUMBER; -- file block size

BEGIN
UTL_FILE.FGETATTR(LOCATION => 'e:\utl_file_dir\ORALOAD',
FILENAME => 'ub_case.bad',
FEXISTS => vExists,
FILE_LENGTH => vFile_l,
BLOCK_SIZE => vFile_bs);

IF (vExists) THEN
UTL_FILE.FREMOVE(LOCATION => 'e:\utl_file_dir\ORALOAD',
FILENAME => 'ub_case.bad');
END IF;
COMMIT;

END LOG_BAD_CASES_P;


Thanks,
Eugene

[Database Administrator]

I hate it when Windows decides to put that damned hook into the file and
won't let you delete it. When I run into this, I try to rename the file
to something else. Then I can go on with my life. At a later time, once
no one is using that renamed file, it can be deleted.

There are Windows utilities out there which can determine the
process(es) using that file, stopping you from deleting it. A Google
serach should bear those out. Once you identify the process, then you
may decide to kill that process, freeing up the lock on the file.

HTH,
Brian


--
================================================== =================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

[Database Administrator]

I hate it when Windows decides to put that damned hook into the file and
won't let you delete it. When I run into this, I try to rename the file
to something else. Then I can go on with my life. At a later time, once
no one is using that renamed file, it can be deleted.

There are Windows utilities out there which can determine the
process(es) using that file, stopping you from deleting it. A Google
serach should bear those out. Once you identify the process, then you
may decide to kill that process, freeing up the lock on the file.

HTH,
Brian


--
================================================== =================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

[tfpayne72]

Have you figured out why the oracle.exe process doesn't release the lock on the file? I have been unable to really find any information on this. I did find this thread... http://asktom.oracle.com/pls/asktom/...:4347075367803
It doesn't really find a reason.

thanks.