This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 7, 2012 11:38 AM by Max Seleznev RSS

Monitoring Oracle session with SQL_ID

imran khan Journeyer
Currently Being Moderated
Hi All,

How can I know a SQL_ID belonging to a user/schema in Oracle. Can anyone post me the query to find the SQL QUERY/SQL_ID belonging to a user session. I have googled but dint get what i expected. Hope I get it here. We dont have OEM configured to monitor the session.

Oracle DB version : 10.2.0.5
OS version : IBM - AIX


Regards,
Imran Khan
  • 1. Re: Monitoring Oracle session with SQL_ID
    sb92075 Guru
    Currently Being Moderated
    imran khan wrote:
    Hi All,

    How can I know a SQL_ID belonging to a user/schema in Oracle. Can anyone post me the query to find the SQL QUERY/SQL_ID belonging to a user session. I have googled but dint get what i expected. Hope I get it here. We dont have OEM configured to monitor the session.

    Oracle DB version : 10.2.0.5
    OS version : IBM - AIX


    Regards,
    Imran Khan
    look for SQL_ID below
    SQL> desc v$session
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SADDR                                              RAW(4)
     SID                                                NUMBER
     SERIAL#                                            NUMBER
     AUDSID                                             NUMBER
     PADDR                                              RAW(4)
     USER#                                              NUMBER
     USERNAME                                           VARCHAR2(30)
     COMMAND                                            NUMBER
     OWNERID                                            NUMBER
     TADDR                                              VARCHAR2(8)
     LOCKWAIT                                           VARCHAR2(8)
     STATUS                                             VARCHAR2(8)
     SERVER                                             VARCHAR2(9)
     SCHEMA#                                            NUMBER
     SCHEMANAME                                         VARCHAR2(30)
     OSUSER                                             VARCHAR2(30)
     PROCESS                                            VARCHAR2(24)
     MACHINE                                            VARCHAR2(64)
     PORT                                               NUMBER
     TERMINAL                                           VARCHAR2(30)
     PROGRAM                                            VARCHAR2(48)
     TYPE                                               VARCHAR2(10)
     SQL_ADDRESS                                        RAW(4)
     SQL_HASH_VALUE                                     NUMBER
     SQL_ID                                             VARCHAR2(13)
     SQL_CHILD_NUMBER                                   NUMBER
     SQL_EXEC_START                                     DATE
     SQL_EXEC_ID                                        NUMBER
     PREV_SQL_ADDR                                      RAW(4)
     PREV_HASH_VALUE                                    NUMBER
     PREV_SQL_ID                                        VARCHAR2(13)
     PREV_CHILD_NUMBER                                  NUMBER
     PREV_EXEC_START                                    DATE
     PREV_EXEC_ID                                       NUMBER
     PLSQL_ENTRY_OBJECT_ID                              NUMBER
     PLSQL_ENTRY_SUBPROGRAM_ID                          NUMBER
     PLSQL_OBJECT_ID                                    NUMBER
     PLSQL_SUBPROGRAM_ID                                NUMBER
     MODULE                                             VARCHAR2(64)
     MODULE_HASH                                        NUMBER
     ACTION                                             VARCHAR2(64)
     ACTION_HASH                                        NUMBER
     CLIENT_INFO                                        VARCHAR2(64)
     FIXED_TABLE_SEQUENCE                               NUMBER
     ROW_WAIT_OBJ#                                      NUMBER
     ROW_WAIT_FILE#                                     NUMBER
     ROW_WAIT_BLOCK#                                    NUMBER
     ROW_WAIT_ROW#                                      NUMBER
     TOP_LEVEL_CALL#                                    NUMBER
     LOGON_TIME                                         DATE
     LAST_CALL_ET                                       NUMBER
     PDML_ENABLED                                       VARCHAR2(3)
     FAILOVER_TYPE                                      VARCHAR2(13)
     FAILOVER_METHOD                                    VARCHAR2(10)
     FAILED_OVER                                        VARCHAR2(3)
     RESOURCE_CONSUMER_GROUP                            VARCHAR2(32)
     PDML_STATUS                                        VARCHAR2(8)
     PDDL_STATUS                                        VARCHAR2(8)
     PQ_STATUS                                          VARCHAR2(8)
     CURRENT_QUEUE_DURATION                             NUMBER
     CLIENT_IDENTIFIER                                  VARCHAR2(64)
     BLOCKING_SESSION_STATUS                            VARCHAR2(11)
     BLOCKING_INSTANCE                                  NUMBER
     BLOCKING_SESSION                                   NUMBER
     FINAL_BLOCKING_SESSION_STATUS                      VARCHAR2(11)
     FINAL_BLOCKING_INSTANCE                            NUMBER
     FINAL_BLOCKING_SESSION                             NUMBER
     SEQ#                                               NUMBER
     EVENT#                                             NUMBER
     EVENT                                              VARCHAR2(64)
     P1TEXT                                             VARCHAR2(64)
     P1                                                 NUMBER
     P1RAW                                              RAW(8)
     P2TEXT                                             VARCHAR2(64)
     P2                                                 NUMBER
     P2RAW                                              RAW(8)
     P3TEXT                                             VARCHAR2(64)
     P3                                                 NUMBER
     P3RAW                                              RAW(8)
     WAIT_CLASS_ID                                      NUMBER
     WAIT_CLASS#                                        NUMBER
     WAIT_CLASS                                         VARCHAR2(64)
     WAIT_TIME                                          NUMBER
     SECONDS_IN_WAIT                                    NUMBER
     STATE                                              VARCHAR2(19)
     WAIT_TIME_MICRO                                    NUMBER
     TIME_REMAINING_MICRO                               NUMBER
     TIME_SINCE_LAST_WAIT_MICRO                         NUMBER
     SERVICE_NAME                                       VARCHAR2(64)
     SQL_TRACE                                          VARCHAR2(8)
     SQL_TRACE_WAITS                                    VARCHAR2(5)
     SQL_TRACE_BINDS                                    VARCHAR2(5)
     SQL_TRACE_PLAN_STATS                               VARCHAR2(10)
     SESSION_EDITION_ID                                 NUMBER
     CREATOR_ADDR                                       RAW(4)
     CREATOR_SERIAL#                                    NUMBER
     ECID                                               VARCHAR2(64)
  • 2. Re: Monitoring Oracle session with SQL_ID
    vlethakula Expert
    Currently Being Moderated
    col sid form 9999
    col curr form a80 head " Current SQL"
    bre on sid skip 2
    set long 10000
    select a.sid sid,b.sql_text curr,b.sql_id
    from v$session a, v$sql b
    where a.sql_address=b.address
    and a.sid=&sessid;---------------->Enter session ID
  • 3. Re: Monitoring Oracle session with SQL_ID
    Veeresh.S Pro
    Currently Being Moderated
    select sql_id from v$session where sid=123; ---->gives sql id
    select sql_text from v$sql where sql_id='ABCD1234'; ----->gives sql query/text of the respective sql_id
  • 4. Re: Monitoring Oracle session with SQL_ID
    imran khan Journeyer
    Currently Being Moderated
    If we need to find for a past time..then? suppose a query which was ran 24 hours back. I would like to know whether it is possible to know the user session with sql_id executed 24 hours back.

    imran khan
  • 5. Re: Monitoring Oracle session with SQL_ID
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    How can I know a SQL_ID belonging to a user/schema in Oracle.
    There are not.
    SQLs do not belong to a user/schema in Oracle.

    User session can issue an SQL, can execute it. During that time you can see this query in V$SESSION.SQL_ID and later in PREV_SQL_ID.
    After that SQL looses association with sessions and belong purely to pool.
    The SQL can be reused by other sessions and other users.
    Though SQL keeps its PARSING_SCHEMA_NAME. May be this is you are looking for?
    But it is not that the SQL belongs to this user/schema. It means that when SQL was parsed the current session schema was this schema, so name resolution will start from this schema.
    Any user can set arbitrary current schema by ALTER SERSSION.
  • 6. Re: Monitoring Oracle session with SQL_ID
    905562 Explorer
    Currently Being Moderated
    If you have the option, query dba_hist_active_sess_history.
  • 7. Re: Monitoring Oracle session with SQL_ID
    Veeresh.S Pro
    Currently Being Moderated
    imran khan wrote:
    If we need to find for a past time..then? suppose a query which was ran 24 hours back. I would like to know whether it is possible to know the user session with sql_id executed 24 hours back.

    imran khan
    Withdrawn

    Edited by: Veeresh.S on Dec 7, 2012 8:47 PM
  • 8. Re: Monitoring Oracle session with SQL_ID
    Max Seleznev Explorer
    Currently Being Moderated
    There's a bunch of DBA_HIST_* views.

    For example DBA_HIST_SQLTEXT contains both sql_id and the text of the statement. DBA_HIST_SQLSTAT has execution details. Of cause statement may not be captured due to its short duration. In this case you can try ASH views.
  • 9. Re: Monitoring Oracle session with SQL_ID
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    For example DBA_HIST_SQLTEXT contains both sql_id and the text of the statement.
    but it does not contain who executed it.
  • 10. Re: Monitoring Oracle session with SQL_ID
    Max Seleznev Explorer
    Currently Being Moderated
    But SQL_HIST_SQLSTAT does. Ever heard of joins?
  • 11. Re: Monitoring Oracle session with SQL_ID
    Dom Brooks Guru
    Currently Being Moderated
    Because many users could execute the same SQL - that is part of the shareable SQL model...

    If that is not the case for your situation then you might find what you want from V$SQL.PARSING_SCHEMA_ID/PARSING_SCHEMA_NAME or the same from DBA_HIST_SQLSTAT.

    Otherwise, if you want to look at ASH for possible samples of sessions actively executing a specific sql statement then see V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY.
  • 12. Re: Monitoring Oracle session with SQL_ID
    EdStevens Guru
    Currently Being Moderated
    Dom Brooks wrote:
    Because many users could execute the same SQL - that is part of the shareable SQL model...

    If that is not the case for your situation then you might find what you want from V$SQL.PARSING_SCHEMA_ID/PARSING_SCHEMA_NAME or the same from DBA_HIST_SQLSTAT.

    Otherwise, if you want to look at ASH for possible samples of sessions actively executing a specific sql statement then see V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY.
    And of course the OP needs to be warned that use of most of the DBA_HIST* tables and the ASH reports require the additional performance pack licenses.
  • 13. Re: Monitoring Oracle session with SQL_ID
    Dom Brooks Guru
    Currently Being Moderated
    but it does not contain who executed it.
    As it looks like he's already accessed it, I thought it a bit late...

    Plus covered by others
    If you have the option...
  • 14. Re: Monitoring Oracle session with SQL_ID
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    But SQL_HIST_SQLSTAT does.
    do you mean DBA_HIST_SQLSTAT?
    Post your SQL that shows who executed a query.
    Ever heard of joins?
    never.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points