Home » RDBMS Server » Server Administration » 'Drop Procedure' Difficulty - Error code :- ORA-04021: timeout occurred while waiting to lock object
'Drop Procedure' Difficulty - Error code :- ORA-04021: timeout occurred while waiting to lock object [message #371143] Wed, 06 September 2000 08:43 Go to next message
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 #371150 is a reply to message #371143] Thu, 07 September 2000 08:11 Go to previous messageGo to next message
Thierry Van der Auwera
Messages: 44
Registered: January 2000
Member
Hallo Taiwo,

I think your object (Procedure ABCDEF) is locked by someone or something.
Can you shutdown and restart the database and then direct delete the object?
You can find out who/what is locking the object, this can be find in the oracle sys views.
Look at these views, ex: sys.V_$LOCKED_OBJECT

Hope this can help you a little bit.

Greetings,

Thierry.
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 Go to previous message
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.
Previous Topic: searching for an underscore in a LIKE statement
Next Topic: Data Dictonary
Goto Forum:
  


Current Time: Fri Mar 29 07:55:49 CDT 2024