Forum Stats

  • 3,837,534 Users
  • 2,262,268 Discussions
  • 7,900,315 Comments

Discussions

Regarding PLSQL_OBJECT_ID

DBA_1
DBA_1 Member Posts: 387
edited Mar 10, 2012 4:10PM in SQL & PL/SQL
What does "PLSQL_OBJECT_ID" , PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID signifies in v$session

Answers

  • 860213
    860213 Member Posts: 705
    edited May 31, 2011 10:37AM
    HI,

    All column on v$session explained in below doc:-

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3016.htm

    Thanks,
    JD
  • user4649694
    user4649694 Member Posts: 31 Green Ribbon
    Hi,

    DOCs could reveal some more interesting details. Here is what I understand:

    plssql_entry_object_id is the top level PL/SQL-Object currently invoked by this session
    plsql_object_id is the currently executing PL/SQL-Object from this session

    And here you will see how to get this objects:
    SELECT sid
    ,      serial#
    ,      username
    ,      ( SELECT max( substr( sql_text , 1, 40 )) FROM v$sql sq WHERE sq.sql_id = se.sql_id ) AS sql_text
    ,      ( SELECT object_name    FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
    ,      ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
    ,      ( SELECT object_name    FROM dba_procedures WHERE object_id = plsql_object_id       AND subprogram_id = 0) AS plsql_entry_object
    ,      ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id       AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
    ,      se.*
    FROM   v$session se
    WHERE  1=1
    -- AND    se.status = 'ACTIVE'
    -- AND    sid = 397
    -- AND    plsql_entry_object_id IS NOT NULL
    ORDER BY se.sid
    Regards
    Martin
    Dmytro Martyniuk
  • jkstill
    jkstill Member Posts: 18 Blue Ribbon
    That is a very useful script, thanks Martin.
This discussion has been closed.