plsql - Alternative to V$LOCKED_OBJECT for finding locked tables

Question

V$VERSION: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

select count(*)
INTO n_count
from V$LOCKED_OBJECT a
inner join ALL_OBJECTS o
on a.OBJECT_ID = o.OBJECT_ID
where o.OBJECT_TYPE = v_object_type
and o.OBJECT_NAME = p_table_name;

The fragment above is part of a function called during batch processing looking for table locks.

SESSION_ROLES shows the application ID has SELECT_CATALOG_ROLE. SELECT_ANY_DICTIONARY privilege was temporarily granted to the application ID to create the function.

So, we can SELECT on V$LOCKED_OBJECT, but cannot compile an object that references it without involving a different group to temporarily grant the SELECT_ANY_DICTIONARY privilege.

The issue is that deploys can make the function invalid, but the DBA cannot recompile it without the additional privilege.

Using "execute immediate" we can compile the function, but get "ORA-00942: table or view does not exist" at runtime, indicating it also expects the additional privilege. Note that when running the same "execute immediate" in a TOAD session it runs as expected. It is only running the function with it that causes the error.

Are there any other options within the application itself to determine if a table is locked?

question from:https://stackoverflow.com/questions/65928054/alternative-to-vlocked-object-for-finding-locked-tables

Answer 1

Waitting for answers