*** SPOILER ALERT : This Blog post ends with a question (it’s a story of failure) ***
I’ve been looking into some Active Session History data for a problem we encountered in some PL/SQL code in our production environment. ASH is wonderful at telling us what SQL is running but not great at showing us which PL/SQL code is running. There is a little hope by way of the data in PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_ENTRY_OBJECT_ID, PLSQL_OBJECT_ID and PLSQL_SUBPROGRAM_ID.
The first two, the “ENTRY” values show the initial PL/SQL call, the top of the call stack. They’re easy to interpret:
select * from dba_objects where object_id = <PLSQL_ENTRY_OBJECT_ID>
shows the package (specification, not body) and:
select * from dba_procedures where object_id = <PLSQL_ENTRY_OBJECT_ID> and subprogram_id = <PLSQL_ENTRY_SUBPROGRAM_ID>
shows the procedure. Obviously the second query shows you the package and the procedure, so, for the ENTRY columns, the former query isn’t really necessary.
My problem came when I wanted to find out which procedures were being referenced by PLSQL_SUBPROGRAM_ID as these could be private procedures and THEY DON”T EXIST IN DBA_PROCEDURES. So the question is, how do I find out which private procedure is being executed?