1 2 Previous Next 18 Replies Latest reply: Dec 7, 2012 1:42 PM by jgarry RSS

    Monitoring Oracle session with SQL_ID

    imran khan
      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
          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
            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
              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
                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)
                  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
                    If you have the option, query dba_hist_active_sess_history.
                    • 7. Re: Monitoring Oracle session with SQL_ID
                      Veeresh.S
                      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
                        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)
                          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
                            But SQL_HIST_SQLSTAT does. Ever heard of joins?
                            • 11. Re: Monitoring Oracle session with SQL_ID
                              Dom Brooks
                              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
                                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
                                  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)
                                    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