| 'Drop Procedure' Difficulty - Error code :- ORA-04021: timeout occurred while waiting to lock object [message #371143] |
Wed, 06 September 2000 08:43  |
Taiwo
Messages: 7
Registered: September 2000
Location: Nigeria
|
Junior Member |
|
|
All efforts to drop a particular Procedure for the past three weeks have failed
and met with the following error :-
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object ABCDEF
Details :-
I created a package called LMNOP which contained only one procedure called QRSTU. Procedure QRSTU takes three IN parameters which are all varchar2. Package LMNOP compiled properly.
I then created a seperate Procedure called ABCDEF shown below which dynamically called Package LMNOP in its code :-
PROCEDURE ABCDEF IS
V_GH VARCHAR2(10) := 'GH';
V_JK VARCHAR2(10) := 'JK';
SQL_STAT VARCHAR2(1000);
V_TAB VARCHAR2(30);
CURSOR GET_TAB IS
SELECT TABLE_NAME
FROM SYS.DBA_TABLES
WHERE OWNER = 'JK'
AND TABLE_NAME IN(
SELECT TABLE_NAME
FROM SYS.DBA_TABLES
WHERE OWNER = 'GH');
BEGIN
OPEN GET_TAB;
LOOP
FETCH GET_TAB INTO V_TAB;
EXIT WHEN GET_TAB%NOTFOUND;
SELECT 'LMNOP.QRSTU'||'('||''''||V_GH||''''||','||''''||V_JK||''''||','||''''||V_TAB||''''||')' INTO SQL_STAT FROM DUAL;
EXECUTE IMMEDIATE SQL_STAT;
-- DBMS_OUTPUT.PUT_LINE(SQL_STAT);
END LOOP;
CLOSE GET_TAB;
END ABCDEF;
/
At first the procedure compiled and ran properly but then I no longer needed both Package LMNOP and Procedure ABCDEF so I first dropped Package LMNOP and this drop was successful.
Now the Problem :- All efforts to drop Procedure ABCDEF for the past three weeks have failed
and met with the following error :-
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object ABCDEF
I have repeatedly waited for days on end before retrying the drop but I always get the above error.
Even when I try to CREATE OR REPLACE the Procedure, I meet with the same error above.
Any help on how to successfully drop the procedure will be greatly appreciated.
Thanks in advance
Taiwo
|
|
|
|
|
|
| Re: 'Drop Procedure' Difficulty - Error code :- ORA-04021: timeout occurred while waiting to lock ob [message #371151 is a reply to message #371143] |
Thu, 07 September 2000 08:18  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo again,
I make a little sql to find info about the lock:
select a.session_id,a.os_user_name,a.oracle_username
,a.object_id,b.owner,b.object_type,b.object_name
from sys.v_$locked_object a
,all_objects b
where a.object_id=b.object_id
order by 1,2,4,5,6
Hope it helps,
Thierry.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
