一、在Oracle中,V$SESSION视图中有哪些比较实用的列?
Oracle 数据库中V 、 G V 、GV 、GV、XKaTeX parse error: Expected group after '_' at position 3: 、V_̲、GV_ 之 间 的 关 系 说 明 G V 之间的关系说明 GV 之间的关系说明GV:全局视图,针对多个实例环境。
V : 针 对 某 个 实 例 的 视 图 。 X :针对某个实例的视图。 X :针对某个实例的视图。X:是GVKaTeX parse error: Expected group after '_' at position 22: …源,Oracle内部表。 GV_̲:是GVKaTeX parse error: Expected group after '_' at position 8: 的同义词。 V_̲:是V 的 同 义 词 。 可 以 使 用 V 的同义词。 可以使用V 的同义词。可以使用VFIXED_VIEW_DEFINITION视图查询到V 视 图 和 G V 视图和GV 视图和GV视图的定义。
讲到Oracle的会话,就必须首先对[G]V$SESSION这个视图中的每个列都非常熟悉。
(DBA) V$SESSION该视图在Oracle 11gR2下包含97列,在Oracle 12cR2下增加了6列,共包含103列。
列 | 数据类型 | 说明 |
---|---|---|
SADDR | RAW(4 / 8) | 会话地址,对应于V$TRANSACTION.SES_ADDR列。 |
SID | NUMBER | 会话标识符。 |
SERIAL# | NUMBER | 会话序列号,用来唯一地标识会话对象。如果该会话结束且其它会话以相同的会话ID开始,那么可以保证会话级的命令被应用到正确的会话对象。 |
AUDSID | NUMBER | 审计会话ID,审查SESSION ID的唯一性,通常也用于寻找并行查询模式。SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE AUDSID = USERENV(‘SESSIONID’); |
PADDR | RAW(4 / 8) | 拥有这个会话的进程地址,对应于VKaTeX parse error: Expected 'EOF', got '#' at position 54: …B.SID, B.SERIAL#̲, C.SPID FROM VSESSION B, V$PROCESS C WHERE B.PADDR = C.ADDR; |
USER# | NUMBER | Oracle用户标识符。 |
USERNAME | VARCHAR2(30) | Oracle用户名。 |
COMMAND | NUMBER | 正在执行的SQL语句类型(分析的最后一个语句)。关于该列值的含义,请参阅V S Q L C O M M A N D . C O M M A N D 列 。 如 果 该 列 的 值 为 0 , 那 么 表 示 并 没 有 在 V SQLCOMMAND.COMMAND列。如果该列的值为0,那么表示并没有在V SQLCOMMAND.COMMAND列。如果该列的值为0,那么表示并没有在VSESSION视图里记录。 |
OWNERID | NUMBER | 如果值为2147483644,那么此列的内容无效,否则此列包含拥有可移植会话的用户标符。对于利用并行从服务器的操作,将这个值解释为一个4字节的值,其低位两字节表示会话号,而高位字节表示查询协调程序的实例ID。 |
TADDR | VARCHAR2(8) | 表示事务处理状态对象的地址,对应于V$TRANSACTION.ADDR列。 |
LOCKWAIT | VARCHAR2(8) | 等待锁的地址,对应于V$LOCK的KADDR列;若当前会话没有被阻塞则为空. |
STATUS | VARCHAR2(8) | 会话的状态:ACTIVE:当前正在执行SQL语句(waiting for/using a resource);INACTIVE:等待操作(即等待需要执行的SQL语句);KILLED:标记为终止,删除;CACHED:为Oracle*XA使用而临时高速缓存;SNIPED:会话不活动,在客户机上等待,该状态不再被允许变为ACTIVE。 |
二、在Oracle中,如何让普通用户可以杀掉自己用户的会话?
普通用户想要杀掉会话必须要具有ALTER
SYSTEM的权限,但是由于该权限过大,用户可能使用该权限错杀其他用户的会话,所以,有没有其它办法可以实现该功能呢?
该类问题也是DBA工作中常遇到的问题,下面给出一种解决方案:
首先,可以创建一个查询自己会话信息的视图,将该视图创建公共同义词,然后创建一个存储过程,该存储过程实现杀掉会话的需要,最后将该存储过程的执行权限赋权给PUBLIC即可解决这个问题。
--代码实现过程如下所示:
CREATE OR REPLACE VIEW VW_MYOWNERSESSION_LHR AS SELECT * FROM V$SESSION WHERE USERNAME = USER;
CREATE OR REPLACE PUBLIC SYNONYM SYN_MYOWNERSESSION_LHR FOR SYS.VW_MYOWNERSESSION_LHR;
--创建存储过程用于杀掉会话:
CREATE OR REPLACE PROCEDURE PRO_KILL_MYOWN_SESSION_LHR(P_INST IN NUMBER,
P_SID IN NUMBER,
P_SERIAL# IN NUMBER) IS
V_IGNORE PLS_INTEGER;
V_VERSION VARCHAR2(10);
V_INST_ID NUMBER;
BEGIN
SELECT COUNT(*)
INTO V_IGNORE
FROM GV$SESSION D
WHERE USERNAME = USER
AND SID = P_SID
AND SERIAL# = P_SERIAL#
AND D.INST_ID = P_INST;
SELECT SUBSTR(V.VERSION, 1, INSTR(V.VERSION, '.') - 1), V.INSTANCE_NUMBER
INTO V_VERSION, V_INST_ID
FROM V$INSTANCE V;
IF (V_IGNORE = 1) THEN
IF (V_VERSION = '10' AND V_INST_ID <> P_INST) THEN
RAISE_APPLICATION_ERROR(-20001,
'Please connect to 【INSTANCE:' || P_INST ||
'】,then retry!');
ELSIF (V_VERSION = '10' AND V_INST_ID = P_INST) THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || P_SID || ',' ||
P_SERIAL# || ''' IMMEDIATE';
ELSE
EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || P_SID || ',' ||
P_SERIAL# || ',@' || P_INST || ''' IMMEDIATE';
END IF;
ELSE
RAISE_APPLICATION_ERROR(-20002,
'You do not own session ''' || P_SID || ',' ||
P_SERIAL# ||',@' || P_INST || '''');
END IF;
END PRO_KILL_MYOWN_SESSION_LHR;
/
CREATE OR REPLACE PUBLIC SYNONYM PRO_KILL_SESSION_LHR FOR SYS.PRO_KILL_MYOWN_SESSION_LHR;
GRANT SELECT ON SYN_MYOWNERSESSION_LHR TO PUBLIC;
GRANT EXECUTE ON PRO_KILL_SESSION_LHR TO PUBLIC;
--使用方法如下所示:
SELECT USERENV('INSTANCE'),USERENV('SID') FROM DUAL;
SELECT V.INST_ID, SID,SERIAL#,PADDR,STATUS FROM SYN_MYOWNERSESSION_LHR V WHERE SID=1008 AND V.INST_ID=1 ;--假设上一步查询出来的SID为1008,实例号为1
EXEC PRO_KILL_SESSION_LHR(1,1008,35038);--假设上一步查询出来的SERIAL#为35038
--使用示例如下所示:
--使用SYS用户杀PMON进程的会话:
SYS@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE A.PROGRAM LIKE '%PMON%';
SID SERIAL# USERENV('INSTANCE') USERNAME
---------- ---------- ------------------- ------------------------------
125 1 1
SYS@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,125,1);
BEGIN PRO_KILL_SESSION_LHR(1,125,1); END;
*
ERROR at line 1:
ORA-20002: You do not own session '125,1,@1'
ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
ORA-06512: at line 1
--由于系统进程的用户名为空,所以,避免了误杀系统进程。
--使用SYS用户杀普通用户的会话如下所示:
SYS@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE USERNAME='LHR';
SID SERIAL# USERENV('INSTANCE') USERNAME
---------- ---------- ------------------- ------------------------------
79 16453 1 LHR
SYS@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,79,16453);
BEGIN PRO_KILL_SESSION_LHR(1,79,16453); END;
*
ERROR at line 1:
ORA-20002: You do not own session '79,16453,@1'
ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
ORA-06512: at line 1
SYS@lhrdb21> conn lhr/lhr
Connected.
LHR@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,79,16453);
PL/SQL procedure successfully completed.
--由于79会话属于LHR用户,所以,避免了误杀其它用户的会话,当使用LHR用户的时候,可以正常杀掉会话。
--使用LHR用户杀其它用户的会话:
LHR@lhrdb21> SELECT A.SID,A.SERIAL#,USERENV('INSTANCE'),USERNAME FROM V$SESSION A WHERE USERNAME='LHRTEST';
SID SERIAL# USERENV('INSTANCE') USERNAME
---------- ---------- ------------------- ------------------------------
142 12947 1 LHRTEST
LHR@lhrdb21> EXEC PRO_KILL_SESSION_LHR(1,142,12947);
BEGIN PRO_KILL_SESSION_LHR(1,142,12947); END;
*
ERROR at line 1:
ORA-20002: You do not own session '142,12947,@1'
ORA-06512: at "SYS.PRO_KILL_MYOWN_SESSION_LHR", line 36
ORA-06512: at line 1
--普通用户LHR也不能杀掉其它用户LHRTEST的会话。
三、在Oracle中,如何查看某一个会话是否被其它会话阻塞?
SELECT DISTINCT A.BLOCKING_SESSION_STATUS,
A.BLOCKING_INSTANCE,
A.BLOCKING_SESSION,
A.EVENT
FROM GV$SESSION A
WHERE A.SID = (SELECT USERENV('SID') FROM DUAL);
四、在Oracle中,如何查到会话正在执行的SQL语句?
SELECT DISTINCT B.SQL_ID, B.SQL_TEXT
FROM GV$SESSION A, GV$SQL B
WHERE A.SQL_ID = B.SQL_ID
AND A.INST_ID = B.INST_ID
AND A.SID = (SELECT USERENV('SID') FROM DUAL);