6

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

JJJ
  • 32,902
  • 20
  • 89
  • 102
Daniel Sousa
  • 61
  • 1
  • 1
  • 4
  • Please check this http://stackoverflow.com/questions/250792/how-do-i-log-trace-oracle-stored-procedure-calls-with-parameter-values – T.S. Aug 02 '13 at 19:43

2 Answers2

25

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

Patrick Marchand
  • 3,405
  • 21
  • 13
  • Thank you. This is exactly what I needed. Already now take this opportunity to ask if you know tell me if it is possible to see the queries that a particular procedure is running!? – Daniel Sousa Aug 02 '13 at 20:17
  • 3
    You can add v$sql to get the current SQL being run at the time you check: LEFT OUTER JOIN v$sql vsq ON vsq.sql_id = vs.sql_id and add vsq.sql_fulltext to the SELECT list. Oh, and you may want to include WHERE vs.status = 'ACTIVE'. – Patrick Marchand Aug 02 '13 at 20:32
  • I've done what you said and it shows what I want however the place of the generated values ​​appear B3:, :B2. You can not show the actual values​​? – Daniel Sousa Aug 02 '13 at 20:54
  • I'm afraid I don't understand what you mean by "generated values" vs "actual values" – Patrick Marchand Aug 02 '13 at 22:25
  • In the field of SGL_FULLTEXT tabeka v $ sql, appears the query is being executed at the moment. However, in that query instead of appearing eg id_number:1234 appears id_number =: B2. My question is whether it is possible to see what value :B2 corresponds. – Daniel Sousa Aug 03 '13 at 08:52
  • Sure, have a look into v$sql_bind_capture: http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_3043.htm – Patrick Marchand Aug 06 '13 at 14:40
0
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
T.S.
  • 18,195
  • 11
  • 58
  • 78
MOHSIN BHAT
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 31 '22 at 23:47