session

记录session信息的动态视图为

SQL> desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR                                              RAW(8)
<strong> SID                                                NUMBER</strong>
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(8)
 USER#                                              NUMBER
<strong> USERNAME                                           VARCHAR2(30)</strong>
 COMMAND                                            NUMBER
 OWNERID                                            NUMBER
 TADDR                                              VARCHAR2(16)
 LOCKWAIT                                           VARCHAR2(16)
 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(8)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
<strong> SQL_EXEC_START                                     DATE</strong>
 SQL_EXEC_ID                                        NUMBER
 PREV_SQL_ADDR                                      RAW(8)
 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
<strong> LOGON_TIME                                         DATE</strong>
 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(8)
 CREATOR_SERIAL#                                    NUMBER
 ECID                                               VARCHAR2(64)

100

SQL> desc v$session

Name Null? Type

----------------------------------------- -------- ----------------------------

SADDR RAW(8)

SID NUMBER

SERIAL# NUMBER

AUDSID NUMBER

PADDR RAW(8)

USER# NUMBER

USERNAME VARCHAR2(30)

COMMAND NUMBER

OWNERID NUMBER

TADDR VARCHAR2(16)

LOCKWAIT VARCHAR2(16)

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(8)

SQL_HASH_VALUE NUMBER

SQL_ID VARCHAR2(13)

SQL_CHILD_NUMBER NUMBER

SQL_EXEC_START DATE

SQL_EXEC_ID NUMBER

PREV_SQL_ADDR RAW(8)

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(8)

CREATOR_SERIAL# NUMBER

ECID VARCHAR2(64)

拣选常用字段进行展示（加粗字体可见sys用户于15年6月8日登陆）

sql语句：

select sid,username,status,logon_time
from v$session;

1 2	select sid,username,status,logon_time from v$session;

具体执行：

SQL> select sid,username,status,logon_time
  2  from v$session;

       SID USERNAME                       STATUS   LOGON_TIM
---------- ------------------------------ -------- ---------
         1                                ACTIVE   08-JUN-15
         2                                ACTIVE   08-JUN-15
         3                                ACTIVE   08-JUN-15
         4                                ACTIVE   08-JUN-15
         5                                ACTIVE   08-JUN-15
         6                                ACTIVE   08-JUN-15
         7                                ACTIVE   08-JUN-15
         8                                ACTIVE   08-JUN-15
         9                                ACTIVE   08-JUN-15
        11                                ACTIVE   08-JUN-15
        15                                ACTIVE   08-JUN-15

       SID USERNAME                       STATUS   LOGON_TIM
---------- ------------------------------ -------- ---------
        17                                ACTIVE   08-JUN-15
<strong>       125 SYS                            ACTIVE   08-JUN-15</strong>
       126                                ACTIVE   08-JUN-15
       127                                ACTIVE   08-JUN-15
       128                                ACTIVE   08-JUN-15
       129                                ACTIVE   08-JUN-15
       130                                ACTIVE   08-JUN-15
       131                                ACTIVE   08-JUN-15
       132                                ACTIVE   08-JUN-15
       136                                ACTIVE   08-JUN-15
       137                                ACTIVE   08-JUN-15

22 rows selected.

SQL> select sid,username,status,logon_time

2 from v$session;

SID USERNAME STATUS LOGON_TIM

---------- ------------------------------ -------- ---------

1 ACTIVE 08-JUN-15

2 ACTIVE 08-JUN-15

3 ACTIVE 08-JUN-15

4 ACTIVE 08-JUN-15

5 ACTIVE 08-JUN-15

6 ACTIVE 08-JUN-15

7 ACTIVE 08-JUN-15

8 ACTIVE 08-JUN-15

9 ACTIVE 08-JUN-15

11 ACTIVE 08-JUN-15

15 ACTIVE 08-JUN-15

SID USERNAME STATUS LOGON_TIM

---------- ------------------------------ -------- ---------

17 ACTIVE 08-JUN-15

125 SYS ACTIVE 08-JUN-15

126 ACTIVE 08-JUN-15

127 ACTIVE 08-JUN-15

128 ACTIVE 08-JUN-15

129 ACTIVE 08-JUN-15

130 ACTIVE 08-JUN-15

131 ACTIVE 08-JUN-15

132 ACTIVE 08-JUN-15

136 ACTIVE 08-JUN-15

137 ACTIVE 08-JUN-15

22 rows selected.

sql 历史

v$sql及v$sqlarea存放着统计信息在调优时使用居多，但其sql是不全的，如果想获得完整的sql需使用v$sqltext。

关于这三个视图的区别与用途见引用文章：http://blog.itpub.net/38542/viewspace-885839/

正在执行的sql

sql语句：

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address;

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT

from v$session a, v$sqlarea b

where a.sql_address = b.address;

具体执行：

SQL> column username format a10
SQL> column sid format a10
SQL> column sql_text format a20
SQL> column sql_fulltext format a20
SQL> select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  2  from v$session a, v$sqlarea b
  3  where a.sql_address = b.address;

USERNAME          SID SQL_TEXT             SQL_FULLTEXT
---------- ---------- -------------------- --------------------
SYS        ########## select a.username, a select a.username, a
                      .sid,b.SQL_TEXT, b.S .sid,b.SQL_TEXT, b.S
                      QL_FULLTEXT from v$s QL_FULLTEXT
                      ession a, v$sqlarea  from v$session a, v$
                      b where a.sql_addres sqlarea
                      s = b.address

SQL> column username format a10

SQL> column sid format a10

SQL> column sql_text format a20

SQL> column sql_fulltext format a20

SQL> select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT

2 from v$session a, v$sqlarea b

3 where a.sql_address = b.address;

USERNAME SID SQL_TEXT SQL_FULLTEXT

---------- ---------- -------------------- --------------------

SYS ########## select a.username, a select a.username, a

.sid,b.SQL_TEXT, b.S .sid,b.SQL_TEXT, b.S

QL_FULLTEXT from v$s QL_FULLTEXT

ession a, v$sqlarea from v$session a, v$

b where a.sql_addres sqlarea

s = b.address

执行过的sql

sql语句：

#查询ARCHIVE用户的包含“select 1”的SQL
select sql_text,last_active_time from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('ARCHIVE') and sql_text like '%select 1%';
#查询ARCHIVE用户执行的命令，根据执行时间降序排列
select sql_text from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('ARCHIVE') order by t.LAST_ACTIVE_TIME desc;
#查询所有执行过的sql：“SELECT 1 FROM DUAL”
select sql_text,sql_id from v$sqltext where sql_text like 'SELECT 1 FROM DUAL';

#查询ARCHIVE用户的包含“select 1”的SQL

select sql_text,last_active_time from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('ARCHIVE') and sql_text like '%select 1%';

#查询ARCHIVE用户执行的命令，根据执行时间降序排列

select sql_text from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('ARCHIVE') order by t.LAST_ACTIVE_TIME desc;

#查询所有执行过的sql：“SELECT 1 FROM DUAL”

select sql_text,sql_id from v$sqltext where sql_text like 'SELECT 1 FROM DUAL';

不再演示。

oracle session与sql历史查询

session

sql 历史

正在执行的sql

执行过的sql

Leave a Comment 取消回复