| View_Name |
Meanings |
Come from |
| GV$ENQUEUE_LOCK |
This view displays all locks owned by enqueue state objects. |
SELECT /*+ ORDERED USE_NL(L), USE_NL(S), USE_NL(R) +*/
s.inst_id, l.addr, l.ksqlkadr, s.ksusenum, r.ksqrsidt, r.ksqrsid1,
r.k sqrsid2, l.ksqlkmod, l.ksqlkreq, l.ksqlkctim, l.ksqlklblk
FROM x$ksqeq l, x$ksuse s, x$ksqrs r
WHERE l.ksqlkses = s.addr
AND BITAND (l.kssobflg, 1) != 0
AND (l.ksqlkmod != 0 OR l.ksqlkreq != 0)
AND l.ksqlkres = r.addr
|
| GV$PARAMETER |
Displays information about the initialization parameters. |
SELECT x.inst_id, x.indx + 1, ksppinm, ksppity, ksppstvl, ksppstdf,
DECODE (BITAND (ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
DECODE (BITAND (ksppiflg / 65536, 3),
1, 'IMMEDIATE',
2, 'DEFERRED',
3, 'IMMEDIATE',
'FALSE'
),
DECODE (BITAND (ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
DECODE (BITAND (ksppstvf, 2), 2, 'TRUE', 'FALSE'), ksppdesc,
ksppstcmnt
FROM x$ksppi x, x$ksppcv y
WHERE (x.indx = y.indx)
AND ((TRANSLATE (ksppinm, '_', '#') NOT LIKE '#%') OR (ksppstdf = 'FALSE')
)
|
| GV$RESOURCE_LIMIT |
Global resource use for some of the system resources |
SELECT inst_id, ksurlmnm, ksurlmcv, ksurlmmv,
LPAD (DECODE (BITAND (ksurlmfg, 1),
0, TO_CHAR (ksurlmia),
'UNLIMITED'
),
10
),
LPAD (DECODE (BITAND (ksurlmfg, 2),
0, TO_CHAR (ksurlmlv),
'UNLIMITED'
),
10
)
FROM x$ksurlmt
|
| GV$SEGSTAT |
Displays information about segment-level statistics |
select inst_id,
fts_tsn,
fts_objn,
fts_objd,
fts_statnam,
fts_statid,
fts_staval
from x$ksolsfts
where fts_inte = 0
|
| GV$SESSION (Oracle9iR2 Version) |
Lists session information for each current session. |
SELECT inst_id, addr, indx, ksuseser, ksuudses, ksusepro, ksuudlui, ksuudlna,
ksuudoct, ksusesow, DECODE (ksusetrn, HEXTORAW ('00'), NULL, ksusetrn),
DECODE (ksqpswat, HEXTORAW ('00'), NULL, ksqpswat),
DECODE (BITAND (ksuseidl, 11),
1, 'ACTIVE',
0, DECODE (BITAND (ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),
2, 'SNIPED',
3, 'SNIPED',
'KILLED'
),
DECODE (ksspatyp, 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, ksuseaph, ksuseact,
ksuseach, ksusecli, ksusefix, ksuseobj, ksusefil, ksuseblk, ksuseslt,
ksuseltm, ksusectm, DECODE (BITAND (ksusepfl, 16), 0, 'NO', 'YES'),
DECODE (ksuseft,
2, 'SESSION',
4, 'SELECT',
8, 'TRANSACTIONAL',
'NONE'
),
DECODE (ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'),
DECODE (ksusefs, 1, 'YES', 'NO'), ksusegrp,
DECODE (BITAND (ksusepfl, 16),
16, 'ENABLED',
DECODE (BITAND (ksusepfl, 32), 32, 'FORCED', 'DISABLED')
),
DECODE (BITAND (ksusepfl, 64),
64, 'FORCED',
DECODE (BITAND (ksusepfl, 128), 128, 'DISABLED', 'ENABLED')
),
DECODE (BITAND (ksusepfl, 512),
512, 'FORCED',
DECODE (BITAND (ksusepfl, 256), 256, 'DISABLED', 'ENABLED')
),
ksusecqd, ksuseclid
FROM x$ksuse
WHERE BITAND (ksspaflg, 1) != 0 AND BITAND (ksuseflg, 1) != 0
|
| GV$SESSION (Oracle10gR1 Version) |
Lists session information for each current session. |
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', 'NONE'),
s.ksuudsid, s.ksuudsna, s.ksuseunm, s.ksusepid, s.ksusemnm, 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.ksusepsq,
s.ksusepha, s.ksusepsi,
DECODE (s.ksusepch, 65535, TO_NUMBER (NULL), s.ksusepch), s.ksuseapp,
s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj,
s.ksusefil, s.ksuseblk, s.ksuseslt, 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, 'GLOBAL',
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),
s.ksuseblocker
),
s.ksuseseq, s.ksuseopc, e.kslednam, e.ksledp1, s.ksusep1, s.ksusep1r,
e.ksledp2, s.ksusep2, s.ksusep2r, e.ksledp3, s.ksusep3, s.ksusep3r,
e.ksledclassid, e.ksledclass#, e.ksledclass,
DECODE (s.ksusetim,
0, 0,
-1, -1,
-2, -2,
DECODE (ROUND (s.ksusetim / 10000),
0, -1,
ROUND (s.ksusetim / 10000)
)
),
s.ksusewtm,
DECODE (s.ksusetim,
0, 'WAITING',
-2, 'WAITED UNKNOWN TIME',
-1, 'WAITED SHORT TIME',
DECODE (ROUND (s.ksusetim / 10000),
0, 'WAITED SHORT TIME',
'WAITED KNOWN TIME'
)
),
s.ksusesvc
FROM x$ksuse s, x$ksled e
WHERE BITAND (s.ksspaflg, 1) != 0
AND BITAND (s.ksuseflg, 1) != 0
AND s.ksuseopc = e.indx
|
| GV$SESSION (Oracle10gR2 Version) |
Lists session information for each current session. |
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', 'NONE'),
s.ksuudsid, s.ksuudsna, s.ksuseunm, s.ksusepid, s.ksusemnm, 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.ksusepsq,
s.ksusepha, s.ksusepsi,
DECODE (s.ksusepch, 65535, TO_NUMBER (NULL), s.ksusepch), s.ksuseapp,
s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj,
s.ksusefil, s.ksuseblk, s.ksuseslt, 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)
),
s.ksuseseq, s.ksuseopc, e.kslednam, e.ksledp1, s.ksusep1, s.ksusep1r,
e.ksledp2, s.ksusep2, s.ksusep2r, e.ksledp3, s.ksusep3, s.ksusep3r,
e.ksledclassid, e.ksledclass#, e.ksledclass,
DECODE (s.ksusetim,
0, 0,
-1, -1,
-2, -2,
DECODE (ROUND (s.ksusetim / 10000),
0, -1,
ROUND (s.ksusetim / 10000)
)
),
s.ksusewtm,
DECODE (s.ksusetim,
0, 'WAITING',
-2, 'WAITED UNKNOWN TIME',
-1, 'WAITED SHORT TIME',
DECODE (ROUND (s.ksusetim / 10000),
0, 'WAITED SHORT TIME',
'WAITED KNOWN TIME'
)
),
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')
FROM x$ksuse s, x$ksled e
WHERE BITAND (s.ksspaflg, 1) != 0
AND BITAND (s.ksuseflg, 1) != 0
AND s.ksuseopc = e.indx
|
| GV$SQL |
Lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. |
SELECT inst_id, kglnaobj,
kglobhs0
+ kglobhs1
+ kglobhs2
+ kglobhs3
+ kglobhs4
+ kglobhs5
+ kglobhs6
+ kglobt16,
kglobt08 + kglobt11, kglobt10, kglobt01, DECODE (kglobhs6, 0, 0, 1),
DECODE (kglhdlmd, 0, 0, 1), kglhdlkc, kglobt04, kglobt05, kglobpc6,
kglhdldc, SUBSTR (TO_CHAR (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
kglhdivc, kglobt12, kglobt13, kglobt14, kglobt15, kglobt02,
DECODE (kglobt32,
0, 'NONE',
1, 'ALL_ROWS',
2, 'FIRST_ROWS',
3, 'RULE',
4, 'CHOOSE',
'UNKNOWN'
),
kglobtn0, kglobt17, kglobt18, kglhdkmk, kglhdpar, kglobtp0, kglnahsh,
kglobt30, kglobt09, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21,
kglobts2, kglobt06, kglobt07, kglobt28, kglhdadr, kglobt29,
DECODE (BITAND (kglobt00, 64), 64, 'Y', 'N'),
DECODE (kglobsta,
1, 'VALID',
2, 'VALID_AUTH_ERROR',
3, 'VALID_COMPILE_ERROR',
4, 'VALID_UNAUTH',
5, 'INVALID_UNAUTH',
6, 'INVALID'
),
kglobt31, SUBSTR (TO_CHAR (kglobtt0, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
DECODE (kglobt33, 1, 'Y', 'N'), kglhdclt
FROM x$kglcursor
WHERE kglhdadr != kglhdpar AND kglobt02 != 0
|
| GV$SQLAREA |
Lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution. . |
SELECT inst_id, kglnaobj,
SUM ( kglobhs0
+ kglobhs1
+ kglobhs2
+ kglobhs3
+ kglobhs4
+ kglobhs5
+ kglobhs6
),
SUM (kglobt08 + kglobt11), SUM (kglobt10), SUM (kglobt01),
COUNT (*) - 1, SUM (DECODE (kglobhs6, 0, 0, 1)),
DECODE (SUM (DECODE (kglhdlmd, 0, 0, 1)),
0, 0,
SUM (DECODE (kglhdlmd, 0, 0, 1)) - 1
),
SUM (kglhdlkc) / 2, SUM (kglobt04), SUM (kglobt05), SUM (kglobpc6),
SUM (kglhdldc) - 1,
SUBSTR (TO_CHAR (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
SUM (kglhdivc), SUM (kglobt12), SUM (kglobt13), SUM (kglobt14),
SUM (kglobt15), SUM (DECODE (kglobt09, 0, kglobt02, 0)),
DECODE (COUNT (*) - 1,
1, DECODE (SUM (DECODE (kglobt09, 0, kglobt32, 0)),
0, 'NONE',
1, 'ALL_ROWS',
2, 'FIRST_ROWS',
3, 'RULE',
4, 'CHOOSE',
'UNKNOWN'
),
'MULTIPLE CHILDREN PRESENT'
),
SUM (DECODE (kglobt09, 0, kglobt17, 0)),
SUM (DECODE (kglobt09, 0, kglobt18, 0)),
DECODE (SUM (DECODE (kglhdkmk, 0, 0, 1)),
0, 0,
SUM (DECODE (kglhdkmk, 0, 0, 1)) - 1
),
kglhdpar, kglnahsh, kglobts0, kglobt19, kglobts1, kglobt20,
SUM (kglobt21), SUM (kglobt06), SUM (kglobt07),
DECODE (kglobt33, 1, 'Y', 'N'), kglhdclt
FROM x$kglcursor
GROUP BY inst_id,
kglnaobj,
kglhdpar,
kglnahsh,
kglnatim,
kglobts0,
kglobt19,
kglobts1,
kglobt20,
DECODE (kglobt33, 1, 'Y', 'N'),
kglhdclt
HAVING SUM (DECODE (kglobt09, 0, kglobt02, 0)) != 0
|
