Oracle动态性能视图学习笔记(7)_v$session

目录

参考文档<>Chapter 24

##################################################################
1 Overview
##################################################################
This view has one row for every session connected to the database instance.
The sessions include user sessions, as well as background processes.

v$session is basically an information view used for finding the SID or SADDR of a user.

##################################################################
2 Userful Columns for v$session
##################################################################
1) STATUS:
Active: executing a SQL statement(waiting for/ using a resource)
正在执行语句，可能在等待或使用资源

Inactive: waiting for more work(???)

Killed: marked to be killed.
被标志为killed

2) SID: Session identifier.

3) SERIAL#: Counter, which is incremented each time a SID is reused by another session.
(When a session ends and another session starts and uses the same SID)
会话重用次数？不是。

SQL> select sid from v$mystat where rownum =1;

       SID
----------
        26

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        442

SQL> disc
Not logged on

SQL> conn gzdc/gzdc2009@testdb
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as gzdc

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        444

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        444

SQL> disc
Not logged on

SQL> conn gzdc/gzdc2009@testdb
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as gzdc

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        450

只是一个标识数。

4) AUDSID: Auditing session ID uniquely identifies a session over the life of a database.
It si also useful when finding the parallel query slaves for a query coordinator.(during the PQ execution they have

the same AUDSID)
审计会话ID，在整个数据库的生命周期中有唯一性，同一个并行进程的几个会话具有相同的AUDSID.

5) USERNAME: The Oracle user name for the connected session

Client Information

1) OSUSER: Operating system user name for the client process.
客户端的操作系统用户名

2) MACHINE: Machine where the client process is executing

3) TERMINAL: Terminal where the client process is running

4) PROCESS: Process ID of the client process(pid:ppid)
客户端父子进程ID号

5) PROGRAM: Client program being executed by the client process
客户端进程名

##################################################################
3 Join Columns for v$session
##################################################################
1) v$session.sid = v$session_wait.sid, v$sesstat.sid, v$lock.sid, v$session_event.sid, v$open_cursor.sid

2) v$session.sql_hash_value = v$sqltext.hash_value, v$sqlarea.hash_value, v$sql.hash_value;

3) v$session.sql_address = v$sqltext.address, v$sqlarea.address, v$sql.address;

4) v$session.taddr = v$transaction.addr

5) v$session.paddr = v$process.addr

##################################################################
4 示例
##################################################################
1) Finding Your Session

SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID');

2) Finding a Session When the Machine Is Known

SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE machine like 'WORKGROUP\MOUSE-PC%';

SQL中如何转义/,
不需要转义。
当字段中包含%时。
select * from procedure_log where errmsg like 'xxx\%xxx' escape '\';

##################################################################
4 问题
##################################################################

ITPub博客

Oracle动态性能视图学习笔记(7)_v$session

最新文章