Procedure log

[fatcodeguy]

Hi,

Does Oracle have a log of which procedures/packages are run on the system, on which objects, and when? If so, how do I access this? Thanks for the help

 

[sahernandez]

Check this table "v$open_cursor" . (but you must be have a permision to read)

select a.SID ,a.PROGRAM ,USERNAME, OSUSER, LOGON_TIME , machine ,STATUS, program , SQL_TEXT ,
-- ( to_char(round(last_call_et/1440,2)) ) last_call_HH ,
MIN_TO_HH24MI( TXTOMINUTE( to_char(round(last_call_et/1440,2)) ) ) last_call_HH24Mi ,
count(*) Conteo_Cursores
-- a.SID , username , status , osuser , machine , terminal , program , SQL_TEXT
from V$SESSION a , v$open_cursor b
where a.sid = b.sid
--and osuser = 'ASPNET'
and username = 'USERNAME'
--and substr(upper(OSUSER),1,2) in ('NTUSER')
group by a.SID ,a.PROGRAM ,USERNAME, OSUSER, LOGON_TIME , machine ,STATUS, program , last_call_et , SQL_TEXT
order by osuser desc




This query show you which statement are executing, but only show you a part of the Query String.
Read about v$open_cursor and V$SESSION