How do I get Oracle, see what procedures are running?

oracle procedures
Daniel Sousa picture Daniel Sousa · Aug 2, 2013 · Viewed 41.6k times · Source

Good afternoon. How do I get Oracle, see what procedures are running?

Answer

Patrick Marchand picture Patrick Marchand · Aug 2, 2013

Depending on your needs, this might suffice (but relies on access to v$session and dba_objects):

select 'CALLED PLSQL', vs.username, d_o.object_name -- whatever info you need
  from dba_objects d_o
       inner join
       v$session vs
          on d_o.object_id = vs.plsql_entry_object_id
union all
select 'CURRENT PLSQL', vs.username, d_o.object_name
  from dba_objects d_o
       inner join
       v$session vs
          on d_o.object_id = vs.plsql_object_id

As per the docs:

PLSQL_ENTRY_OBJECT_ID - ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack

PLSQL_OBJECT_ID - Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL

Related questions

Whats the exact meaning of having a condition like where 0=0?

I'm having a bit confusion in using a statement like "where 0=0" in Oracle procedures? Can someone please clarify it? Even though we do it for dynamic selection, why do we add this statement even though we append the actual condition …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More