Return
-
Title
"ORA-04021: timeout occurred while waiting to lock object" error when running Oracle permissions script -
Description
When executing a permissions script an Oracle database agent the following error occurs
ERROR at line 1:
ORA-20001: ORA-04021: timeout occurred while waiting to lock object ,sql:
grant select on gv_$instance to FOGLIGHT_AGENT
ORA-06512: at "SYS.EXECUTE_IMMEDIATE", line 34
ORA-06512: at line 191 -
Cause
The ORA-04021 timeout error is most likely caused by another session locking/using a package that you are attempting to execute. Oracle can't grant execute privileges to procedures that are currently been used by a session. In this case we may need to determine if the object is been used and from whom, finding the session and killing the session. -
Resolution
RESOLUTION 1
Use the following to look for the source of the locking:
SELECT ORACLE_USERNAME, OS_USER_NAME,LOCKED_MODE,OBJECT_NAME,OBJECT_TYPEFROM V$LOCKED_OBJECT A, DBA_OBJECTS BWHERE A.OBJECT_ID=B.OBJECT_IDUse the V$ACCESS view to see which users have locks on which objects in your databaseSELECT SID, OWNER, OBJECT, TYPEFROM V$ACCESSWHERE OBJECT = 'gv_$instance';Once the sid has been determined the process locking the object can be killed.
RESOLUTION 2
Contact Oracle Support to clarify why the select privilege cannot be granted to gv_$instance.As a workaround the following code may be successful in granting the privilege:
DECLARE vcmd varchar2(4000); BEGIN vcmd :=' grant select on gv_$instance to FOGLIGHT'; sys.execute_immediate(vcmd,-02030); END; /Note: Replace FOGLIGHT in the code above with the name of your database user in the monitored instance. -
Additional Information
Look also at Oracle Metalink for Doc ID 2213685.1