прямой доступ к SGA / v$ как select from x$
Динамические view v$ построены на основе fixed таблиц x$. Одна из views словаря содержит описание всех v$ views. Её название v$fixed_view_definition. Вот какие данные о v$session содержатся в ней:
SQL> select view_definition from v$fixed_view_definition where view_name='V$SESSION';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select SADDR , SID , SERIAL# , AUDSID , PADDR , USER# , USERNAME , COMMAND , OW
NERID, TADDR , LOCKWAIT , STATUS , SERVER , SCHEMA# , SCHEMANAME ,OSUSER , PROCE
SS , MACHINE , TERMINAL , PROGRAM , TYPE , SQL_ADDRESS , SQL_HASH_VALUE , PREV_S
QL_ADDR , PREV_HASH_VALUE , MODULE , MODULE_HASH , ACTION , ACTION_HASH , CLIENT
_INFO , FIXED_TABLE_SEQUENCE , ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK#
, ROW_WAIT_ROW# , LOGON_TIME , LAST_CALL_ET , PDML_ENABLED , FAILOVER_TYPE , FAI
LOVER_METHOD , FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, P
Q_STATUS, CURRENT_QUEUE_DURATION, CLIENT_IDENTIFIER from GV$SESSION where inst_i
d = USERENV('Instance')
Информации немного :) Всё, что мы узнали, это то, что v$session построена на базе gv$session. Кстати, такое построение наблюдается в Oracle начиная с 8-ой версии. Ладно, смотрим, что из себя представляет gv$session:
SQL> select view_definition from v$fixed_view_definition where view_name='GV$SESSION';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,addr,indx,ksuseser,ksuudses,ksusepro, ksuudlui,ksuudlna,ksuudoct,
ksusesow, decode(ksusetrn,hextoraw('00'),null,ksusetrn), decode(ksqpswat,hextor
aw('00'),null,ksqpswat), decode(bitand(ksuseidl,11),1,'ACTIVE',0, decode(bitand(
ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED'), decode(k
sspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'), ksuudsid,ksuudsna,ksuseunm,
ksusepid,ksusemnm,ksusetid,ksusepnm, decode(bitand(ksuseflg,19),17,'BACKGROUND',
1,'USER',2,'RECURSIVE','?'), ksusesql, ksusesqh, ksusepsq, ksusepha, ksuseapp, k
suseaph, ksuseact, ksuseach, ksusecli, ksusefix, ksuseobj, ksusefil, ksuseblk,
ksuseslt, ksuseltm, ksusectm, decode(bitand(ksusepfl, 16),0,'NO','YES'), deco
de(ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'), decode(ksusefm,1,
'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'), decode(ksusefs, 1, 'YES', 'NO'), ks
usegrp, decode(bitand(ksusepfl,16),16,'ENABLED', decode(bitand(ksusepfl,32),3
2,'FORCED','DISABLED')), decode(bitand(ksusepfl,64),64,'FORCED', decode(bitan
d(ksusepfl,128),128,'DISABLED','ENABLED')), decode(bitand(ksusepfl,512),512,'FO
RCED', decode(bitand(ksusepfl,256),256,'DISABLED','ENABLED')), ksusecqd, ksus
eclid from x$ksuse where bitand(ksspaflg,1)!=0 and bitand(ksuseflg,1)!=0
Уже лучше. Информация хоть и не отформатирована, но есть как таковая.
Делаем ещё один запрос:
SQL> desc gv$session Name Null? Type ----------------------------------------------------- -------- ------------------------------------ INST_ID NUMBER 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(12) MACHINE VARCHAR2(64) TERMINAL VARCHAR2(30) PROGRAM VARCHAR2(48) TYPE VARCHAR2(10) SQL_ADDRESS RAW(4) SQL_HASH_VALUE NUMBER PREV_SQL_ADDR RAW(4) PREV_HASH_VALUE NUMBER MODULE VARCHAR2(48) MODULE_HASH NUMBER ACTION VARCHAR2(32) 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 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)
Отформатировав текст из запроса 2 и используя информацию из запроса 3, создаём (вручную или с помощью вспомогательного скрипта) следующий текст:
select
inst_id, -- INST_ID NUMBER
addr, -- SADDR RAW(4)
indx, -- SID NUMBER
ksuseser, -- SERIAL# NUMBER
ksuudses, -- AUDSID NUMBER
ksusepro, -- PADDR RAW(4)
ksuudlui, -- USER# NUMBER
ksuudlna, -- USERNAME VARCHAR2(30)
ksuudoct, -- COMMAND NUMBER
ksusesow, -- OWNERID NUMBER
decode -- TADDR VARCHAR2(8)
(ksusetrn, hextoraw ('00'), null, ksusetrn),
decode -- LOCKWAIT VARCHAR2(8)
(ksqpswat, hextoraw ('00'), null, ksqpswat),
decode -- STATUS VARCHAR2(8)
(bitand (ksuseidl, 11), 1, 'ACTIVE', 0, decode (bitand (ksuseflg, 4096), 0,
'INACTIVE', 'CACHED'), 2, 'SNIPED', 3, 'SNIPED', 'KILLED'),
decode -- SERVER VARCHAR2(9)
(ksspatyp, 1, 'DEDICATED', 2, 'SHARED', 3, 'PSEUDO', 'NONE'),
ksuudsid, -- SCHEMA# NUMBER
ksuudsna, -- SCHEMANAME VARCHAR2(30)
ksuseunm, -- OSUSER VARCHAR2(30)
ksusepid, -- PROCESS VARCHAR2(12)
ksusemnm, -- MACHINE VARCHAR2(64)
ksusetid, -- TERMINAL VARCHAR2(30)
ksusepnm, -- PROGRAM VARCHAR2(48)
decode -- TYPE VARCHAR2(10)
(bitand (ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?'),
ksusesql, -- SQL_ADDRESS RAW(4)
ksusesqh, -- SQL_HASH_VALUE NUMBER
ksusepsq, -- PREV_SQL_ADDR RAW(4)
ksusepha, -- PREV_HASH_VALUE NUMBER
ksuseapp, -- MODULE VARCHAR2(48)
ksuseaph, -- MODULE_HASH NUMBER
ksuseact, -- ACTION VARCHAR2(32)
ksuseach, -- ACTION_HASH NUMBER
ksusecli, -- CLIENT_INFO VARCHAR2(64)
ksusefix, -- FIXED_TABLE_SEQUENCE NUMBER
ksuseobj, -- ROW_WAIT_OBJ# NUMBER
ksusefil, -- ROW_WAIT_FILE# NUMBER
ksuseblk, -- ROW_WAIT_BLOCK# NUMBER
ksuseslt, -- ROW_WAIT_ROW# NUMBER
ksuseltm, -- LOGON_TIME DATE
ksusectm, -- LAST_CALL_ET NUMBER
decode -- PDML_ENABLED VARCHAR2(3)
(bitand (ksusepfl, 16), 0, 'NO', 'YES'),
decode -- FAILOVER_TYPE VARCHAR2(13)
(ksuseft, 2, 'SESSION', 4, 'SELECT', 8, 'TRANSACTIONAL', 'NONE'),
decode -- FAILOVER_METHOD VARCHAR2(10)
(ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'),
decode -- FAILED_OVER VARCHAR2(3)
(ksusefs, 1, 'YES', 'NO'),
ksusegrp, -- RESOURCE_CONSUMER_GROUP VARCHAR2(32)
decode -- PDML_STATUS VARCHAR2(8)
(bitand (ksusepfl, 16), 16, 'ENABLED', decode (bitand (ksusepfl, 32), 32,
'FORCED', 'DISABLED')),
decode -- PDDL_STATUS VARCHAR2(8)
(bitand (ksusepfl, 64), 64, 'FORCED', decode (bitand (ksusepfl, 128), 128,
'DISABLED', 'ENABLED')),
decode -- PQ_STATUS VARCHAR2(8)
(bitand (ksusepfl, 512), 512, 'FORCED', decode (bitand (ksusepfl, 256), 256,
'DISABLED', 'ENABLED')),
ksusecqd, -- CURRENT_QUEUE_DURATION NUMBER
ksuseclid -- CLIENT_IDENTIFIER VARCHAR2(64)
from
x$ksuse
where
bitand (ksspaflg, 1) !=0 and bitand (ksuseflg, 1) !=0
В итоге, мы наглядно видим, что для получения, к примеру, username, нам придётся прочитать поле kssuudlna из x$ksuse. А для получения поля status нам надо прочитать поля ksuseidl, ksuseflg и после этого проделать небольшие вычисления.