[20120307]檢視v$session檢視的定義.txt
[20120307]檢視v$session檢視的定義.txt
今天檢視v$session的原始定義，查詢GV$FIXED_VIEW_DEFINITION
這才發現在這個檢視的定義不全，原來VIEW_DEFINITION僅僅儲存4000個位元組。
--昏！透過這個沒有辦法獲得完整的定義。
--如何獲得完整的定義呢？
執行如下命令：
$ tkprof test_ora_24079.trc aaa.txt
檢視aaa.txt檔案：
我查詢
SELECT view_name FROM v$fixed_view_definition WHERE LENGTH (view_definition) >= 4000;
發現有幾個檢視定義等於4000的，也許會不完整。
4.還有一些比較簡單的方法:
--alter system flush shared_pool; 這個可以不執行！
select * from gv$session ;
利用toad的SGA trace查詢特定的字元，比如s.ksuseser很快能找到定義的檢視。
今天檢視v$session的原始定義，查詢GV$FIXED_VIEW_DEFINITION
SQL> column VIEW_DEFINITION format a100
SQL> select * from GV$FIXED_VIEW_DEFINITION where view_name='GV$SESSION';
INST_ID VIEW_NAME VIEW_DEFINITION
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
1 GV$SESSION select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.k
susesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetrn),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACH
ED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO',4,'POOLE
D','NONE'), s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid, s.ksusemnm,s.ksusemnp,s.ksusetid,s.ksusepn
m, decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh,
s.ksusesqi, decode(s.ksusesch, 65535, to_number(null), s.ksusesch), s.ksusesesta, decode(s.ksuses
eid, 0, to_number(null), s.ksuseseid), s.ksusepsq, s.ksusepha, s.ksusepsi, decode(s.ksusepch, 6553
5, to_number(null), s.ksusepch), s.ksusepesta, decode(s.ksusepeid, 0, to_number(null), s.ksusepeid
), decode(s.ksusepeo,0,to_number(null),s.ksusepeo), decode(s.ksusepeo,0,to_number(null),s.ksusepes
), decode(s.ksusepco,0,to_number(null), decode(bitand(s.ksusstmbv, power(2,11)), power(2,11
), s.ksusepco, to_number(null))), decode(s.ksusepcs,0,to_number(null), decod
e(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepcs, to_number(null))), s.ks
useapp, s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseb
lk, s.ksuseslt, s.ksuseorafn, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxopt, 12),0,'NO','YES'),
decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),decode(s.ksusefm,1,'BASIC',2,'PR
ECONNECT',4,'PREPARSE','NONE'),decode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxo
pt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),decode(bitand(s.ksusepxopt,2
),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),3
2,'FORCED',decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED')), s.ksusecqd, s.ksuseclid, deco
de(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO H
OLDER', 4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967
294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291, to_number(n
ull),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),429
4967294,to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291, to_numb
er(null),bitand(s.ksuseblocker, 65535)), decode(s.ksusefblocker,4294967295,'UNKNOWN', 4294967294,
'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER', 4294967291,'NOT IN WAIT','VALID'),decode(s.k
susefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294
967292,to_number(null),4294967291, to_number(null),bitand(s.ksusefblocker, 2147418112)/65536),decod
e(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null),
4294967292,to_number(null),4294967291, to_number(null),bitand(s.ksusefblocker, 65535)), w.kslwtse
q,w.kslwtevt,e.kslednam,e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.k
slwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass, decode(w.kslwtinwait, 0,decode(
bitand(w.kslwtflags,256), 0,-2, decode(round(w.kslwtstime/10000),
0,-1, round(w.kslwtstime/10000))), 0), decode(w.k
slwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000), round(w.kslwtstime/1000000)), decode(w.ksl
wtinwait,1,'WAITING', decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME', decode(round(w.ksl
wtstime/10000),0,'WAITED SHORT TIME', 'WAITED KNOWN TIME'))),w.kslwtstime, decode(w.kslwtinwait,0
,to_number(null), decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), w.kslwtltime,s.ksusesvc, decod
e(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),d
這才發現在這個檢視的定義不全，原來VIEW_DEFINITION僅僅儲存4000個位元組。
SQL> DESC GV$FIXED_VIEW_DEFINITION
Name Null? Type
---------------- -------- --------------
INST_ID NUMBER
VIEW_NAME VARCHAR2(30)
VIEW_DEFINITION VARCHAR2(4000)
SQL> column VIEW_DEFINITION format a100
SQL> select * from GV$FIXED_VIEW_DEFINITION where view_name='GV$FIXED_VIEW_DEFINITION';
INST_ID VIEW_NAME VIEW_DEFINITION
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
1 GV$FIXED_VIEW_DEFINITION select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indx
SQL> desc x$kqfvt
Name Null? Type
--------- -------- --------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KQFTPSEL VARCHAR2(4000)
--昏！透過這個沒有辦法獲得完整的定義。
--如何獲得完整的定義呢？
執行如下命令：
alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';
select * from gv$session ;
alter session set events '10053 trace name context off';
alter system flush shared_pool;
alter session set events '10046 trace name context forever, level 12';
select * from gv$session ;
alter session set events '10046 trace name context off';
$ tkprof test_ora_24079.trc aaa.txt
檢視aaa.txt檔案：
SQL ID: 5ax0q1md1w99p
Plan Hash: 0
select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,
s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw('00'),null, s.ksusetrn),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,
'INACTIVE','CACHED'),2,'SNIPED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,
'DEDICATED',2,'SHARED',3,'PSEUDO',4,'POOLED','NONE'), s.ksuudsid,
s.ksuudsna,s.ksuseunm,s.ksusepid, s.ksusemnm,s.ksusemnp,s.ksusetid,
s.ksusepnm, decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,
'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch,
65535, to_number(null), s.ksusesch), s.ksusesesta, decode(s.ksuseseid, 0,
to_number(null), s.ksuseseid), s.ksusepsq, s.ksusepha, s.ksusepsi,
decode(s.ksusepch, 65535, to_number(null), s.ksusepch), s.ksusepesta,
decode(s.ksusepeid, 0, to_number(null), s.ksusepeid), decode(s.ksusepeo,0,
to_number(null),s.ksusepeo), decode(s.ksusepeo,0,to_number(null),
s.ksusepes), decode(s.ksusepco,0,to_number(null),
decode(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepco,
to_number(null))), decode(s.ksusepcs,0,to_number(null),
decode(bitand(s.ksusstmbv, power(2,11)), power(2,11), s.ksusepcs,
to_number(null))), s.ksuseapp, s.ksuseaph, s.ksuseact, s.ksuseach,
s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt,
s.ksuseorafn, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxopt, 12),0,'NO',
'YES'),decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),
decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),
decode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxopt,4),
4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),
decode(bitand(s.ksusepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,
'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),32,'FORCED',
decode(bitand(s.ksusepxopt,16),16,'DISABLED','ENABLED')), s.ksusecqd,
s.ksuseclid, decode(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294,
'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLDER', 4294967291,'NOT IN
WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,
to_number(null), 4294967293,to_number(null), 4294967292,to_number(null),
4294967291, to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),
decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null)
, 4294967293,to_number(null), 4294967292,to_number(null),4294967291,
to_number(null),bitand(s.ksuseblocker, 65535)), decode(s.ksusefblocker,
4294967295,'UNKNOWN', 4294967294, 'UNKNOWN',4294967293,'UNKNOWN',
4294967292,'NO HOLDER', 4294967291,'NOT IN WAIT','VALID'),
decode(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null)
, 4294967293,to_number(null), 4294967292,to_number(null),4294967291,
to_number(null),bitand(s.ksusefblocker, 2147418112)/65536),
decode(s.ksusefblocker,4294967295,to_number(null),4294967294,to_number(null)
, 4294967293,to_number(null), 4294967292,to_number(null),4294967291,
to_number(null),bitand(s.ksusefblocker, 65535)), w.kslwtseq,w.kslwtevt,
e.kslednam,e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,
e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass,
decode(w.kslwtinwait, 0,decode(bitand(w.kslwtflags,256),
0,-2, decode(round(w.kslwtstime/10000),
0,-1, round(w.kslwtstime/10000))), 0),
decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000),
round(w.kslwtstime/1000000)), decode(w.kslwtinwait,1,'WAITING',
decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME',
decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME', 'WAITED KNOWN
TIME'))),w.kslwtstime, decode(w.kslwtinwait,0,to_number(null),
decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), w.kslwtltime,s.ksusesvc,
decode(bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),
decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,
128),128,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,65536) +
bitand(s.ksuseflg2,131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC'),
s.ksuudsae,s.ksusecre,s.ksusecsn,s.ksuseecid
from
x$ksuse s, x$ksled e, x$kslwt w where bitand(s.ksspaflg,1)!=0 and
bitand(s.ksuseflg,1)!=0 and s.indx=w.kslwtsid and w.kslwtevt=e.indx
我查詢
SELECT view_name FROM v$fixed_view_definition WHERE LENGTH (view_definition) >= 4000;
發現有幾個檢視定義等於4000的，也許會不完整。
SQL> SELECT view_name FROM v$fixed_view_definition WHERE LENGTH (view_definition) >= 4000;
VIEW_NAME
------------------------------
GV$SESSION
GV$STREAMS_CAPTURE
V$RECOVERY_AREA_USAGE
GV$ACTIVE_SESSION_HISTORY
V$RMAN_BACKUP_SUBJOB_DETAILS
V$BACKUP_DATAFILE_SUMMARY
V$BACKUP_CONTROLFILE_SUMMARY
GV$IOSTAT_FILE
8 rows selected.
4.還有一些比較簡單的方法:
--alter system flush shared_pool; 這個可以不執行！
select * from gv$session ;
利用toad的SGA trace查詢特定的字元，比如s.ksuseser很快能找到定義的檢視。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-717962/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211019]V$DETACHED_SESSION檢視.txtSession
- [20240911]檢視超長檢視的定義2.txt
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- 檢視錶的定義
- V$ACTIVE_SESSION_HISTORY檢視的使用Session
- [20181004]12c dba_source檢視定義.txt
- 檢視瀏覽器請求的session 瀏覽器怎麼檢視session瀏覽器Session
- [20210528]V$INDEXED_FIXED_COLUMN檢視.txtIndex
- [20210418]查詢v$檢視問題.txt
- [20221130]測試訪問檢視v$session幾種情況的效能差異.txtSession
- 瀏覽器檢視Session瀏覽器Session
- session檢視中wait_timeSessionAI
- [20181103]12c檢視V$EVENT_NAME.txt
- [20201207]12c v$open_cursor檢視.txt
- 谷歌工具檢視CSS程式碼定義的位置谷歌CSS
- 自定義檢視指令
- [20180503]檢視提示使用索引.txt索引
- 11 UML中的邏輯檢視、程序檢視、實現檢視、部署檢視
- 檢視V$DATAGUARD_STATS
- Laravel 自定義檢視元件Laravel元件
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- [20230323]ps命令檢視thread.txtthread
- ASP.NET Core 5.0 MVC中的檢視分類——佈局檢視、啟動檢視、具體檢視、分部檢視ASP.NETMVC
- [20190312]檢視v$datafile欄位OFFLINE_CHANGE#, ONLINE_CHANGE#.txt
- [Django REST framework - 檢視元件之檢視基類、檢視擴充套件類、檢視子類、檢視集]DjangoRESTFramework元件套件
- [20190416]檢視shared latch gets的變化.txt
- [20190324]奇怪的GV$FILESPACE_USAGE檢視.txt
- [20210422]如何檢視字元的ascii編碼.txt字元ASCII
- Oracle檢視已被使用的open_cursors&session_cached_cursorsOracleSession
- [20180907]訪問v$檢視與一致性讀取.txt
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與檢視v$open_cursor.txt
- [20200417]跟蹤特定sql語句以及v$open_cursor檢視.txtSQL
- 檢視ORACLE中鎖定物件Oracle物件
- [20210208][20200426]檢視shared latch gets的變化.txt
- Oracle普通檢視和物化檢視的區別Oracle
- 自定義 Command 檢視 Laravel 日誌Laravel
- 2.7.11 檢視引數設定的方法
- [20230510]19c dg無法使用dbms_metadata.get_ddl檢視錶結構定義.txt
- [20231012]如何檢視unicode編碼內容.txtUnicode