Home » SQL & PL/SQL » SQL & PL/SQL » Strange Error 04021 timeout occurred while waiting to lock object <DIRECTORY> (Oracle 10g)
| Strange Error 04021 timeout occurred while waiting to lock object <DIRECTORY> [message #625128] |
Wed, 01 October 2014 10:41  |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Hi people,
and thanks in advance!
I have a small procedure wich opens disk files by using BFILENAME and DBMS_LOB.
I manage DIRECTORY object dynamicly: before loading each file, I replace the same DIRECTORY with the path's value to be used.
I do load -sequentally- a lot of existing files, in different paths (it means, replacing value of this DIRECTORY object a lot of times), with no problem.
The problem comes when I try to load a non existing file...
That's important: it happens the same...
- ...trying to load from a non existing path
- ...trying to load a non existing file from an existing path
Step by step description:
0.- Previously, I did a lot of 'CREATE OR REPLACE DIRECTORY' to the same DIRECTORY, and I loaded a lot of existing files into BLOBs with BFILENAME() and DBMS_LOB.
1.- I do a CREATE OR REPLACE DIRECTORY AS '...'
2.- BFILENAME (Directory, FileName)
--> Directory (phisical path) or FileName does not exist, but Oracle does not know yet
3.- DBMS_LOB.OPEN()
--> Returns an error (ORA-22288: file or LOB operation FILEOPEN failed. No such file or directory)
4.- I move forward, I don't care about this error. I try to load another disk file. And the next one exists. For sure.
5.- I do a CREATE OR REPLACE DIRECTORY AS '...'
6.- After 5 minutes, I get the error:
ORA-04021 timeout occurred while waiting to lock object <DIRECTORY_NAME>
There is no sign in V$ACCESS about this <DIRECTORY_NAME>.
And I don't really know...
The only thing I can think of, is that BFILENAME(), when phisical path or file does not exist, keeps anyway looking for it... eternally...
Well, for sure you'll see what is wrong here.
I past the whole PL block. You should only change the values of the firsts declarations, with paths and filenames, to make it function in your machines. Well, at least I think so.
DECLARE
----------------------------------------------------------------------------
-- Configure this values to test this stuff
----------------------------------------------------------------------------
-- Name of the DIRECTORY we'll use
pDirectory VARCHAR2(100) := 'TEMP_DIRECTORY_FOR_TEST';
-- A default Path (it won't be used, so it's not necessary to be ok)
pDefaultPath VARCHAR2(100) := '/path/that/exists/';
-- Path and FileName to a file that DOES exists
pPathInExists1 VARCHAR2(100) := '/path/that/exists/one/';
pFileInExists1 VARCHAR2(100) := 'existing_file_one.pdf';
-- Path and FileName to another file that DOES exists
pPathInExists2 VARCHAR2(100) := '/path/that/exists/two/';
pFileInExists2 VARCHAR2(100) := 'existing_file_two.pdf';
-- Path and FileName to another file that DOES NOT exists
pPathInNotExists VARCHAR2(100) := '/path/that/not/exists/';
pFileInNotExists VARCHAR2(100) := 'non_existing_file.pdf';
----------------------------------------------------------------------------
-- Private function that:
-- 1.- Replces global 'pDirectory' with value of parameter 'pPath'
-- 2.- Load into a BLOB the file 'pFileName' wich should exists in 'pPath'
-- 3.- Clears everything
----------------------------------------------------------------------------
PROCEDURE P_Sub_Load_File (pPath IN VARCHAR2, pFileName IN VARCHAR2)
IS
v_bfile BFILE;
v_amount INTEGER;
v_dest_offset INTEGER;
v_src_offset INTEGER;
v_blob BLOB;
BEGIN
--
-- We'll show a lot of outputs. Yeah.
--
dbms_output.put_line('____[Load] Start loading of ['||pPath||']['||pFileName||']...');
--
-- Replace DIRECTORY object dynamicly with this 'pPath'
--
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || pDirectory || ' AS '''|| pPath ||'''';
dbms_output.put_line('____[Load] Dir replaced.');
BEGIN
--
-- Assign BFILE locator
-- It will raise no exception, even if pDiretory or pFileName does not exist
--
v_bfile := BFILENAME(pDirectory, pFileName);
dbms_output.put_line('____[Load] BFILE located.');
--
-- Try to open the BLOB.
-- We'll get here the exception if path or file does not exist
--
dbms_lob.open(v_bfile, dbms_lob.file_readonly);
dbms_output.put_line('____[Load] BLOB opened.');
BEGIN
--
-- Initializes and create temporary BLOB
--
v_amount := dbms_lob.getlength(file_loc => v_bfile);
v_dest_offset := 1;
v_src_offset := 1;
IF NVL(dbms_lob.istemporary(v_blob),0) = 1 THEN
dbms_lob.freetemporary(v_blob);
END IF;
dbms_lob.createtemporary(v_blob,TRUE);
--
-- Loads data into BLOB
--
dbms_lob.loadblobfromfile( dest_lob => v_blob,
src_bfile => v_bfile,
amount => v_amount,
dest_offset => v_dest_offset,
src_offset => v_src_offset);
dbms_output.put_line('____[Load] BLOB loaded OK.');
--
-- We could do something here with this v_blob,
-- but it's not important right now
--
NULL;
--
-- We just delete it
--
dbms_lob.freetemporary(v_blob);
--
-- And close BFILE
--
dbms_lob.close(v_bfile);
dbms_output.put_line('____[Load] BFILE closed.');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('____[Load] Unexpected error loading BLOB:' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('____[Load] Error opening file (BLOB):' || REPLACE(SQLERRM, chr(10), '. '));
dbms_lob.close(v_bfile);
dbms_output.put_line('____[Load] BFILE closed.');
END;
dbms_output.put_line('____[Load] Done!');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('____[Load] Error replacing DIRECTORY:' || SQLERRM);
END;
BEGIN
----------------------------------------------------------------------------
-- Step one: Create DIRECTORY with default value
----------------------------------------------------------------------------
dbms_output.put_line('[St.1] Creating DIRECORY...');
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || pDirectory || ' AS '''|| pDefaultPath || '''';
----------------------------------------------------------------------------
-- Step two: Load an existing File
----------------------------------------------------------------------------
dbms_output.put_line('[St.2] Load an existing File...');
P_Sub_Load_File (pPath => pPathInExists1, pFileName => pFileInExists1);
----------------------------------------------------------------------------
-- Step three: Load another existing File
----------------------------------------------------------------------------
dbms_output.put_line('[St.3] Load another existing File...');
P_Sub_Load_File (pPath => pPathInExists2, pFileName => pFileInExists2);
----------------------------------------------------------------------------
-- Step four: Load a non existing File
----------------------------------------------------------------------------
dbms_output.put_line('[St.4] Load a non existing File...');
P_Sub_Load_File (pPath => pPathInNotExists, pFileName => pFileInNotExists);
----------------------------------------------------------------------------
-- Step five: Load an existing File
----------------------------------------------------------------------------
dbms_output.put_line('[St.5] Load an existing File...');
P_Sub_Load_File (pPath => pPathInExists1, pFileName => pFileInExists1);
----------------------------------------------------------------------------
-- Step six: Maybe it's enough
----------------------------------------------------------------------------
dbms_output.put_line('[St.6] End. Enough.');
END;
And that's the output I get:
[St.1] Creating DIRECORY...
[St.2] Load an existing File...
____[Load] Start loading of [/path/that/exists/one/][existing_file_one.pdf]...
____[Load] DIRECTORY replaced.
____[Load] BFILE located.
____[Load] BLOB opened.
____[Load] BLOB loaded OK.
____[Load] BFILE closed.
____[Load] Done!
[St.3] Load another existing File...
____[Load] Start loading of [/path/that/exists/two/][existing_file_two.pdf]...
____[Load] DIRECTORY replaced.
____[Load] BFILE located.
____[Load] BLOB opened.
____[Load] BLOB loaded OK.
____[Load] BFILE closed.
____[Load] Done!
[St.4] Load a non existing File...
____[Load] Start loading of [/path/that/not/exists/][non_existing_file.pdf]...
____[Load] DIRECTORY replaced.
____[Load] BFILE located.
____[Load] Error opening file (BLOB):ORA-22288: file or LOB operation FILEOPEN failed. No such file or directory
____[Load] BFILE closed.
____[Load] Done!
[St.5] Load an existing File...
____[Load] Start loading of [/path/that/exists/one/][existing_file_one.pdf]...
And after five minutes, more output:
____[Load] Error replacing DIRECTORY: ORA-04021: timeout occurred while waiting to lock object SYS.TEMP_DIRECTORY_FOR_TEST
[St.6] End. Enough.
Well, I'm looking forward your feedback.
Thank u!!
|
|
|
|
|
|
| Re: Strange Error 04021 timeout occurred while waiting to lock object <DIRECTORY> [message #625181 is a reply to message #625131] |
Thu, 02 October 2014 05:48  |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
Thanks a lot Michel!
I have read it. I get -more or less- what locks and pins are. But... I don't know exactly how to interpret it in my case.
That's the info I get during the 5-miunutes "block" situation:
From v$access table:
SID OWNER OBJECT TYPE
14 ALBOR DBMS_LOB NON-EXISTENT
14 ALBOR DBMS_OUTPUT NON-EXISTENT
From dba_ddl_locks table:
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQUESTED
14 ALBOR ALBOR 18 Null None
From V$SESSION_WAIT table (by SID):
SID - 14
SEQ# - 182
EVENT - library cache pin
P1TEXT - handle address
P1 - 17060164464
P1RAW - 00000003F8DD7370
P2TEXT - pin address
P2 - 16720243376
P2RAW - 00000003E49AAAB0
P3TEXT - 100*mode+namespace
P3 - 309
P3RAW - 135
WAIT_TIME - 0
SECONDS_IN_WAIT - 21
STATE - WAITING
From this query on MOS Note, wich takes info from dba_kgllock and v$session:
WAITING_SESSION - 14
HOLDING_SESSION - 14
LOCK_OR_PIN - Pin
ADDRESS - 00000003F8DD7370
MODE_HELD - Share
MODE_REQUESTED - Exclusive
And finally, those are the affectted records from dba_kgllock :
KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLLKTYPE
00000003E16EBDA8 00000003F8DD7370 1 0 Lock
00000003E16EBDA8 00000003F8DD7370 1 0 Lock
00000003E16EBDA8 00000003F8DD7370 1 0 Lock
00000003E16EBDA8 00000003F8DD7370 3 0 Lock
00000003E16EBDA8 00000003F8DD7370 0 3 Pin
00000003E16EBDA8 00000003F8DD7370 2 0 Pin
Well, those last two records are the one in conflict ('library cache pin', 2-Share mode for the Lock, 3-Exclusive mode for the request).
But, how do I get more info about this two addresses (00000003E16EBDA8 and 00000003F8DD7370)?
Well, I guess the "Exclsuive request" refers to the "EXECUTE IMMEDIATE CREATE OR REPLACE DIRECTORY...".
And, still guessing, this "Share mode" use of the same address (the DIRECTORY), comes from the BFILENAME and DBMS_LOB operations.
In this case, If I did a DBMS_LOB.CLOSE(BFILE)... ¿how is still using the DIRECTORY?
Well, I'm lost at this point.
Thanks again!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Strange Error 04021 timeout occurred while waiting to lock object <DIRECTORY> [message #625340 is a reply to message #625280] |
Mon, 06 October 2014 04:47 |
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
|
Member |
|
|
I've been surfing MOS but I did not find anything that fits.
I'll solve (avoid) it checking, before DBMS_LOB.Open(), if 'DBMS_LOB.FileExists()'. That's gonna avoid DBMS_LOB.Open() raising the ORA-22288 and, subsequently, the ORA-04021.
But I really would like to know why DBMS_LOB.Open() makes a Lock and a Pin to the DIRECTORY object when it fails, and just a Lock when it goes well.
Thanks Michel!!
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 08:43:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
Well, this address referrs to the DIRECTORY in conflict....