GV$ACCESS
select
distinct s.inst_id, -- INST_ID NUMBER
s.ksusenum, -- SID NUMBER
o.kglnaown, -- OWNER VARCHAR2(64)
o.kglnaobj, -- OBJECT VARCHAR2(1000)
decode -- TYPE VARCHAR2(24)
(o.kglobtyp, 0, 'CURSOR', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 15, 'OBJECT', 16, 'USER', 17, 'DBLINK', 18, 'PIPE', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 25, 'INDEX-ORGANIZED TABLE', 26, 'REPLICATION OBJECT GROUP', 27, 'REPLICATION PROPAGATOR', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 31, 'JAVA JAR', 'INVALID TYPE')
from
x$ksuse s,
x$kglob o,
x$kgldp d,
x$kgllk l
where
l.kgllkuse=s.addr and l.kgllkhdl=d.kglhdadr and l.kglnahsh=d.kglnahsh and o.kglnahsh=d.kglrfhsh and o.kglhdadr=d.kglrfhdl
GV$ACTIVE_INSTANCES
select
inst_id, -- INST_ID NUMBER
ksiminum, -- INST_NUMBER NUMBER
rpad (ksimstr, 60) -- INST_NAME VARCHAR2(60)
from
x$ksimsi
GV$AQ1
select
INST_ID, -- INST_ID NUMBER
KWQSIQID, -- QID NUMBER
KWQSINWT, -- WAITING NUMBER
KWQSINRD, -- READY NUMBER
KWQSINEX, -- EXPIRED NUMBER
KWQSINCO, -- TOTAL_CONSUMERS NUMBER
KWQSITWT, -- TOTAL_WAIT NUMBER
DECODE -- AVERAGE_WAIT NUMBER
(KWQSINCO, 0, 0, KWQSITWT/KWQSINCO)
from
X$KWQSI
GV$ARCHIVE
select
le.inst_id, -- INST_ID NUMBER
le.lenum, -- GROUP# NUMBER
le.lethr, -- THREAD# NUMBER
le.leseq, -- SEQUENCE# NUMBER
decode -- CURRENT VARCHAR2(3)
(bitand (le.leflg, 8), 0, 'NO', 'YES'),
to_number (le.lelos) -- FIRST_CHANGE# NUMBER
from
x$kccle le,
x$kccdi di
where
bitand (di.diflg, 1) !=0 and le.ledup!=0 and bitand (le.leflg, 1) =0 and (to_number (le.lelos) <=to_number (di.difas) or bitand (le.leflg, 8) =0)
GV$ARCHIVED_LOG
select
inst_id, -- INST_ID NUMBER
alrid, -- RECID NUMBER
alstm, -- STAMP NUMBER
alnam, -- NAME VARCHAR2(513)
althp, -- THREAD# NUMBER
alseq, -- SEQUENCE# NUMBER
to_number (alrls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(alrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (allos), -- FIRST_CHANGE# NUMBER
to_date -- FIRST_TIME DATE
(allot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (alnxs), -- NEXT_CHANGE# NUMBER
to_date -- NEXT_TIME DATE
(alnxt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
albct, -- BLOCKS NUMBER
albsz, -- BLOCK_SIZE NUMBER
decode -- CREATOR VARCHAR2(4)
(bitand (alflg, 16), 16, 'ARCH', decode (bitand (alflg, 32), 32, 'FGRD', decode (bitand (alflg, 64), 64, 'RMAN', decode (bitand (alflg, 128), 128, 'RMAN', NULL)))),
decode -- REGISTRAR VARCHAR2(4)
(bitand (alflg, 4), 4, 'RFS', decode (bitand (alflg, 16), 16, 'ARCH', decode (bitand (alflg, 32), 32, 'FGRD', decode (bitand (alflg, 64), 64, 'RMAN', decode (bitand (alflg, 128), 128, 'SRMN', NULL))))),
decode -- STANDBY_DEST VARCHAR2(3)
(bitand (alflg, 8), 0, 'NO', 'YES'),
decode -- ARCHIVED VARCHAR2(3)
(bitand (alflg, 2), 0, 'NO', 'YES'),
decode -- DELETED VARCHAR2(3)
(bitand (alflg, 1), 0, 'NO', 'YES'),
to_date -- COMPLETION_TIME DATE
(altsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kccal
GV$ARCHIVE_DEST
select
inst_id, -- INST_ID NUMBER
to_number (ADDID), -- DEST_ID NUMBER
decode -- STATUS VARCHAR2(9)
(ADSTS, 1, 'VALID', 2, 'INACTIVE', 3, 'DEFERRED', 4, 'ERROR', 5, 'DISABLED', 6, 'BAD PARAM', 'UNKNOWN'),
decode -- BINDING VARCHAR2(9)
(ADMND, 0, 'OPTIONAL', 'MANDATORY'),
decode -- NAME_SPACE VARCHAR2(7)
(ADSES, 0, 'SYSTEM', 'SESSION'),
decode -- TARGET VARCHAR2(7)
(ADRMT, 0, 'PRIMARY', 'STANDBY'),
to_number (ADROP), -- REOPEN_SECS NUMBER
ADDNM, -- DESTINATION VARCHAR2(256)
to_date -- FAIL_DATE DATE
(ADFDT, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (ADFSQ), -- FAIL_SEQUENCE NUMBER
to_number (ADFBK), -- FAIL_BLOCK NUMBER
ADFER -- ERROR VARCHAR2(256)
from
x$kcrrdest
GV$ARCHIVE_PROCESSES
select
inst_id, -- INST_ID NUMBER
to_number (kcrrxpid), -- PROCESS NUMBER
decode -- STATUS VARCHAR2(10)
(kcrrxsts, 1, 'SCHEDULED', 2, 'STARTING', 3, 'ACTIVE', 4, 'STOPPING', 5, 'TERMINATED', 'STOPPED'),
to_number (kcrrxseq), -- LOG_SEQUENCE NUMBER
decode -- STATE VARCHAR2(4)
(kcrrxsta, 1, 'BUSY', 'IDLE')
from
x$kcrrarch
GV$BACKUP
select
inst_id, -- INST_ID NUMBER
hxfil, -- FILE# NUMBER
decode -- STATUS VARCHAR2(18)
(hxerr, 0, decode (bitand (fhsta, 1), 0, 'NOT ACTIVE', 'ACTIVE'), 1, 'FILE MISSING', 2, 'OFFLINE NORMAL', 3, 'NOT VERIFIED', 4, 'FILE NOT FOUND', 5, 'CANNOT OPEN FILE', 6, 'CANNOT READ HEADER', 7, 'CORRUPT HEADER', 8, 'WRONG FILE TYPE', 9, 'WRONG DATABASE', 10, 'WRONG FILE NUMBER', 11, 'WRONG FILE CREATE', 12, 'WRONG FILE CREATE', 16, 'DELAYED OPEN', 'UNKNOWN ERROR'),
to_number (fhbsc), -- CHANGE# NUMBER
to_date -- TIME DATE
(fhbti, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kcvfhonl
GV$BACKUP_ASYNC_IO
select
inst_id, -- INST_ID NUMBER
sid, -- SID NUMBER
ser, -- SERIAL NUMBER
setid, -- USE_COUNT NUMBER
devtype, -- DEVICE_TYPE VARCHAR2(17)
decode -- TYPE VARCHAR2(9)
(type, 1, 'INPUT', 2, 'OUTPUT', 3, 'AGGREGATE', 'UNKNOWN'),
decode -- STATUS VARCHAR2(11)
(status, 1, 'NOT STARTED', 2, 'IN PROGRESS', 3, 'FINISHED', 'UNKNOWN'),
filename, -- FILENAME VARCHAR2(513)
set_count, -- SET_COUNT NUMBER
set_stamp, -- SET_STAMP NUMBER
block_size -- BUFFER_SIZE NUMBER
* buffer_size,
buffer_count, -- BUFFER_COUNT NUMBER
decode -- TOTAL_BYTES NUMBER
(total_blocks, 0, null, total_blocks) * block_size,
to_date -- OPEN_TIME DATE
(open_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- CLOSE_TIME DATE
(close_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
(to_date -- ELAPSED_TIME NUMBER
(close_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (open_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) * 8640000,
decode -- MAXOPENFILES NUMBER
(aggregate_count, 0, null, aggregate_count) * 1,
blocks * block_size, -- BYTES NUMBER
decode -- EFFECTIVE_BYTES_PER_SECOND NUMBER
(instr (open_time, close_time), 1, null, round ((blocks * block_size) / ((to_date (close_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (open_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) * 86400))) * 1,
async_short_count -- IO_COUNT NUMBER
+ async_long_count + async_ready,
async_ready, -- READY NUMBER
async_short_count, -- SHORT_WAITS NUMBER
async_short_tottime, -- SHORT_WAIT_TIME_TOTAL NUMBER
async_short_maxtime, -- SHORT_WAIT_TIME_MAX NUMBER
async_long_count, -- LONG_WAITS NUMBER
async_long_tottime, -- LONG_WAIT_TIME_TOTAL NUMBER
async_long_maxtime -- LONG_WAIT_TIME_MAX NUMBER
from
x$ksfqp
where
bitand (flags, 2) = 2
GV$BACKUP_CORRUPTION
select
inst_id, -- INST_ID NUMBER
fcrid, -- RECID NUMBER
fcstm, -- STAMP NUMBER
fcbss, -- SET_STAMP NUMBER
fcbsc, -- SET_COUNT NUMBER
fcpno, -- PIECE# NUMBER
fcdfp, -- FILE# NUMBER
fcblk, -- BLOCK# NUMBER
fccnt, -- BLOCKS NUMBER
to_number (fcscn), -- CORRUPTION_CHANGE# NUMBER
decode -- MARKED_CORRUPT VARCHAR2(3)
(bitand (fcflg, 1), 1, 'YES', 'NO')
from
x$kccfc
GV$BACKUP_DATAFILE
select
inst_id, -- INST_ID NUMBER
bfrid, -- RECID NUMBER
bfstm, -- STAMP NUMBER
bfbss, -- SET_STAMP NUMBER
bfbsc, -- SET_COUNT NUMBER
bfdfp, -- FILE# NUMBER
to_number (bfcrs), -- CREATION_CHANGE# NUMBER
to_date -- CREATION_TIME DATE
(bfcrt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (bfrls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(bfrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- INCREMENTAL_LEVEL NUMBER
(bitand (bfflg, 1), 1, bflvl, NULL),
to_number (bfics), -- INCREMENTAL_CHANGE# NUMBER
to_number (bfcps), -- CHECKPOINT_CHANGE# NUMBER
to_date -- CHECKPOINT_TIME DATE
(bfcpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (bfafs), -- ABSOLUTE_FUZZY_CHANGE# NUMBER
bfncb, -- MARKED_CORRUPT NUMBER
bfmcb, -- MEDIA_CORRUPT NUMBER
bflcb, -- LOGICALLY_CORRUPT NUMBER
bffsz, -- DATAFILE_BLOCKS NUMBER
bfbct, -- BLOCKS NUMBER
bfbsz, -- BLOCK_SIZE NUMBER
bflor, -- OLDEST_OFFLINE_RANGE NUMBER
to_date -- COMPLETION_TIME DATE
(bftsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- CONTROLFILE_TYPE VARCHAR2(1)
(bfdfp, 0, decode (bitand (bfflg, 2), 2, 'S', 'B'), NULL)
from
x$kccbf
GV$BACKUP_DEVICE
select
inst_id, -- INST_ID NUMBER
devtype, -- DEVICE_TYPE VARCHAR2(17)
devname -- DEVICE_NAME VARCHAR2(513)
from
x$ksfhdvnt
GV$BACKUP_PIECE
select
inst_id, -- INST_ID NUMBER
bprid, -- RECID NUMBER
bpstm, -- STAMP NUMBER
bpbss, -- SET_STAMP NUMBER
bpbsc, -- SET_COUNT NUMBER
bpnum, -- PIECE# NUMBER
bitand -- COPY# NUMBER
(bpflg, 12) /4 + 1,
bpdev, -- DEVICE_TYPE VARCHAR2(17)
bphdl, -- HANDLE VARCHAR2(513)
bpcmt, -- COMMENTS VARCHAR2(81)
bpmdh, -- MEDIA VARCHAR2(65)
bitand -- MEDIA_POOL NUMBER
(bpflg, 4080) / 16,
decode -- CONCUR VARCHAR2(3)
(bitand (bpflg, 2), 1, 'YES', 'NO'),
bptag, -- TAG VARCHAR2(32)
decode -- STATUS VARCHAR2(1)
(bitand (bpflg, 1+4096), 1, 'D', 4096, 'X', 0, 'A', '?'),
decode -- DELETED VARCHAR2(3)
(bitand (bpflg, 1), 1, 'YES', 'NO'),
to_date -- START_TIME DATE
(bptsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- COMPLETION_TIME DATE
(bptim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
(to_date -- ELAPSED_SECONDS NUMBER
(bptim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (bptsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) *86400
from
x$kccbp
GV$BACKUP_REDOLOG
select
inst_id, -- INST_ID NUMBER
blrid, -- RECID NUMBER
blstm, -- STAMP NUMBER
blbss, -- SET_STAMP NUMBER
blbsc, -- SET_COUNT NUMBER
blthp, -- THREAD# NUMBER
blseq, -- SEQUENCE# NUMBER
to_number (blrls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(blrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (bllos), -- FIRST_CHANGE# NUMBER
to_date -- FIRST_TIME DATE
(bllot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (blnxs), -- NEXT_CHANGE# NUMBER
to_date -- NEXT_TIME DATE
(blnxt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
blbct, -- BLOCKS NUMBER
blbsz -- BLOCK_SIZE NUMBER
from
x$kccbl
GV$BACKUP_SET
select
inst_id, -- INST_ID NUMBER
bsrid, -- RECID NUMBER
bsstm, -- STAMP NUMBER
bsbss, -- SET_STAMP NUMBER
bsbsc, -- SET_COUNT NUMBER
decode -- BACKUP_TYPE VARCHAR2(1)
(bitand (bstyp, 11), 1, 'D', 2, 'I', 8, 'L'),
decode -- CONTROLFILE_INCLUDED VARCHAR2(3)
(bitand (bstyp, 4+64), 4, 'YES', 68, 'SBY', 'NO'),
decode -- INCREMENTAL_LEVEL NUMBER
(bitand (bstyp, 16), 16, bslvl, NULL),
bspct, -- PIECES NUMBER
to_date -- START_TIME DATE
(bsbst, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- COMPLETION_TIME DATE
(bstsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
(to_date -- ELAPSED_SECONDS NUMBER
(bstsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (bsbst, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) *86400,
bsbsz, -- BLOCK_SIZE NUMBER
decode -- INPUT_FILE_SCAN_ONLY VARCHAR2(3)
(bitand (bstyp, 128), 128, 'YES', 'NO')
from
x$kccbs
GV$BACKUP_SYNC_IO
select
inst_id, -- INST_ID NUMBER
sid, -- SID NUMBER
ser, -- SERIAL NUMBER
setid, -- USE_COUNT NUMBER
devtype, -- DEVICE_TYPE VARCHAR2(17)
decode -- TYPE VARCHAR2(9)
(type, 1, 'INPUT', 2, 'OUTPUT', 3, 'AGGREGATE', 'UNKNOWN'),
decode -- STATUS VARCHAR2(11)
(status, 1, 'NOT STARTED', 2, 'IN PROGRESS', 3, 'FINISHED', 'UNKNOWN'),
filename, -- FILENAME VARCHAR2(513)
set_count, -- SET_COUNT NUMBER
set_stamp, -- SET_STAMP NUMBER
block_size -- BUFFER_SIZE NUMBER
* buffer_size,
buffer_count, -- BUFFER_COUNT NUMBER
decode -- TOTAL_BYTES NUMBER
(total_blocks, 0, null, total_blocks) * block_size,
to_date -- OPEN_TIME DATE
(open_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- CLOSE_TIME DATE
(close_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
(to_date -- ELAPSED_TIME NUMBER
(close_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (open_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) * 8640000,
decode -- MAXOPENFILES NUMBER
(aggregate_count, 0, null, aggregate_count) * 1,
blocks * block_size, -- BYTES NUMBER
decode -- EFFECTIVE_BYTES_PER_SECOND NUMBER
(instr (open_time, close_time), 1, null, round ((blocks * block_size) / ((to_date (close_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (open_time, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) * 86400))) * 1,
sync_count, -- IO_COUNT NUMBER
sync_tottime, -- IO_TIME_TOTAL NUMBER
sync_maxtime, -- IO_TIME_MAX NUMBER
decode -- DISCRETE_BYTES_PER_SECOND NUMBER
(sync_tottime, 0, NULL, round ((blocks *block_size) / sync_tottime * 100)) * 1
from
x$ksfqp
where
bitand (flags, 2) = 0
GV$BGPROCESS
select
p.inst_id, -- INST_ID NUMBER
p.ksbdppro, -- PADDR RAW(4)
p.ksbdpnam, -- NAME VARCHAR2(5)
d.ksbdddsc, -- DESCRIPTION VARCHAR2(64)
p.ksbdperr -- ERROR NUMBER
from
x$ksbdp p,
x$ksbdd d
where
p.indx=d.indx
GV$BH
select
bh.inst_id, -- INST_ID NUMBER
file#, -- FILE# NUMBER
dbablk, -- BLOCK# NUMBER
class, -- CLASS# NUMBER
decode -- STATUS VARCHAR2(4)
(state, 0, 'free', 1, 'xcur', 2, 'scur', 3, 'cr', 4, 'read', 5, 'mrec', 6, 'irec'),
x_to_null, -- XNC NUMBER
forced_reads, -- FORCED_READS NUMBER
forced_writes, -- FORCED_WRITES NUMBER
bh.le_addr, -- LOCK_ELEMENT_ADDR RAW(4)
name, -- LOCK_ELEMENT_NAME NUMBER
le_class, -- LOCK_ELEMENT_CLASS NUMBER
decode -- DIRTY VARCHAR2(1)
(bitand (flag, 1), 0, 'N', 'Y'),
decode -- TEMP VARCHAR2(1)
(bitand (flag, 16), 0, 'N', 'Y'),
decode -- PING VARCHAR2(1)
(bitand (flag, 1536), 0, 'N', 'Y'),
decode -- STALE VARCHAR2(1)
(bitand (flag, 16384), 0, 'N', 'Y'),
decode -- DIRECT VARCHAR2(1)
(bitand (flag, 65536), 0, 'N', 'Y'),
'N', -- NEW VARCHAR2(1)
obj, -- OBJD NUMBER
ts# -- TS# NUMBER
from
x$bh bh,
x$le le
where
bh.le_addr = le.le_addr (+)
GV$BSP
select
inst_id, -- INST_ID NUMBER
reqcr, -- CR_REQUESTS NUMBER
reqcur, -- CURRENT_REQUESTS NUMBER
reqdata, -- DATA_REQUESTS NUMBER
requndo, -- UNDO_REQUESTS NUMBER
reqtx, -- TX_REQUESTS NUMBER
rescur, -- CURRENT_RESULTS NUMBER
respriv, -- PRIVATE_RESULTS NUMBER
reszero, -- ZERO_RESULTS NUMBER
resdisk, -- DISK_READ_RESULTS NUMBER
resfail, -- FAIL_RESULTS NUMBER
fairdc, -- FAIRNESS_DOWN_CONVERTS NUMBER
faircl, -- FAIRNESS_CLEARS NUMBER
freedc, -- FREE_LOCK_ELEMENTS NUMBER
flush, -- FLUSHES NUMBER
flushq, -- FLUSHES_QUEUED NUMBER
flushf, -- FLUSH_QUEUE_FULL NUMBER
flushmx, -- FLUSH_MAX_TIME NUMBER
light, -- LIGHT_WORKS NUMBER
error -- ERRORS NUMBER
from
x$kclcrst
GV$BUFFER_POOL
select
inst_id, -- INST_ID NUMBER
bp_id, -- ID NUMBER
bp_name, -- NAME VARCHAR2(20)
bp_lo_sid, -- LO_SETID NUMBER
bp_hi_sid, -- HI_SETID NUMBER
bp_set_ct, -- SET_COUNT NUMBER
bp_size, -- BUFFERS NUMBER
0, -- LO_BNUM NUMBER
0 -- HI_BNUM NUMBER
from
x$kcbwbpd
where
bp_id > 0
GV$BUFFER_POOL_STATISTICS
select
kcbwbpd.inst_id, -- INST_ID NUMBER
kcbwbpd.bp_id, -- ID NUMBER
kcbwbpd.bp_name, -- NAME VARCHAR2(20)
sum (kcbwds.cnum_set), -- SET_MSIZE NUMBER
sum (kcbwds.cnum_repl), -- CNUM_REPL NUMBER
sum (kcbwds.cnum_write), -- CNUM_WRITE NUMBER
sum (kcbwds.cnum_set), -- CNUM_SET NUMBER
sum (kcbwds.buf_got), -- BUF_GOT NUMBER
sum (kcbwds.sum_wrt), -- SUM_WRITE NUMBER
sum (kcbwds.sum_scn), -- SUM_SCAN NUMBER
sum (kcbwds.fbwait), -- FREE_BUFFER_WAIT NUMBER
sum (kcbwds.wcwait), -- WRITE_COMPLETE_WAIT NUMBER
sum (kcbwds.bbwait), -- BUFFER_BUSY_WAIT NUMBER
sum (kcbwds.fbinsp), -- FREE_BUFFER_INSPECTED NUMBER
sum (kcbwds.dbinsp), -- DIRTY_BUFFERS_INSPECTED NUMBER
0, -- DB_BLOCK_CHANGE NUMBER
0, -- DB_BLOCK_GETS NUMBER
0, -- CONSISTENT_GETS NUMBER
sum (kcbwds.pread), -- PHYSICAL_READS NUMBER
sum (kcbwds.pwrite) -- PHYSICAL_WRITES NUMBER
from
x$kcbwds kcbwds,
x$kcbwbpd kcbwbpd
where
kcbwds.set_id >= kcbwbpd.bp_lo_sid and kcbwds.set_id <= kcbwbpd.bp_hi_sid and kcbwbpd.bp_size != 0
group by kcbwbpd.inst_id,
kcbwbpd.bp_id,
kcbwbpd.bp_name
GV$CIRCUIT
select
inst_id, -- INST_ID NUMBER
addr, -- CIRCUIT RAW(4)
kmcvcdpc, -- DISPATCHER RAW(4)
decode -- SERVER RAW(4)
(kmcvcpro, kmcvcdpc, hextoraw ('00'), kmcvcpro),
kmcvcwat, -- WAITER RAW(4)
kmcvcses, -- SADDR RAW(4)
kmcvcsta, -- STATUS VARCHAR2(16)
kmcvcque, -- QUEUE VARCHAR2(16)
kmcvcsz0, -- MESSAGE0 NUMBER
kmcvcsz1, -- MESSAGE1 NUMBER
kmcvcsz2, -- MESSAGE2 NUMBER
kmcvcsz3, -- MESSAGE3 NUMBER
kmcvcnmg, -- MESSAGES NUMBER
kmcvcnmb, -- BYTES NUMBER
kmcvcbrk, -- BREAKS NUMBER
kmcvcpre -- PRESENTATION VARCHAR2(16)
from
x$kmcvc
where
bitand (ksspaflg, 1) != 0
GV$CLASS_PING
select
inst_id, -- INST_ID NUMBER
decode -- CLASS VARCHAR2(18)
(indx, 1, 'data block', 2, 'sort block', 3, 'save undo block', 4, 'segment header', 5, 'save undo header', 6, 'free list', 7, 'extent map', 8, 'bitmap block', 9, 'bitmap index block', 10, 'unused', 11, 'undo header', 12, 'undo block'),
CLASS_X2NC, -- X_2_NULL NUMBER
CLASS_X2NFWC, -- X_2_NULL_FORCED_WRITE NUMBER
CLASS_X2NFSC, -- X_2_NULL_FORCED_STALE NUMBER
CLASS_X2SC, -- X_2_S NUMBER
CLASS_X2SFWC, -- X_2_S_FORCED_WRITE NUMBER
0, -- X_2_SSX NUMBER
0, -- X_2_SSX_FORCED_WRITE NUMBER
CLASS_S2NC, -- S_2_NULL NUMBER
CLASS_S2NFSC, -- S_2_NULL_FORCED_STALE NUMBER
0, -- SS_2_NULL NUMBER
0, -- SS_2_RLS NUMBER
0, -- OP_2_SS NUMBER
CLASS_N2XC, -- NULL_2_X NUMBER
CLASS_S2XC, -- S_2_X NUMBER
0, -- SSX_2_X NUMBER
CLASS_N2SC, -- NULL_2_S NUMBER
0 -- NULL_2_SS NUMBER
from
x$class_stat
GV$COMPATIBILITY
select
inst_id, -- INST_ID NUMBER
kcktyid, -- TYPE_ID VARCHAR2(8)
kcktyrls, -- RELEASE VARCHAR2(60)
kcktydsc -- DESCRIPTION VARCHAR2(64)
from
x$kckty
GV$COMPATSEG
select
inst_id, -- INST_ID NUMBER
kckceid, -- TYPE_ID VARCHAR2(8)
kckcerl, -- RELEASE VARCHAR2(60)
kckcevsn -- UPDATED VARCHAR2(60)
from
x$kckce
GV$CONTEXT
select
namespace, -- NAMESPACE VARCHAR2(64)
attribute, -- ATTRIBUTE VARCHAR2(64)
value -- VALUE VARCHAR2(4000)
from
x$context
GV$CONTROLFILE
select
inst_id, -- INST_ID NUMBER
decode -- STATUS VARCHAR2(7)
(bitand (cfflg, 1), 0, '', 1, 'INVALID'),
cfnam -- NAME VARCHAR2(513)
from
x$kcccf
GV$CONTROLFILE_RECORD_SECTION
select
inst_id, -- INST_ID NUMBER
decode -- TYPE VARCHAR2(17)
(indx, 0, 'DATABASE', 1, 'CKPT PROGRESS', 2, 'REDO THREAD', 3, 'REDO LOG', 4, 'DATAFILE', 5, 'FILENAME', 6, 'TABLESPACE', 7, 'RESERVED1', 8, 'RESERVED2', 9, 'LOG HISTORY', 10, 'OFFLINE RANGE', 11, 'ARCHIVED LOG', 12, 'BACKUP SET', 13, 'BACKUP PIECE', 14, 'BACKUP DATAFILE', 15, 'BACKUP REDOLOG', 16, 'DATAFILE COPY', 17, 'BACKUP CORRUPTION', 18, 'COPY CORRUPTION', 19, 'DELETED OBJECT', 20, 'PROXY COPY', 21, 'RESERVED4', 'UNKNOWN'),
rsrsz, -- RECORD_SIZE NUMBER
rsnum, -- RECORDS_TOTAL NUMBER
rsnus, -- RECORDS_USED NUMBER
rsiol, -- FIRST_INDEX NUMBER
rsilw, -- LAST_INDEX NUMBER
rsrlw -- LAST_RECID NUMBER
from
x$kccrs
GV$COPY_CORRUPTION
select
inst_id, -- INST_ID NUMBER
ccrid, -- RECID NUMBER
ccstm, -- STAMP NUMBER
ccdcp, -- COPY_RECID NUMBER
ccdcs, -- COPY_STAMP NUMBER
ccdfp, -- FILE# NUMBER
ccblk, -- BLOCK# NUMBER
cccnt, -- BLOCKS NUMBER
to_number (ccscn), -- CORRUPTION_CHANGE# NUMBER
decode -- MARKED_CORRUPT VARCHAR2(3)
(bitand (ccflg, 1), 1, 'YES', 'NO')
from
x$kcccc
GV$DATABASE
select
inst_id, -- INST_ID NUMBER
didbi, -- DBID NUMBER
didbn, -- NAME VARCHAR2(9)
to_date -- CREATED DATE
(dicts, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (dirls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(dirlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (diprs), -- PRIOR_RESETLOGS_CHANGE# NUMBER
to_date -- PRIOR_RESETLOGS_TIME DATE
(diprc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- LOG_MODE VARCHAR2(12)
(bitand (diflg, 1), 0, 'NOARCHIVELOG', 'ARCHIVELOG'),
to_number (discn), -- CHECKPOINT_CHANGE# NUMBER
to_number (difas), -- ARCHIVE_CHANGE# NUMBER
decode -- CONTROLFILE_TYPE VARCHAR2(7)
(bitand (diflg, 256), 256, 'CREATED', decode (bitand (diflg, 1024), 1024, 'STANDBY', decode (bitand (diflg, 32768), 32768, 'CLONE', decode (bitand (diflg, 4096), 4096, 'BACKUP', 'CURRENT')))),
to_date -- CONTROLFILE_CREATED DATE
(dicct, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
dicsq, -- CONTROLFILE_SEQUENCE# NUMBER
to_number (dickp_scn), -- CONTROLFILE_CHANGE# NUMBER
to_date -- CONTROLFILE_TIME DATE
(dickp_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- OPEN_RESETLOGS VARCHAR2(11)
(bitand (diflg, 4), 4, 'REQUIRED', decode (diirs, 0, 'NOT ALLOWED', 'ALLOWED')),
to_date -- VERSION_TIME DATE
(divts, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- OPEN_MODE VARCHAR2(10)
(didor, 0, 'MOUNTED', decode (didor, 1, 'READ WRITE', 'READ ONLY'))
from
x$kccdi
GV$DATAFILE
select
fe.inst_id, -- INST_ID NUMBER
fe.fenum, -- FILE# NUMBER
to_number -- CREATION_CHANGE# NUMBER
(fe.fecrc_scn),
to_date -- CREATION_TIME DATE
(fe.fecrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fe.fetsn, -- TS# NUMBER
fe.ferfn, -- RFILE# NUMBER
decode -- STATUS VARCHAR2(7)
(fe.fetsn, 0, decode (bitand (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'), decode (bitand (fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER')),
decode -- ENABLED VARCHAR2(10)
(fe.fedor, 2, 'READ ONLY', decode (bitand (fe.festa, 12), 0, 'DISABLED', 4, 'READ ONLY', 12, 'READ WRITE', 'UNKNOWN')),
to_number (fe.fecps), -- CHECKPOINT_CHANGE# NUMBER
to_date -- CHECKPOINT_TIME DATE
(fe.fecpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (fe.feurs), -- UNRECOVERABLE_CHANGE# NUMBER
to_date -- UNRECOVERABLE_TIME DATE
(fe.feurt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (fe.fests), -- LAST_CHANGE# NUMBER
decode -- LAST_TIME DATE
(fe.fests, NULL, to_date (NULL), to_date (fe.festt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')),
to_number (fe.feofs), -- OFFLINE_CHANGE# NUMBER
to_number -- ONLINE_CHANGE# NUMBER
(fe.feonc_scn),
to_date -- ONLINE_TIME DATE
(fe.feonc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fh.fhfsz*fe.febsz, -- BYTES NUMBER
fh.fhfsz, -- BLOCKS NUMBER
fe.fecsz*fe.febsz, -- CREATE_BYTES NUMBER
fe.febsz, -- BLOCK_SIZE NUMBER
fn.fnnam, -- NAME VARCHAR2(513)
fe.fefdb -- PLUGGED_IN NUMBER
from
x$kccfe fe,
x$kccfn fn,
x$kcvfh fh
where
fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil and fe.fefnh=fn.fnnum and fe.fedup!=0 and fn.fntyp=4 and fn.fnnam is not null
GV$DATAFILE_COPY
select
inst_id, -- INST_ID NUMBER
dcrid, -- RECID NUMBER
dcstm, -- STAMP NUMBER
dcnam, -- NAME VARCHAR2(513)
dctag, -- TAG VARCHAR2(32)
dcdfp, -- FILE# NUMBER
dcrfn, -- RFILE# NUMBER
to_number (dccrs), -- CREATION_CHANGE# NUMBER
to_date -- CREATION_TIME DATE
(dccrt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (dcrls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(dcrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- INCREMENTAL_LEVEL NUMBER
(bitand (dcflg, 8), 8, 0, NULL),
to_number (dccps), -- CHECKPOINT_CHANGE# NUMBER
to_date -- CHECKPOINT_TIME DATE
(dccpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (dcafs), -- ABSOLUTE_FUZZY_CHANGE# NUMBER
to_number (dcrfs), -- RECOVERY_FUZZY_CHANGE# NUMBER
to_date -- RECOVERY_FUZZY_TIME DATE
(dcrft, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- ONLINE_FUZZY VARCHAR2(3)
(bitand (dcflg, 2), 0, 'NO', 'YES'),
decode -- BACKUP_FUZZY VARCHAR2(3)
(bitand (dcflg, 4), 0, 'NO', 'YES'),
dcncb, -- MARKED_CORRUPT NUMBER
dcmcb, -- MEDIA_CORRUPT NUMBER
dclcb, -- LOGICALLY_CORRUPT NUMBER
dcbct, -- BLOCKS NUMBER
dcbsz, -- BLOCK_SIZE NUMBER
dclor, -- OLDEST_OFFLINE_RANGE NUMBER
decode -- DELETED VARCHAR2(3)
(bitand (dcflg, 1), 0, 'NO', 'YES'),
to_date -- COMPLETION_TIME DATE
(dctsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- CONTROLFILE_TYPE VARCHAR2(1)
(dcdfp, 0, decode (bitand (dcflg, 16), 16, 'S', 'B'), NULL)
from
x$kccdc
GV$DATAFILE_HEADER
select
inst_id, -- INST_ID NUMBER
hxfil, -- FILE# NUMBER
decode -- STATUS VARCHAR2(7)
(hxons, 0, 'OFFLINE', 'ONLINE'),
decode -- ERROR VARCHAR2(18)
(hxerr, 0, NULL, 1, 'FILE MISSING', 2, 'OFFLINE NORMAL', 3, 'NOT VERIFIED', 4, 'FILE NOT FOUND', 5, 'CANNOT OPEN FILE', 6, 'CANNOT READ HEADER', 7, 'CORRUPT HEADER', 8, 'WRONG FILE TYPE', 9, 'WRONG DATABASE', 10, 'WRONG FILE NUMBER', 11, 'WRONG FILE CREATE', 12, 'WRONG FILE CREATE', 16, 'DELAYED OPEN', 14, 'WRONG RESETLOGS', 15, 'OLD CONTROLFILE', 'UNKNOWN ERROR'),
hxver, -- FORMAT NUMBER
decode -- RECOVER VARCHAR2(3)
(hxnrcv, 0, 'NO', 1, 'YES', NULL),
decode -- FUZZY VARCHAR2(3)
(hxifz, 0, 'NO', 1, 'YES', NULL),
to_number (fhcrs), -- CREATION_CHANGE# NUMBER
to_date -- CREATION_TIME DATE
(fhcrt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fhtnm, -- TABLESPACE_NAME VARCHAR2(30)
fhtsn, -- TS# NUMBER
fhrfn, -- RFILE# NUMBER
to_number (fhrls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(fhrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (fhscn), -- CHECKPOINT_CHANGE# NUMBER
to_date -- CHECKPOINT_TIME DATE
(fhtim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
fhcpc, -- CHECKPOINT_COUNT NUMBER
fhfsz*fhbsz, -- BYTES NUMBER
fhfsz, -- BLOCKS NUMBER
hxfnm -- NAME VARCHAR2(513)
from
x$kcvfh
GV$DBFILE
select
inst_id, -- INST_ID NUMBER
fnfno, -- FILE# NUMBER
fnnam -- NAME VARCHAR2(513)
from
x$kccfn
where
fnnam is not null and fntyp=4
GV$DBLINK
select
inst_id, -- INST_ID NUMBER
nconam, -- DB_LINK VARCHAR2(128)
ncouid, -- OWNER_ID NUMBER
decode -- LOGGED_ON VARCHAR2(3)
(bitand (hstflg, 32), 0, 'NO', 'YES'),
decode -- HETEROGENEOUS VARCHAR2(3)
(bitand (hstflg, 8), 0, 'NO', 'YES'),
decode -- PROTOCOL VARCHAR2(6)
(hstpro, 1, 'V5', 2, 'V6', 3, 'V6_NLS', 4, 'V7', 'UNKN'),
ncouct, -- OPEN_CURSORS NUMBER
decode -- IN_TRANSACTION VARCHAR2(3)
(bitand (ncoflg, 2), 0, 'NO', 'YES'),
decode -- UPDATE_SENT VARCHAR2(3)
(bitand (ncoflg, 8), 0, 'NO', 'YES'),
nco2pstr -- COMMIT_POINT_STRENGTH NUMBER
from
x$uganco
where
bitand (hstflg, 1) != 0
GV$DB_CACHE_ADVICE
select
A.inst_id, -- INST_ID NUMBER
A.bpid, -- ID NUMBER
B.bp_name, -- NAME VARCHAR2(20)
A.poolsz, -- SIZE_FOR_ESTIMATE NUMBER
A.nbufs, -- BUFFERS_FOR_ESTIMATE NUMBER
decode -- ESTD_PHYSICAL_READ_FACTOR VARCHAR2(40)
(A.base_preads, 0, NULL, round ((A.preads / A.base_preads), 4)),
decode -- ESTD_PHYSICAL_READS NUMBER
(A.base_preads, 0, A.preads, round ((A.preads * (A.actual_preads / A.base_preads)), 0))
from
x$kcbsc A,
x$kcbwbpd B
where
A.bpid = B.bp_id and A.inst_id = B.inst_id
GV$DB_OBJECT_CACHE
select
inst_id, -- INST_ID NUMBER
kglnaown, -- OWNER VARCHAR2(64)
kglnaobj, -- NAME VARCHAR2(1000)
kglnadlk, -- DB_LINK VARCHAR2(64)
decode -- NAMESPACE VARCHAR2(28)
(kglhdnsp, 0, 'CURSOR', 1, 'TABLE/PROCEDURE', 2, 'BODY', 3, 'TRIGGER', 4, 'INDEX', 5, 'CLUSTER', 6, 'OBJECT', 13, 'JAVA SOURCE', 14, 'JAVA RESOURCE', 15, 'REPLICATED TABLE OBJECT', 16, 'REPLICATION INTERNAL PACKAGE', 17, 'CONTEXT POLICY', 18, 'PUB_SUB', 19, 'SUMMARY', 20, 'DIMENSION', 21, 'APP CONTEXT', 22, 'STORED OUTLINE', 23, 'RULESET', 24, 'RSRC PLAN', 25, 'RSRC CONSUMER GROUP', 26, 'PENDING RSRC PLAN', 27, 'PENDING RSRC CONSUMER GROUP', 28, 'SUBSCRIPTION', 29, 'LOCATION', 'INVALID NAMESPACE'),
decode -- TYPE VARCHAR2(28)
(bitand (kglobflg, 3), 0, 'NOT LOADED', 2, 'NON-EXISTENT', 3, 'INVALID STATUS', decode (kglobtyp, 0, 'CURSOR', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 15, 'OBJECT', 16, 'USER', 17, 'DBLINK', 18, 'PIPE', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 25, 'INDEX-ORGANIZED TABLE', 26, 'REPLICATION OBJECT GROUP', 27, 'REPLICATION PROPAGATOR', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 31, 'JAVA JAR', 32, 'INDEX TYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 36, 'REPLICATED TABLE OBJECT', 37, 'REPLICATION INTERNAL PACKAGE', 38, 'CONTEXT POLICY', 39, 'PUB_SUB', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'SUMMARY', 43, 'DIMENSION', 44, 'APP CONTEXT', 45, 'STORED OUTLINE', 46, 'RULESET', 47, 'RSRC PLAN', 48, 'RSRC CONSUMER GROUP', 49, 'PENDING RSRC PLAN', 50, 'PENDING RSRC CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 'INVALID TYPE')),
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6, -- SHARABLE_MEM NUMBER
kglhdldc, -- LOADS NUMBER
kglhdexc, -- EXECUTIONS NUMBER
kglhdlkc, -- LOCKS NUMBER
kglobpc0, -- PINS NUMBER
decode -- KEPT VARCHAR2(3)
(kglhdkmk, 0, 'NO', 'YES')
from
x$kglob
GV$DB_PIPES
select
inst_id, -- INST_ID NUMBER
decode -- OWNERID NUMBER
(kglobt00, 1, kglobt17, null),
kglnaobj, -- NAME VARCHAR2(1000)
decode -- TYPE VARCHAR2(7)
(kglobt00, 1, 'PRIVATE', 'PUBLIC'),
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6 -- PIPE_SIZE NUMBER
from
x$kglob
where
kglhdnsp=7 and kglobsta != 0
GV$DELETED_OBJECT
select
inst_id, -- INST_ID NUMBER
dlrid, -- RECID NUMBER
dlstm, -- STAMP NUMBER
decode -- TYPE VARCHAR2(22)
(dltyp, 11, 'ARCHIVED LOG', 13, 'BACKUP PIECE', 16, 'DATAFILE COPY', 20, 'PROXY COPY', 255, 'BACKUP PIECE AVAILABLE', 254, 'BACKUP PIECE EXPIRED', 253, 'PROXY COPY AVAILABLE', 252, 'PROXY COPY EXPIRED', 'UNKNOWN'),
dlobp, -- OBJECT_RECID NUMBER
dlosm -- OBJECT_STAMP NUMBER
from
x$kccdl
GV$DISPATCHER
select
inst_id, -- INST_ID NUMBER
kmmdinam, -- NAME VARCHAR2(4)
kmmdiadd, -- NETWORK VARCHAR2(128)
kmmdipro, -- PADDR RAW(4)
kmmdista, -- STATUS VARCHAR2(16)
decode -- ACCEPT VARCHAR2(3)
(kmmdiacc, 0, 'NO', 'YES'),
kmmdinmg, -- MESSAGES NUMBER
kmmdinmb, -- BYTES NUMBER
kmmdibrk, -- BREAKS NUMBER
kmmdinvo, -- OWNED NUMBER
kmmditnc, -- CREATED NUMBER
kmmdiidl, -- IDLE NUMBER
kmmdibsy, -- BUSY NUMBER
kmmdiler, -- LISTENER NUMBER
kmmdidci -- CONF_INDX NUMBER
from
x$kmmdi
where
kmmdiflg != 0
GV$DISPATCHER_RATE
select
inst_id, -- INST_ID NUMBER
kmmdinam, -- NAME VARCHAR2(4)
kmmdipro, -- PADDR RAW(4)
kmmdicrle, -- CUR_LOOP_RATE NUMBER
kmmdicre, -- CUR_EVENT_RATE NUMBER
kmmdicepl, -- CUR_EVENTS_PER_LOOP NUMBER
kmmdicrm, -- CUR_MSG_RATE NUMBER
kmmdicrus, -- CUR_SVR_BUF_RATE NUMBER
kmmdicrys, -- CUR_SVR_BYTE_RATE NUMBER
kmmdicyus, -- CUR_SVR_BYTE_PER_BUF NUMBER
kmmdicruc, -- CUR_CLT_BUF_RATE NUMBER
kmmdicryc, -- CUR_CLT_BYTE_RATE NUMBER
kmmdicyuc, -- CUR_CLT_BYTE_PER_BUF NUMBER
kmmdicru, -- CUR_BUF_RATE NUMBER
kmmdicry, -- CUR_BYTE_RATE NUMBER
kmmdicyu, -- CUR_BYTE_PER_BUF NUMBER
kmmdicic, -- CUR_IN_CONNECT_RATE NUMBER
kmmdicoc, -- CUR_OUT_CONNECT_RATE NUMBER
kmmdicrr, -- CUR_RECONNECT_RATE NUMBER
kmmdimrle, -- MAX_LOOP_RATE NUMBER
kmmdimre, -- MAX_EVENT_RATE NUMBER
kmmdimepl, -- MAX_EVENTS_PER_LOOP NUMBER
kmmdimrm, -- MAX_MSG_RATE NUMBER
kmmdimrus, -- MAX_SVR_BUF_RATE NUMBER
kmmdimrys, -- MAX_SVR_BYTE_RATE NUMBER
kmmdimyus, -- MAX_SVR_BYTE_PER_BUF NUMBER
kmmdimruc, -- MAX_CLT_BUF_RATE NUMBER
kmmdimryc, -- MAX_CLT_BYTE_RATE NUMBER
kmmdimyuc, -- MAX_CLT_BYTE_PER_BUF NUMBER
kmmdimru, -- MAX_BUF_RATE NUMBER
kmmdimry, -- MAX_BYTE_RATE NUMBER
kmmdimyu, -- MAX_BYTE_PER_BUF NUMBER
kmmdimic, -- MAX_IN_CONNECT_RATE NUMBER
kmmdimoc, -- MAX_OUT_CONNECT_RATE NUMBER
kmmdimrr, -- MAX_RECONNECT_RATE NUMBER
kmmdiarle, -- AVG_LOOP_RATE NUMBER
kmmdiare, -- AVG_EVENT_RATE NUMBER
kmmdiaepl, -- AVG_EVENTS_PER_LOOP NUMBER
kmmdiarm, -- AVG_MSG_RATE NUMBER
kmmdiarus, -- AVG_SVR_BUF_RATE NUMBER
kmmdiarys, -- AVG_SVR_BYTE_RATE NUMBER
kmmdiayus, -- AVG_SVR_BYTE_PER_BUF NUMBER
kmmdiaruc, -- AVG_CLT_BUF_RATE NUMBER
kmmdiaryc, -- AVG_CLT_BYTE_RATE NUMBER
kmmdiayuc, -- AVG_CLT_BYTE_PER_BUF NUMBER
kmmdiaru, -- AVG_BUF_RATE NUMBER
kmmdiary, -- AVG_BYTE_RATE NUMBER
kmmdiayu, -- AVG_BYTE_PER_BUF NUMBER
kmmdiaic, -- AVG_IN_CONNECT_RATE NUMBER
kmmdiaoc, -- AVG_OUT_CONNECT_RATE NUMBER
kmmdiarr, -- AVG_RECONNECT_RATE NUMBER
kmmdinrle, -- TTL_LOOPS NUMBER
kmmdinrm, -- TTL_MSG NUMBER
kmmdinrus, -- TTL_SVR_BUF NUMBER
kmmdinruc, -- TTL_CLT_BUF NUMBER
kmmdinru, -- TTL_BUF NUMBER
kmmdinic, -- TTL_IN_CONNECT NUMBER
kmmdinoc, -- TTL_OUT_CONNECT NUMBER
kmmdinrr, -- TTL_RECONNECT NUMBER
kmmdisrle, -- SCALE_LOOPS NUMBER
kmmdisrm, -- SCALE_MSG NUMBER
kmmdisrus, -- SCALE_SVR_BUF NUMBER
kmmdisruc, -- SCALE_CLT_BUF NUMBER
kmmdisru, -- SCALE_BUF NUMBER
kmmdisic, -- SCALE_IN_CONNECT NUMBER
kmmdisoc, -- SCALE_OUT_CONNECT NUMBER
kmmdisrr -- SCALE_RECONNECT NUMBER
from
x$kmmdi
where
kmmdiflg!=0
GV$DLM_ALL_LOCKS
select
inst_id, -- INST_ID NUMBER
kjilkftlkp, -- LOCKP RAW(4)
kjilkftgl, -- GRANT_LEVEL VARCHAR2(9)
kjilkftrl, -- REQUEST_LEVEL VARCHAR2(9)
kjilkftrn1, -- RESOURCE_NAME1 VARCHAR2(30)
kjilkftrn2, -- RESOURCE_NAME2 VARCHAR2(30)
kjilkftpid, -- PID NUMBER
kjilkftxid0, -- TRANSACTION_ID0 NUMBER
kjilkftxid1, -- TRANSACTION_ID1 NUMBER
kjilkftgid, -- GROUP_ID NUMBER
kjilkftoodd, -- OPEN_OPT_DEADLOCK NUMBER
kjilkftoopt, -- OPEN_OPT_PERSISTENT NUMBER
kjilkftoopo, -- OPEN_OPT_PROCESS_OWNED NUMBER
kjilkftoonxid, -- OPEN_OPT_NO_XID NUMBER
kjilkftcogv, -- CONVERT_OPT_GETVALUE NUMBER
kjilkftcopv, -- CONVERT_OPT_PUTVALUE NUMBER
kjilkftconv, -- CONVERT_OPT_NOVALUE NUMBER
kjilkftcodv, -- CONVERT_OPT_DUBVALUE NUMBER
kjilkftconq, -- CONVERT_OPT_NOQUEUE NUMBER
kjilkftcoep, -- CONVERT_OPT_EXPRESS NUMBER
kjilkftconddw, -- CONVERT_OPT_NODEADLOCKWAIT NUMBER
kjilkftconddb, -- CONVERT_OPT_NODEADLOCKBLOCK NUMBER
kjilkftwq, -- WHICH_QUEUE NUMBER
kjilkftls, -- LOCKSTATE VARCHAR2(64)
kjilkftaste0, -- AST_EVENT0 NUMBER
kjilkfton, -- OWNER_NODE NUMBER
kjilkftblked, -- BLOCKED NUMBER
kjilkftblker -- BLOCKER NUMBER
from
x$kjilkft
GV$DLM_CONVERT_LOCAL
select
inst_id, -- INST_ID NUMBER
kjicvtnam, -- CONVERT_TYPE VARCHAR2(64)
kjicvtalt, -- AVERAGE_CONVERT_TIME NUMBER
kjicvtalc -- CONVERT_COUNT NUMBER
from
x$kjicvt
GV$DLM_CONVERT_REMOTE
select
inst_id, -- INST_ID NUMBER
kjicvtnam, -- CONVERT_TYPE VARCHAR2(64)
kjicvtart, -- AVERAGE_CONVERT_TIME NUMBER
kjicvtarc -- CONVERT_COUNT NUMBER
from
x$kjicvt
GV$DLM_LATCH
select
USERENV ('Instance'), -- INST_ID NUMBER
addr, -- ADDR RAW(4)
latch#, -- LATCH# NUMBER
level#, -- LEVEL# NUMBER
name, -- NAME VARCHAR2(64)
gets, -- GETS NUMBER
misses, -- MISSES NUMBER
sleeps, -- SLEEPS NUMBER
immediate_gets, -- IMMEDIATE_GETS NUMBER
immediate_misses, -- IMMEDIATE_MISSES NUMBER
waiters_woken, -- WAITERS_WOKEN NUMBER
waits_holding_latch, -- WAITS_HOLDING_LATCH NUMBER
spin_gets, -- SPIN_GETS NUMBER
sleep1, -- SLEEP1 NUMBER
sleep2, -- SLEEP2 NUMBER
sleep3, -- SLEEP3 NUMBER
sleep4, -- SLEEP4 NUMBER
sleep5, -- SLEEP5 NUMBER
sleep6, -- SLEEP6 NUMBER
sleep7, -- SLEEP7 NUMBER
sleep8, -- SLEEP8 NUMBER
sleep9, -- SLEEP9 NUMBER
sleep10, -- SLEEP10 NUMBER
sleep11 -- SLEEP11 NUMBER
from
V$LATCH
where
NAME like 'dlm %'
GV$DLM_LOCKS
select
USERENV ('Instance'), -- INST_ID NUMBER
LOCKP, -- LOCKP RAW(4)
GRANT_LEVEL, -- GRANT_LEVEL VARCHAR2(9)
REQUEST_LEVEL, -- REQUEST_LEVEL VARCHAR2(9)
RESOURCE_NAME1, -- RESOURCE_NAME1 VARCHAR2(30)
RESOURCE_NAME2, -- RESOURCE_NAME2 VARCHAR2(30)
PID, -- PID NUMBER
TRANSACTION_ID0, -- TRANSACTION_ID0 NUMBER
TRANSACTION_ID1, -- TRANSACTION_ID1 NUMBER
GROUP_ID, -- GROUP_ID NUMBER
OPEN_OPT_DEADLOCK, -- OPEN_OPT_DEADLOCK NUMBER
OPEN_OPT_PERSISTENT, -- OPEN_OPT_PERSISTENT NUMBER
OPEN_OPT_PROCESS_OWNED, -- OPEN_OPT_PROCESS_OWNED NUMBER
OPEN_OPT_NO_XID, -- OPEN_OPT_NO_XID NUMBER
CONVERT_OPT_GETVALUE, -- CONVERT_OPT_GETVALUE NUMBER
CONVERT_OPT_PUTVALUE, -- CONVERT_OPT_PUTVALUE NUMBER
CONVERT_OPT_NOVALUE, -- CONVERT_OPT_NOVALUE NUMBER
CONVERT_OPT_DUBVALUE, -- CONVERT_OPT_DUBVALUE NUMBER
CONVERT_OPT_NOQUEUE, -- CONVERT_OPT_NOQUEUE NUMBER
CONVERT_OPT_EXPRESS, -- CONVERT_OPT_EXPRESS NUMBER
CONVERT_OPT_NODEADLOCKWAIT, -- CONVERT_OPT_NODEADLOCKWAIT NUMBER
CONVERT_OPT_NODEADLOCKBLOCK, -- CONVERT_OPT_NODEADLOCKBLOCK NUMBER
WHICH_QUEUE, -- WHICH_QUEUE NUMBER
LOCKSTATE, -- LOCKSTATE VARCHAR2(64)
AST_EVENT0, -- AST_EVENT0 NUMBER
OWNER_NODE, -- OWNER_NODE NUMBER
BLOCKED, -- BLOCKED NUMBER
BLOCKER -- BLOCKER NUMBER
from
V$DLM_ALL_LOCKS
where
(REQUEST_LEVEL != 'KJUSERNL') and (BLOCKED = 1 or BLOCKER = 1)
GV$DLM_MISC
select
inst_id, -- INST_ID NUMBER
indx, -- STATISTIC# NUMBER
kjisftdesc, -- NAME VARCHAR2(64)
kjisftval -- VALUE NUMBER
from
x$kjisft
GV$DLM_RESS
select
inst_id, -- INST_ID NUMBER
kjirftrp, -- RESP RAW(4)
kjirftrn, -- RESOURCE_NAME VARCHAR2(30)
kjirftcq, -- ON_CONVERT_Q NUMBER
kjirftgq, -- ON_GRANT_Q NUMBER
kjirftpr, -- PERSISTENT_RES NUMBER
kjirftrdn, -- RDOMAIN_NAME VARCHAR2(25)
kjirftrdp, -- RDOMAINP RAW(4)
kjirftmn, -- MASTER_NODE NUMBER
kjirftncl, -- NEXT_CVT_LEVEL VARCHAR2(9)
kjirftvs, -- VALUE_BLK_STATE VARCHAR2(32)
kjirftvb -- VALUE_BLK VARCHAR2(64)
from
x$kjirft
GV$DLM_TRAFFIC_CONTROLLER
select
inst_id, -- INST_ID NUMBER
kjitrftlid, -- LOCAL_NID NUMBER
kjitrftrid, -- REMOTE_NID NUMBER
kjitrftta, -- TCKT_AVAIL NUMBER
kjitrfttl, -- TCKT_LIMIT NUMBER
decode -- TCKT_WAIT VARCHAR2(10)
(kjitrfttw, 0, 'NO ', 'YES '),
kjitrftss, -- SND_SEQ_NO NUMBER
kjitrftsr, -- RCV_SEQ_NO NUMBER
kjitrftsa, -- ACK_SEQ_NO NUMBER
kjitrftsql, -- SND_Q_LEN NUMBER
kjitrftrql, -- RCV_Q_LEN NUMBER
kjitrfthwm, -- RCV_Q_HWM NUMBER
kjitrftpid, -- SND_L_HLDR NUMBER
kjitrftst -- STATUS NUMBER
from
x$kjitrft
GV$ENABLEDPRIVS
select
inst_id, -- INST_ID NUMBER
-indx -- PRIV_NUMBER NUMBER
from
x$kzspr
where
x$kzspr.kzsprprv=1
GV$ENQUEUE_LOCK
select
/*+ ordered use_nl (l), use_nl (s), use_nl (r) +*/
s.inst_id, -- INST_ID NUMBER
l.addr, -- ADDR RAW(4)
l.ksqlkadr, -- KADDR RAW(4)
s.ksusenum, -- SID NUMBER
r.ksqrsidt, -- TYPE VARCHAR2(2)
r.ksqrsid1, -- ID1 NUMBER
r.ksqrsid2, -- ID2 NUMBER
l.ksqlkmod, -- LMODE NUMBER
l.ksqlkreq, -- REQUEST NUMBER
l.ksqlkctim, -- CTIME NUMBER
l.ksqlklblk -- BLOCK NUMBER
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$EVENT_NAME
select
inst_id, -- INST_ID NUMBER
indx, -- EVENT# NUMBER
kslednam, -- NAME VARCHAR2(64)
ksledp1, -- PARAMETER1 VARCHAR2(64)
ksledp2, -- PARAMETER2 VARCHAR2(64)
ksledp3 -- PARAMETER3 VARCHAR2(64)
from
x$ksled
GV$EXECUTION
select
inst_id, -- INST_ID NUMBER
pid, -- PID NUMBER
val0, -- DEPTH NUMBER
func, -- FUNCTION VARCHAR2(10)
decode -- TYPE VARCHAR2(7)
(id, 1, 'call', 2, 'return', 3, 'longjmp'),
nvals, -- NVALS NUMBER
val2, -- VAL1 NUMBER
val3, -- VAL2 NUMBER
seqh, -- SEQH NUMBER
seql -- SEQL NUMBER
from
x$kstex
where
op=10
GV$FAST_START_SERVERS
SELECT
inst_id, -- INST_ID NUMBER
state, -- STATE VARCHAR2(11)
wdone, -- UNDOBLOCKSDONE NUMBER
pid -- PID NUMBER
from
x$ktprxrs
GV$FAST_START_TRANSACTIONS
SELECT
inst_id, -- INST_ID NUMBER
usn, -- USN NUMBER
slt, -- SLT NUMBER
seq, -- SEQ NUMBER
state, -- STATE VARCHAR2(16)
wkd, -- UNDOBLOCKSDONE NUMBER
twk, -- UNDOBLOCKSTOTAL NUMBER
pid, -- PID NUMBER
etime, -- CPUTIME NUMBER
parentusn, -- PARENTUSN NUMBER
parentslt, -- PARENTSLT NUMBER
parentseq -- PARENTSEQ NUMBER
from
x$ktprxrt
GV$FILESTAT
select
k.inst_id, -- INST_ID NUMBER
k.kcfiofno, -- FILE# NUMBER
k.kcfiopyr, -- PHYRDS NUMBER
k.kcfiopyw, -- PHYWRTS NUMBER
k.kcfiopbr, -- PHYBLKRD NUMBER
k.kcfiopbw, -- PHYBLKWRT NUMBER
k.kcfioprt, -- READTIM NUMBER
k.kcfiopwt, -- WRITETIM NUMBER
k.kcfioavg, -- AVGIOTIM NUMBER
k.kcfiolst, -- LSTIOTIM NUMBER
k.kcfiomin, -- MINIOTIM NUMBER
k.kcfiormx, -- MAXIORTM NUMBER
k.kcfiowmx -- MAXIOWTM NUMBER
from
x$kcfio k,
x$kccfe f
where
f.fedup <> 0 and f.fenum=k.kcfiofno
GV$FILE_PING
select
x.inst_id, -- INST_ID NUMBER
kcfiofno, -- FILE_NUMBER NUMBER
0, -- FREQUENCY NUMBER
KCFIOX2NC, -- X_2_NULL NUMBER
KCFIOX2NFWC, -- X_2_NULL_FORCED_WRITE NUMBER
KCFIOX2NFSC, -- X_2_NULL_FORCED_STALE NUMBER
KCFIOX2SC, -- X_2_S NUMBER
KCFIOX2SFWC, -- X_2_S_FORCED_WRITE NUMBER
0, -- X_2_SSX NUMBER
0, -- X_2_SSX_FORCED_WRITE NUMBER
KCFIOS2NC, -- S_2_NULL NUMBER
KCFIOS2NFSC, -- S_2_NULL_FORCED_STALE NUMBER
0, -- SS_2_NULL NUMBER
0, -- SS_2_RLS NUMBER
0, -- WRB NUMBER
0, -- WRB_FORCED_WRITE NUMBER
KCFIORBRC, -- RBR NUMBER
KCFIORBRFWC, -- RBR_FORCED_WRITE NUMBER
KCFIORBRFSC, -- RBR_FORCED_STALE NUMBER
0, -- CBR NUMBER
0, -- CBR_FORCED_WRITE NUMBER
KCFION2XC, -- NULL_2_X NUMBER
KCFIOS2XC, -- S_2_X NUMBER
0, -- SSX_2_X NUMBER
KCFION2SC, -- NULL_2_S NUMBER
0, -- NULL_2_SS NUMBER
0 -- OP_2_SS NUMBER
from
x$kcfio x,
x$kccfe fe
where
x.kcfiofno = fe.fenum
GV$FIXED_TABLE
select
inst_id, -- INST_ID NUMBER
kqftanam, -- NAME VARCHAR2(30)
kqftaobj, -- OBJECT_ID NUMBER
'TABLE', -- TYPE VARCHAR2(5)
indx -- TABLE_NUM NUMBER
from
x$kqfta
union all
select
inst_id, -- INST_ID NUMBER
kqfvinam, -- NAME VARCHAR2(30)
kqfviobj, -- OBJECT_ID NUMBER
'VIEW', -- TYPE VARCHAR2(5)
65537 -- TABLE_NUM NUMBER
from
x$kqfvi
union all
select
inst_id, -- INST_ID NUMBER
kqfdtnam, -- NAME VARCHAR2(30)
kqfdtobj, -- OBJECT_ID NUMBER
'TABLE', -- TYPE VARCHAR2(5)
65537 -- TABLE_NUM NUMBER
from
x$kqfdt
GV$FIXED_VIEW_DEFINITION
select
i.inst_id, -- INST_ID NUMBER
kqfvinam, -- VIEW_NAME VARCHAR2(30)
kqftpsel -- VIEW_DEFINITION VARCHAR2(4000)
from
x$kqfvi i,
x$kqfvt t
where
i.indx = t.indx
GV$GLOBAL_BLOCKED_LOCKS
select
USERENV ('instance'), -- INST_ID NUMBER
addr, -- ADDR RAW(4)
kaddr, -- KADDR RAW(4)
sid, -- SID NUMBER
type, -- TYPE VARCHAR2(2)
id1, -- ID1 NUMBER
id2, -- ID2 NUMBER
lmode, -- LMODE NUMBER
request, -- REQUEST NUMBER
ctime -- CTIME NUMBER
from
v$lock l
where
exists (
select
*
from
v$dlm_locks d
where
substr (d.resource_name2, 1, instr (d.resource_name2, ', ', 1, 1) -1) = id1 and substr (d.resource_name2, instr (d.resource_name2, ', ', 1, 1) +1, instr (d.resource_name2, ', ', 1, 2) -instr (d.resource_name2, ', ', 1, 1) -1) = id2 and substr (d.resource_name2, instr (d.resource_name2, ', ', -1, 1) +1, 2) = type)
GV$GLOBAL_TRANSACTION
select
inst_id, -- INST_ID NUMBER
K2GTIFMT, -- FORMATID NUMBER
K2GTITID_EXT, -- GLOBALID RAW(64)
K2GTIBID, -- BRANCHID RAW(64)
K2GTECNT, -- BRANCHES NUMBER
K2GTERCT, -- REFCOUNT NUMBER
K2GTDPCT, -- PREPARECOUNT NUMBER
decode -- STATE VARCHAR2(18)
(K2GTDFLG, 0, 'ACTIVE', 1, 'COLLECTING', 2, 'FINALIZED', 4, 'FAILED', 8, 'RECOVERING', 16, 'UNASSOCIATED', 32, 'FORGOTTEN', 64, 'READY FOR RECOVERY', 'COMBINATION'),
K2GTDFLG, -- FLAGS NUMBER
decode -- COUPLING VARCHAR2(15)
(K2GTETYP, 0, 'FREE', 1, 'LOOSELY COUPLED', 2, 'TIGHTLY COUPLED')
from
X$K2GTE2
GV$HS_AGENT
select
unique INST_ID, -- INST_ID NUMBER
AGENT_ID, -- AGENT_ID NUMBER
MACHINE, -- MACHINE VARCHAR2(64)
PROCESS, -- PROCESS VARCHAR2(9)
PROGRAM, -- PROGRAM VARCHAR2(48)
OSUSER, -- OSUSER VARCHAR2(30)
AGT_STARTTIME, -- STARTTIME DATE
AGENT_TYPE, -- AGENT_TYPE NUMBER
decode -- FDS_CLASS_ID NUMBER
(AGENT_TYPE, 1, to_number (NULL), FDS_CLASS_ID),
decode -- FDS_INST_ID NUMBER
(AGENT_TYPE, 1, to_number (NULL), FDS_INST_ID)
from
X$HS_SESSION
GV$HS_PARAMETER
select
A.INST_ID, -- INST_ID NUMBER
HS_SESSION_ID, -- HS_SESSION_ID NUMBER
PARAMETER, -- PARAMETER VARCHAR2(30)
VALUE, -- VALUE VARCHAR2(64)
SOURCE, -- SOURCE VARCHAR2(1)
ENV -- ENV VARCHAR2(1)
from
X$HS_SESSION A,
X$HOFP B
WHERE
A.FDS_INST_ID = B.FDS_INST_ID
GV$HS_SESSION
select
INST_ID, -- INST_ID NUMBER
HS_SESSION_ID, -- HS_SESSION_ID NUMBER
AGENT_ID, -- AGENT_ID NUMBER
SID, -- SID NUMBER
decode -- DB_LINK VARCHAR2(128)
(AGENT_TYPE, 1, NULL, DB_LINK),
decode -- DB_LINK_OWNER NUMBER
(AGENT_TYPE, 1, to_number (NULL), DB_LINK_OWNER),
SES_STARTTIME -- STARTTIME DATE
from
X$HS_SESSION
GV$INDEXED_FIXED_COLUMN
select
c.inst_id, -- INST_ID NUMBER
kqftanam, -- TABLE_NAME VARCHAR2(30)
kqfcoidx, -- INDEX_NUMBER NUMBER
kqfconam, -- COLUMN_NAME VARCHAR2(30)
kqfcoipo -- COLUMN_POSITION NUMBER
from
x$kqfco c,
x$kqfta t
where
t.indx = c.kqfcotab and kqfcoidx != 0
GV$INSTANCE
select
ks.inst_id, -- INST_ID NUMBER
ksuxsins, -- INSTANCE_NUMBER NUMBER
ksuxssid, -- INSTANCE_NAME VARCHAR2(16)
ksuxshst, -- HOST_NAME VARCHAR2(64)
ksuxsver, -- VERSION VARCHAR2(17)
ksuxstim, -- STARTUP_TIME DATE
decode -- STATUS VARCHAR2(7)
(ksuxssts, 0, 'STARTED', 1, 'MOUNTED', 2, 'OPEN', 'UNKNOWN'),
decode -- PARALLEL VARCHAR2(3)
(ksuxsshr, 0, 'NO', 1, 'YES', 2, NULL),
ksuxsthr, -- THREAD# NUMBER
decode -- ARCHIVER VARCHAR2(7)
(ksuxsarc, 0, 'STOPPED', 1, 'STARTED', 'FAILED'),
decode -- LOG_SWITCH_WAIT VARCHAR2(11)
(ksuxslsw, 0, NULL, 2, 'ARCHIVE LOG', 3, 'CLEAR LOG', 4, 'CHECKPOINT'),
decode -- LOGINS VARCHAR2(10)
(ksuxsdba, 0, 'ALLOWED', 'RESTRICTED'),
decode -- SHUTDOWN_PENDING VARCHAR2(3)
(ksuxsshp, 0, 'NO', 'YES'),
decode -- DATABASE_STATUS VARCHAR2(17)
(kvitval, 0, 'ACTIVE', 2147483647, 'SUSPENDED', 'INSTANCE RECOVERY'),
decode -- INSTANCE_ROLE VARCHAR2(18)
(ksuxsrol, 1, 'PRIMARY_INSTANCE', 2, 'SECONDARY_INSTANCE', 'UNKNOWN')
from
x$ksuxsinst ks,
x$kvit kv
where
kvittag = 'kcbwst'
GV$INSTANCE_RECOVERY
select
INST_ID, -- INST_ID NUMBER
CUR_EST_RCV_READS, -- RECOVERY_ESTIMATED_IOS NUMBER
ACTUAL_REDO_BLKS, -- ACTUAL_REDO_BLKS NUMBER
MIN_LAG, -- TARGET_REDO_BLKS NUMBER
LOGFILESZ, -- LOG_FILE_SIZE_REDO_BLKS NUMBER
CT_LAG, -- LOG_CHKPT_TIMEOUT_REDO_BLKS NUMBER
CI_LAG, -- LOG_CHKPT_INTERVAL_REDO_BLKS NUMBER
FS_LAG -- FAST_START_IO_TARGET_REDO_BLKS NUMBER
from
X$TARGETRBA
GV$LATCH
select
d.inst_id, -- INST_ID NUMBER
d.kslldadr, -- ADDR RAW(4)
la.latch#, -- LATCH# NUMBER
d.kslldlvl, -- LEVEL# NUMBER
d.kslldnam, -- NAME VARCHAR2(64)
la.gets, -- GETS NUMBER
la.misses, -- MISSES NUMBER
la.sleeps, -- SLEEPS NUMBER
la.immediate_gets, -- IMMEDIATE_GETS NUMBER
la.immediate_misses, -- IMMEDIATE_MISSES NUMBER
la.waiters_woken, -- WAITERS_WOKEN NUMBER
la.waits_holding_latch, -- WAITS_HOLDING_LATCH NUMBER
la.spin_gets, -- SPIN_GETS NUMBER
la.sleep1, -- SLEEP1 NUMBER
la.sleep2, -- SLEEP2 NUMBER
la.sleep3, -- SLEEP3 NUMBER
la.sleep4, -- SLEEP4 NUMBER
la.sleep5, -- SLEEP5 NUMBER
la.sleep6, -- SLEEP6 NUMBER
la.sleep7, -- SLEEP7 NUMBER
la.sleep8, -- SLEEP8 NUMBER
la.sleep9, -- SLEEP9 NUMBER
la.sleep10, -- SLEEP10 NUMBER
la.sleep11 -- SLEEP11 NUMBER
from
x$kslld d,
(
select
kslltnum latch#,
sum (kslltwgt) gets,
sum (kslltwff) misses,
sum (kslltwsl) sleeps,
sum (kslltngt) immediate_gets,
sum (kslltnfa) immediate_misses,
sum (kslltwkc) waiters_woken,
sum (kslltwth) waits_holding_latch,
sum (ksllthst0) spin_gets,
sum (ksllthst1) sleep1,
sum (ksllthst2) sleep2,
sum (ksllthst3) sleep3,
sum (ksllthst4) sleep4,
sum (ksllthst5) sleep5,
sum (ksllthst6) sleep6,
sum (ksllthst7) sleep7,
sum (ksllthst8) sleep8,
sum (ksllthst9) sleep9,
sum (ksllthst10) sleep10,
sum (ksllthst11) sleep11
from
x$ksllt
group by kslltnum) la
where
la.latch# = d.indx
GV$LATCHHOLDER
select
inst_id, -- INST_ID NUMBER
ksuprpid, -- PID NUMBER
ksuprsid, -- SID NUMBER
ksuprlat, -- LADDR RAW(4)
ksuprlnm -- NAME VARCHAR2(64)
from
x$ksuprlat
GV$LATCHNAME
select
inst_id, -- INST_ID NUMBER
indx, -- LATCH# NUMBER
kslldnam -- NAME VARCHAR2(64)
from
x$kslld
GV$LATCH_CHILDREN
select
t.inst_id, -- INST_ID NUMBER
t.addr, -- ADDR RAW(4)
t.kslltnum, -- LATCH# NUMBER
t.kslltcnm, -- CHILD# NUMBER
n.kslldlvl, -- LEVEL# NUMBER
n.kslldnam, -- NAME VARCHAR2(64)
t.kslltwgt, -- GETS NUMBER
t.kslltwff, -- MISSES NUMBER
t.kslltwsl, -- SLEEPS NUMBER
t.kslltngt, -- IMMEDIATE_GETS NUMBER
t.kslltnfa, -- IMMEDIATE_MISSES NUMBER
t.kslltwkc, -- WAITERS_WOKEN NUMBER
t.kslltwth, -- WAITS_HOLDING_LATCH NUMBER
t.ksllthst0, -- SPIN_GETS NUMBER
t.ksllthst1, -- SLEEP1 NUMBER
t.ksllthst2, -- SLEEP2 NUMBER
t.ksllthst3, -- SLEEP3 NUMBER
t.ksllthst4, -- SLEEP4 NUMBER
t.ksllthst5, -- SLEEP5 NUMBER
t.ksllthst6, -- SLEEP6 NUMBER
t.ksllthst7, -- SLEEP7 NUMBER
t.ksllthst8, -- SLEEP8 NUMBER
t.ksllthst9, -- SLEEP9 NUMBER
t.ksllthst10, -- SLEEP10 NUMBER
t.ksllthst11 -- SLEEP11 NUMBER
from
x$ksllt t,
x$kslld n
where
t.kslltcnm > 0 and t.kslltnum = n.indx
GV$LATCH_MISSES
select
t1.inst_id, -- INST_ID NUMBER
t1.ksllasnam, -- PARENT_NAME VARCHAR2(50)
t2.ksllwnam, -- WHERE VARCHAR2(64)
t1.kslnowtf, -- NWFAIL_COUNT NUMBER
t1.kslsleep, -- SLEEP_COUNT NUMBER
t1.kslwscwsl, -- WTR_SLP_COUNT NUMBER
t1.kslwsclthg -- LONGHOLD_COUNT NUMBER
from
x$ksllw t2,
x$kslwsc t1
where
t2.indx = t1.indx
GV$LATCH_PARENT
select
t.inst_id, -- INST_ID NUMBER
t.addr, -- ADDR RAW(4)
t.kslltnum, -- LATCH# NUMBER
n.kslldlvl, -- LEVEL# NUMBER
n.kslldnam, -- NAME VARCHAR2(64)
t.kslltwgt, -- GETS NUMBER
t.kslltwff, -- MISSES NUMBER
t.kslltwsl, -- SLEEPS NUMBER
t.kslltngt, -- IMMEDIATE_GETS NUMBER
t.kslltnfa, -- IMMEDIATE_MISSES NUMBER
t.kslltwkc, -- WAITERS_WOKEN NUMBER
t.kslltwth, -- WAITS_HOLDING_LATCH NUMBER
t.ksllthst0, -- SPIN_GETS NUMBER
t.ksllthst1, -- SLEEP1 NUMBER
t.ksllthst2, -- SLEEP2 NUMBER
t.ksllthst3, -- SLEEP3 NUMBER
t.ksllthst4, -- SLEEP4 NUMBER
t.ksllthst5, -- SLEEP5 NUMBER
t.ksllthst6, -- SLEEP6 NUMBER
t.ksllthst7, -- SLEEP7 NUMBER
t.ksllthst8, -- SLEEP8 NUMBER
t.ksllthst9, -- SLEEP9 NUMBER
t.ksllthst10, -- SLEEP10 NUMBER
t.ksllthst11 -- SLEEP11 NUMBER
from
x$ksllt t,
x$kslld n
where
t.kslltcnm = 0 and t.kslltnum = n.indx
GV$LIBRARYCACHE
select
inst_id, -- INST_ID NUMBER
decode -- NAMESPACE VARCHAR2(15)
(indx, 0, 'SQL AREA', 1, 'TABLE/PROCEDURE', 2, 'BODY', 3, 'TRIGGER', 4, 'INDEX', 5, 'CLUSTER', 6, 'OBJECT', 7, 'PIPE', '?'),
kglstget, -- GETS NUMBER
kglstght, -- GETHITS NUMBER
decode -- GETHITRATIO NUMBER
(kglstget, 0, 1, kglstght/kglstget),
kglstpin, -- PINS NUMBER
kglstpht, -- PINHITS NUMBER
decode -- PINHITRATIO NUMBER
(kglstpin, 0, 1, kglstpht/kglstpin),
kglstrld, -- RELOADS NUMBER
kglstinv, -- INVALIDATIONS NUMBER
kglstlrq, -- DLM_LOCK_REQUESTS NUMBER
kglstprq, -- DLM_PIN_REQUESTS NUMBER
kglstprl, -- DLM_PIN_RELEASES NUMBER
kglstirq, -- DLM_INVALIDATION_REQUESTS NUMBER
kglstmiv -- DLM_INVALIDATIONS NUMBER
from
x$kglst
where
indx<8
GV$LICENSE
select
inst_id, -- INST_ID NUMBER
ksullms, -- SESSIONS_MAX NUMBER
ksullws, -- SESSIONS_WARNING NUMBER
ksullcs, -- SESSIONS_CURRENT NUMBER
ksullhs, -- SESSIONS_HIGHWATER NUMBER
ksullmu -- USERS_MAX NUMBER
from
x$ksull
GV$LOADCSTAT
select
inst_id, -- INST_ID NUMBER
kllcntnrd, -- READ NUMBER
kllcntnrj, -- REJECTED NUMBER
kllcnttds, -- TDISCARD NUMBER
kllcntnds -- NDISCARD NUMBER
from
x$kllcnt
GV$LOADISTAT
select
inst_id, -- INST_ID NUMBER
klcieon, -- OWNER VARCHAR2(31)
klcietn, -- TABNAME VARCHAR2(31)
klciein, -- INDEXNAME VARCHAR2(31)
klcieisn, -- SUBNAME VARCHAR2(31)
klciemsg -- MESSAGE VARCHAR2(4000)
from
x$klcie
GV$LOADPSTAT
select
inst_id, -- INST_ID NUMBER
klcpxon, -- OWNER VARCHAR2(31)
klcpxtn, -- TABNAME VARCHAR2(31)
klcpxpn, -- PARTNAME VARCHAR2(31)
klcpxrld -- LOADED NUMBER
from
x$klpt
GV$LOADTSTAT
select
inst_id, -- INST_ID NUMBER
klltabnld, -- LOADED NUMBER
klltabnrj, -- REJECTED NUMBER
klltabnfw, -- FAILWHEN NUMBER
klltabnan, -- ALLNULL NUMBER
klltabnls, -- LEFT2SKIP NUMBER
klltabpld -- PTNLOADED NUMBER
from
x$klltab
GV$LOCK
select
/*+ ordered use_nl (l), use_nl (s), use_nl (r) +*/
s.inst_id, -- INST_ID NUMBER
l.laddr, -- ADDR RAW(4)
l.kaddr, -- KADDR RAW(4)
s.ksusenum, -- SID NUMBER
r.ksqrsidt, -- TYPE VARCHAR2(2)
r.ksqrsid1, -- ID1 NUMBER
r.ksqrsid2, -- ID2 NUMBER
l.lmode, -- LMODE NUMBER
l.request, -- REQUEST NUMBER
l.ctime, -- CTIME NUMBER
l.block -- BLOCK NUMBER
from
v$_lock l,
x$ksuse s,
x$ksqrs r
where
l.saddr=s.addr and l.raddr=r.addr
GV$LOCKED_OBJECT
select
x.inst_id, -- INST_ID NUMBER
x.kxidusn, -- XIDUSN NUMBER
x.kxidslt, -- XIDSLOT NUMBER
x.kxidsqn, -- XIDSQN NUMBER
l.ktadmtab, -- OBJECT_ID NUMBER
s.indx, -- SESSION_ID NUMBER
s.ksuudlna, -- ORACLE_USERNAME VARCHAR2(30)
s.ksuseunm, -- OS_USER_NAME VARCHAR2(30)
s.ksusepid, -- PROCESS VARCHAR2(9)
l.ksqlkmod -- LOCKED_MODE NUMBER
from
x$ktcxb x,
x$ktadm l,
x$ksuse s
where
x.ktcxbxba = l.kssobown and x.ktcxbses = s.addr
GV$LOCKS_WITH_COLLISIONS
select
inst_id, -- INST_ID NUMBER
lock_element_addr -- LOCK_ELEMENT_ADDR RAW(4)
from
gv$bh
where
(forced_writes + forced_reads) > 10
group by lock_element_addr,
inst_id
having count (*) >= 2
GV$LOCK_ACTIVITY
select
inst_id, -- INST_ID NUMBER
decode -- FROM_VAL VARCHAR2(4)
(indx, 0, 'NULL', 1, 'NULL', 2, 'S', 3, 'S', 4, 'X', 5, 'X', '?'),
decode -- TO_VAL VARCHAR2(4)
(indx, 0, 'S', 1, 'X', 2, 'NULL', 3, 'X', 4, 'NULL', 5, 'S', '?'),
decode -- ACTION_VAL VARCHAR2(50)
(indx, 0, 'Lock buffers for read', 1, 'Lock buffers for write', 2, 'Make buffers CR (no write) ', 3, 'Upgrade read lock to write', 4, 'Make buffers CR (write dirty buffers) ', 5, 'Downgrade write lock to read (write dirty buffers) ', 'Should not happen'),
conv -- COUNTER NUMBER
from
x$le_stat
where
conv > 0
GV$LOCK_ELEMENT
select
inst_id, -- INST_ID NUMBER
le_addr, -- LOCK_ELEMENT_ADDR RAW(4)
indx, -- INDX NUMBER
le_class, -- CLASS NUMBER
name, -- LOCK_ELEMENT_NAME NUMBER
le_mode, -- MODE_HELD NUMBER
le_blks, -- BLOCK_COUNT NUMBER
le_rls, -- RELEASING NUMBER
le_acq, -- ACQUIRING NUMBER
le_inv, -- INVALID NUMBER
le_flags -- FLAGS NUMBER
from
x$le
GV$LOG
select
le.inst_id, -- INST_ID NUMBER
le.lenum, -- GROUP# NUMBER
le.lethr, -- THREAD# NUMBER
le.leseq, -- SEQUENCE# NUMBER
le.lesiz*le.lebsz, -- BYTES NUMBER
ledup, -- MEMBERS NUMBER
decode -- ARCHIVED VARCHAR2(3)
(bitand (le.leflg, 1), 0, 'NO', 'YES'),
decode -- STATUS VARCHAR2(16)
(bitand (le.leflg, 24), 8, 'CURRENT', 16, 'CLEARING', 24, 'CLEARING_CURRENT', decode (sign (leseq), 0, 'UNUSED', decode (sign ((to_number (rt.rtckp_scn) -to_number (le.lenxs)) * bitand (rt.rtsta, 2)), -1, 'ACTIVE', 'INACTIVE'))),
to_number (le.lelos), -- FIRST_CHANGE# NUMBER
to_date -- FIRST_TIME DATE
(le.lelot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kccle le,
x$kccrt rt
where
le.ledup!=0 and le.lethr=rt.rtnum and le.inst_id = rt.inst_id
GV$LOGFILE
select
inst_id, -- INST_ID NUMBER
fnfno, -- GROUP# NUMBER
decode -- STATUS VARCHAR2(7)
(fnflg, 0, '', 1, 'INVALID', 2, 'STALE', 4, 'DELETED', 'UNKNOWN'),
fnnam -- MEMBER VARCHAR2(513)
from
x$kccfn
where
fnnam is not null and fntyp=3
GV$LOGHIST
select
inst_id, -- INST_ID NUMBER
lhthp, -- THREAD# NUMBER
lhseq, -- SEQUENCE# NUMBER
to_number (lhlos), -- FIRST_CHANGE# NUMBER
to_date -- FIRST_TIME DATE
(lhlot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (lhnxs) -- SWITCH_CHANGE# NUMBER
from
x$kcclh
GV$LOGMNR_CONTENTS
select
INST_ID, -- INST_ID NUMBER
SCN, -- SCN NUMBER
TIMESTAMP, -- TIMESTAMP DATE
THREAD#, -- THREAD# NUMBER
LOG_ID, -- LOG_ID NUMBER
XIDUSN, -- XIDUSN NUMBER
XIDSLT, -- XIDSLT NUMBER
XIDSQN, -- XIDSQN NUMBER
RBASQN, -- RBASQN NUMBER
RBABLK, -- RBABLK NUMBER
RBABYTE, -- RBABYTE NUMBER
UBAFIL, -- UBAFIL NUMBER
UBABLK, -- UBABLK NUMBER
UBAREC, -- UBAREC NUMBER
UBASQN, -- UBASQN NUMBER
ABS_FILE#, -- ABS_FILE# NUMBER
REL_FILE#, -- REL_FILE# NUMBER
DATA_BLK#, -- DATA_BLK# NUMBER
DATA_OBJ#, -- DATA_OBJ# NUMBER
DATA_OBJD#, -- DATA_OBJD# NUMBER
SEG_OWNER, -- SEG_OWNER VARCHAR2(32)
SEG_NAME, -- SEG_NAME VARCHAR2(32)
SEG_TYPE, -- SEG_TYPE NUMBER
SEG_TYPE_NAME, -- SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE, -- TABLE_SPACE VARCHAR2(32)
ROW_ID, -- ROW_ID VARCHAR2(19)
SESSION#, -- SESSION# NUMBER
SERIAL#, -- SERIAL# NUMBER
USERNAME, -- USERNAME VARCHAR2(32)
SESSION_INFO, -- SESSION_INFO VARCHAR2(4000)
ROLLBACK, -- ROLLBACK NUMBER
OPERATION, -- OPERATION VARCHAR2(32)
SQL_REDO, -- SQL_REDO VARCHAR2(4000)
SQL_UNDO, -- SQL_UNDO VARCHAR2(4000)
RS_ID, -- RS_ID VARCHAR2(32)
SSN, -- SSN NUMBER
CSF, -- CSF NUMBER
INFO, -- INFO VARCHAR2(32)
STATUS, -- STATUS NUMBER
PH1_NAME, -- PH1_NAME VARCHAR2(32)
PH1_REDO, -- PH1_REDO VARCHAR2(4000)
PH1_UNDO, -- PH1_UNDO VARCHAR2(4000)
PH2_NAME, -- PH2_NAME VARCHAR2(32)
PH2_REDO, -- PH2_REDO VARCHAR2(4000)
PH2_UNDO, -- PH2_UNDO VARCHAR2(4000)
PH3_NAME, -- PH3_NAME VARCHAR2(32)
PH3_REDO, -- PH3_REDO VARCHAR2(4000)
PH3_UNDO, -- PH3_UNDO VARCHAR2(4000)
PH4_NAME, -- PH4_NAME VARCHAR2(32)
PH4_REDO, -- PH4_REDO VARCHAR2(4000)
PH4_UNDO, -- PH4_UNDO VARCHAR2(4000)
PH5_NAME, -- PH5_NAME VARCHAR2(32)
PH5_REDO, -- PH5_REDO VARCHAR2(4000)
PH5_UNDO -- PH5_UNDO VARCHAR2(4000)
from
x$logmnr_contents
where
ROW_TYPE = 0
GV$LOGMNR_DICTIONARY
select
INST_ID, -- INST_ID NUMBER
TIMESTAMP, -- TIMESTAMP DATE
DB_ID, -- DB_ID NUMBER
DB_NAME, -- DB_NAME VARCHAR2(8)
FILENAME, -- FILENAME VARCHAR2(513)
DICTIONARY_SCN, -- DICTIONARY_SCN NUMBER
RESET_SCN, -- RESET_SCN NUMBER
RESET_SCN_TIME, -- RESET_SCN_TIME DATE
ENABLED_THREAD_MAP, -- ENABLED_THREAD_MAP RAW(16)
INFO, -- INFO VARCHAR2(32)
STATUS -- STATUS NUMBER
from
x$logmnr_dictionary
GV$LOGMNR_LOGS
select
INST_ID, -- INST_ID NUMBER
LOG_ID, -- LOG_ID NUMBER
FILENAME, -- FILENAME VARCHAR2(513)
LOW_TIME, -- LOW_TIME DATE
HIGH_TIME, -- HIGH_TIME DATE
DB_ID, -- DB_ID NUMBER
DB_NAME, -- DB_NAME VARCHAR2(8)
RESET_SCNBAS, -- RESET_SCN NUMBER
RESET_SCN_TIME, -- RESET_SCN_TIME DATE
THREAD_ID, -- THREAD_ID NUMBER
THREAD_SQN, -- THREAD_SQN NUMBER
LOW_SCNBAS, -- LOW_SCN NUMBER
NEXT_SCNBAS, -- NEXT_SCN NUMBER
INFO, -- INFO VARCHAR2(32)
STATUS -- STATUS NUMBER
from
x$logmnr_logs
GV$LOGMNR_PARAMETERS
select
INST_ID, -- INST_ID NUMBER
START_DATE, -- START_DATE DATE
END_DATE, -- END_DATE DATE
START_SCN, -- START_SCN NUMBER
END_SCN, -- END_SCN NUMBER
INFO, -- INFO VARCHAR2(32)
STATUS -- STATUS NUMBER
from
x$logmnr_parameters
GV$LOG_HISTORY
select
inst_id, -- INST_ID NUMBER
lhrid, -- RECID NUMBER
lhstm, -- STAMP NUMBER
lhthp, -- THREAD# NUMBER
lhseq, -- SEQUENCE# NUMBER
to_number (lhlos), -- FIRST_CHANGE# NUMBER
to_date -- FIRST_TIME DATE
(lhlot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (lhnxs) -- NEXT_CHANGE# NUMBER
from
x$kcclh
GV$MAX_ACTIVE_SESS_TARGET_MTH
select
inst_id, -- INST_ID NUMBER
policy_name_kgskasp -- NAME VARCHAR2(40)
from
x$kgskasp
GV$MTS
select
inst_id, -- INST_ID NUMBER
kmmsgcmx, -- MAXIMUM_CONNECTIONS NUMBER
kmmsgmmx, -- MAXIMUM_SESSIONS NUMBER
kmmsgsta+kmmsgutr, -- SERVERS_STARTED NUMBER
kmmsgtrm, -- SERVERS_TERMINATED NUMBER
kmmsgsmx -- SERVERS_HIGHWATER NUMBER
from
x$kmmsg
GV$MYSTAT
select
inst_id, -- INST_ID NUMBER
ksusenum, -- SID NUMBER
ksusestn, -- STATISTIC# NUMBER
ksusestv -- VALUE NUMBER
from
x$ksumysta
where
bitand (ksspaflg, 1) !=0 and bitand (ksuseflg, 1) !=0 and ksusestn< (
select
count (*)
from
x$ksusd)
GV$NLS_PARAMETERS
select
inst_id, -- INST_ID NUMBER
parameter, -- PARAMETER VARCHAR2(64)
value -- VALUE VARCHAR2(64)
from
x$nls_parameters
where
parameter != 'NLS_SPECIAL_CHARS'
GV$NLS_VALID_VALUES
select
inst_id, -- INST_ID NUMBER
parameter, -- PARAMETER VARCHAR2(64)
value -- VALUE VARCHAR2(64)
from
x$ksulv
GV$OBJECT_DEPENDENCY
select
d.inst_id, -- INST_ID NUMBER
d.kglhdpar, -- FROM_ADDRESS RAW(4)
d.kglnahsh, -- FROM_HASH NUMBER
o.kglnaown, -- TO_OWNER VARCHAR2(64)
o.kglnaobj, -- TO_NAME VARCHAR2(1000)
o.kglhdadr, -- TO_ADDRESS RAW(4)
o.kglnahsh, -- TO_HASH NUMBER
o.kglobtyp -- TO_TYPE NUMBER
from
x$kglob o,
x$kgldp d
where
o.kglnahsh = d.kglrfhsh and o.kglhdadr = d.kglrfhdl
GV$OBSOLETE_PARAMETER
select
inst_id, -- INST_ID NUMBER
kspponm, -- NAME VARCHAR2(64)
decode -- ISSPECIFIED VARCHAR2(5)
(ksppoval, 0, 'FALSE', 'TRUE')
from
x$ksppo
GV$OFFLINE_RANGE
select
inst_id, -- INST_ID NUMBER
orrid, -- RECID NUMBER
orstm, -- STAMP NUMBER
ordfp, -- FILE# NUMBER
to_number (orofs), -- OFFLINE_CHANGE# NUMBER
to_number (orons), -- ONLINE_CHANGE# NUMBER
to_date -- ONLINE_TIME DATE
(oront, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kccor
GV$OPEN_CURSOR
select
inst_id, -- INST_ID NUMBER
kgllkuse, -- SADDR RAW(4)
kgllksnm, -- SID NUMBER
user_name, -- USER_NAME VARCHAR2(30)
kglhdpar, -- ADDRESS RAW(4)
kglnahsh, -- HASH_VALUE NUMBER
kglnaobj -- SQL_TEXT VARCHAR2(60)
from
x$kgllk
where
kglhdnsp = 0 and kglhdpar != kgllkhdl
GV$OPTION
select
inst_id, -- INST_ID NUMBER
parameter, -- PARAMETER VARCHAR2(64)
value -- VALUE VARCHAR2(64)
from
x$option
GV$PARALLEL_DEGREE_LIMIT_MTH
select
inst_id, -- INST_ID NUMBER
policy_name_kgskdopp -- NAME VARCHAR2(40)
from
x$kgskdopp
GV$PARAMETER
select
x.inst_id, -- INST_ID NUMBER
x.indx+1, -- NUM NUMBER
ksppinm, -- NAME VARCHAR2(64)
ksppity, -- TYPE NUMBER
ksppstvl, -- VALUE VARCHAR2(512)
ksppstdf, -- ISDEFAULT VARCHAR2(9)
decode -- ISSES_MODIFIABLE VARCHAR2(5)
(bitand (ksppiflg/256, 1), 1, 'TRUE', 'FALSE'),
decode -- ISSYS_MODIFIABLE VARCHAR2(9)
(bitand (ksppiflg/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE'),
decode -- ISMODIFIED VARCHAR2(10)
(bitand (ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
decode -- ISADJUSTED VARCHAR2(5)
(bitand (ksppstvf, 2), 2, 'TRUE', 'FALSE'),
ksppdesc -- DESCRIPTION VARCHAR2(64)
from
x$ksppi x,
x$ksppcv y
where
(x.indx = y.indx) and ((translate (ksppinm,
'_',
'#') not like '#%') or (ksppstdf = 'FALSE'))
GV$PARAMETER2
select
x.inst_id, -- INST_ID NUMBER
kspftctxpn, -- NUM NUMBER
ksppinm, -- NAME VARCHAR2(64)
ksppity, -- TYPE NUMBER
kspftctxvl, -- VALUE VARCHAR2(512)
kspftctxdf, -- ISDEFAULT VARCHAR2(6)
decode -- ISSES_MODIFIABLE VARCHAR2(5)
(bitand (ksppiflg/256, 1), 1, 'TRUE', 'FALSE'),
decode -- ISSYS_MODIFIABLE VARCHAR2(9)
(bitand (ksppiflg/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE'),
decode -- ISMODIFIED VARCHAR2(10)
(bitand (kspftctxvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
decode -- ISADJUSTED VARCHAR2(5)
(bitand (kspftctxvf, 2), 2, 'TRUE', 'FALSE'),
ksppdesc -- DESCRIPTION VARCHAR2(64)
from
x$ksppi x,
x$ksppcv2 y
where
((x.indx+1) = kspftctxpn) and ((translate (ksppinm,
'_',
'#') not like '#%') or (kspftctxdf = 'FALSE'))
GV$PQ_SESSTAT
select
inst_id, -- INST_ID NUMBER
kxfpssnam, -- STATISTIC VARCHAR2(30)
kxfpssval, -- LAST_QUERY NUMBER
kxfpsstot -- SESSION_TOTAL NUMBER
from
x$kxfpsst
GV$PQ_SLAVE
select
inst_id, -- INST_ID NUMBER
kxfpdpnam, -- SLAVE_NAME VARCHAR2(4)
decode -- STATUS VARCHAR2(4)
(bitand (kxfpdpflg, 16), 0, 'BUSY', 'IDLE'),
kxfpdpses, -- SESSIONS NUMBER
floor -- IDLE_TIME_CUR NUMBER
(kxfpdpcit / 6000),
floor -- BUSY_TIME_CUR NUMBER
(kxfpdpcbt / 6000),
floor (kxfpdpcct / 100), -- CPU_SECS_CUR NUMBER
kxfpdpclsnt -- MSGS_SENT_CUR NUMBER
+ kxfpdpcrsnt,
kxfpdpclrcv -- MSGS_RCVD_CUR NUMBER
+ kxfpdpcrrcv,
floor -- IDLE_TIME_TOTAL NUMBER
((kxfpdptit + kxfpdpcit) / 6000),
floor -- BUSY_TIME_TOTAL NUMBER
((kxfpdptbt + kxfpdpcbt) / 6000),
floor -- CPU_SECS_TOTAL NUMBER
((kxfpdptct + kxfpdpcct) / 100),
kxfpdptlsnt -- MSGS_SENT_TOTAL NUMBER
+ kxfpdpclsnt + kxfpdptrsnt + kxfpdpcrsnt,
kxfpdptlrcv -- MSGS_RCVD_TOTAL NUMBER
+ kxfpdpclrcv + kxfpdptrrcv + kxfpdpcrrcv
from
x$kxfpdp
where
bitand (kxfpdpflg, 8) != 0
GV$PQ_SYSSTAT
select
inst_id, -- INST_ID NUMBER
rpad (kxfpysnam, 30), -- STATISTIC VARCHAR2(30)
kxfpysval -- VALUE NUMBER
from
x$kxfpys
GV$PQ_TQSTAT
select
inst_id, -- INST_ID NUMBER
kxfqsqn, -- DFO_NUMBER NUMBER
kxfqsid, -- TQ_ID NUMBER
rpad (kxfqsty, 10), -- SERVER_TYPE VARCHAR2(10)
kxfqscnt, -- NUM_ROWS NUMBER
kxfqslen, -- BYTES NUMBER
kxfqset, -- OPEN_TIME NUMBER
kxfqsavl, -- AVG_LATENCY NUMBER
kxfqsdw, -- WAITS NUMBER
kxfqsdt, -- TIMEOUTS NUMBER
rpad (kxfqssid, 10), -- PROCESS VARCHAR2(10)
kxfqsiid -- INSTANCE NUMBER
from
x$kxfqsrow
GV$PROCESS
select
inst_id, -- INST_ID NUMBER
addr, -- ADDR RAW(4)
indx, -- PID NUMBER
ksuprpid, -- SPID VARCHAR2(9)
ksuprunm, -- USERNAME VARCHAR2(15)
ksuprser, -- SERIAL# NUMBER
ksuprtid, -- TERMINAL VARCHAR2(30)
ksuprpnm, -- PROGRAM VARCHAR2(48)
ksuprtfi, -- TRACEID VARCHAR2(255)
decode -- BACKGROUND VARCHAR2(1)
(bitand (ksuprflg, 2), 0, null, 1),
decode -- LATCHWAIT VARCHAR2(8)
(ksllawat, hextoraw ('00'), null, ksllawat),
decode -- LATCHSPIN VARCHAR2(8)
(ksllaspn, hextoraw ('00'), null, ksllaspn)
from
x$ksupr
where
bitand (ksspaflg, 1) !=0
GV$PROXY_ARCHIVEDLOG
select
inst_id, -- INST_ID NUMBER
pcrid, -- RECID NUMBER
pcstm, -- STAMP NUMBER
pcdev, -- DEVICE_TYPE VARCHAR2(17)
pchdl, -- HANDLE VARCHAR2(513)
pccmt, -- COMMENTS VARCHAR2(81)
pcmdh, -- MEDIA VARCHAR2(65)
pcmpl, -- MEDIA_POOL NUMBER
decode -- STATUS VARCHAR2(1)
(bitand (pcflg, 1+2), 1, 'D', 2, 'X', 0, 'A', '?'),
decode -- DELETED VARCHAR2(3)
(bitand (pcflg, 1), 1, 'YES', 'NO'),
pathp, -- THREAD# NUMBER
paseq, -- SEQUENCE# NUMBER
to_number (parls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(parlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (palos), -- FIRST_CHANGE# NUMBER
to_date -- FIRST_TIME DATE
(palot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (panxs), -- NEXT_CHANGE# NUMBER
to_date -- NEXT_TIME DATE
(panxt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
pabct, -- BLOCKS NUMBER
pabsz, -- BLOCK_SIZE NUMBER
to_date -- START_TIME DATE
(pctsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- COMPLETION_TIME DATE
(pctim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
(to_date -- ELAPSED_SECONDS NUMBER
(pctim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (pctsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) *86400
from
x$kccpa
GV$PROXY_DATAFILE
select
inst_id, -- INST_ID NUMBER
pcrid, -- RECID NUMBER
pcstm, -- STAMP NUMBER
pcdev, -- DEVICE_TYPE VARCHAR2(17)
pchdl, -- HANDLE VARCHAR2(513)
pccmt, -- COMMENTS VARCHAR2(81)
pcmdh, -- MEDIA VARCHAR2(65)
pcmpl, -- MEDIA_POOL NUMBER
pctag, -- TAG VARCHAR2(32)
decode -- STATUS VARCHAR2(1)
(bitand (pcflg, 1+2), 1, 'D', 2, 'X', 0, 'A', '?'),
decode -- DELETED VARCHAR2(3)
(bitand (pcflg, 1), 1, 'YES', 'NO'),
pddfp, -- FILE# NUMBER
to_number (pdcrs), -- CREATION_CHANGE# NUMBER
to_date -- CREATION_TIME DATE
(pdcrt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (pdrls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(pdrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (pdcps), -- CHECKPOINT_CHANGE# NUMBER
to_date -- CHECKPOINT_TIME DATE
(pdcpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (pdafs), -- ABSOLUTE_FUZZY_CHANGE# NUMBER
to_number (pdrfs), -- RECOVERY_FUZZY_CHANGE# NUMBER
to_date -- RECOVERY_FUZZY_TIME DATE
(pdrft, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- INCREMENTAL_LEVEL NUMBER
(bitand (pdflg, 1), 1, 0, NULL),
decode -- ONLINE_FUZZY VARCHAR2(3)
(bitand (pdflg, 2), 0, 'NO', 'YES'),
decode -- BACKUP_FUZZY VARCHAR2(3)
(bitand (pdflg, 4), 0, 'NO', 'YES'),
pdfsz, -- BLOCKS NUMBER
pdbsz, -- BLOCK_SIZE NUMBER
pdlor, -- OLDEST_OFFLINE_RANGE NUMBER
to_date -- START_TIME DATE
(pctsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- COMPLETION_TIME DATE
(pctim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
(to_date -- ELAPSED_SECONDS NUMBER
(pctim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (pctsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) *86400,
decode -- CONTROLFILE_TYPE VARCHAR2(1)
(pddfp, 0, decode (bitand (pcflg, 8), 8, 'S', 'B'), NULL)
from
x$kccpd
GV$PWFILE_USERS
select
inst_id, -- INST_ID NUMBER
username, -- USERNAME VARCHAR2(30)
decode -- SYSDBA VARCHAR2(5)
(sysdba, 1, 'TRUE', 'FALSE'),
decode -- SYSOPER VARCHAR2(5)
(sysoper, 1, 'TRUE', 'FALSE')
from
x$kzsrt
where
valid=1
GV$PX_PROCESS
select
a.inst_id, -- INST_ID NUMBER
a.kxfpdpnam, -- SERVER_NAME VARCHAR2(4)
decode -- STATUS VARCHAR2(9)
(bitand (a.kxfpdpflg, 16), 0, 'IN USE', 'AVAILABLE'),
b.pid, -- PID NUMBER
a.kxfpdpspid, -- SPID VARCHAR2(9)
c.sid, -- SID NUMBER
c.serial# -- SERIAL# NUMBER
from
x$kxfpdp a,
GV$PROCESS b,
GV$SESSION c
where
bitand (kxfpdpflg, 8) != 0 and a.inst_id = b.inst_id and a.inst_id = c.inst_id (+) and a.kxfpdpspid = b.SPID and a.kxfpdpspid = c.PROCESS (+)
GV$PX_PROCESS_SYSSTAT
select
inst_id, -- INST_ID NUMBER
rpad (kxfpnsnam, 30), -- STATISTIC VARCHAR2(30)
kxfpnsval -- VALUE NUMBER
from
x$kxfpns
GV$PX_SESSION
select
a.inst_id, -- INST_ID NUMBER
a.addr, -- SADDR RAW(4)
a.indx, -- SID NUMBER
a.ksuseser, -- SERIAL# NUMBER
decode -- QCSID NUMBER
(b.kxfpdpqcsid, NULL, a.indx, b.kxfpdpqcsid),
b.kxfpdpqcser, -- QCSERIAL# NUMBER
b.kxfpdpcin, -- QCINST_ID NUMBER
b.kxfpdpsvgrp, -- SERVER_GROUP NUMBER
b.kxfpdpsvset, -- SERVER_SET NUMBER
b.kxfpdpsvnum, -- SERVER# NUMBER
b.kxfpdpadg, -- DEGREE NUMBER
b.kxfpdprdg -- REQ_DEGREE NUMBER
from
x$ksuse a,
x$kxfpdp b
where
bitand (a.ksspaflg, 1) !=0 and bitand (a.ksuseflg, 1) !=0 and a.ksuseqcsid > 0 and a.ksusepro = b.kxfpdppro (+)
GV$PX_SESSTAT
select
a.inst_id, -- INST_ID NUMBER
a.addr, -- SADDR RAW(4)
a.indx, -- SID NUMBER
a.ksuseser, -- SERIAL# NUMBER
decode -- QCSID NUMBER
(b.kxfpdpqcsid, NULL, a.indx, b.kxfpdpqcsid),
b.kxfpdpqcser, -- QCSERIAL# NUMBER
b.kxfpdpcin, -- QCINST_ID NUMBER
b.kxfpdpsvgrp, -- SERVER_GROUP NUMBER
b.kxfpdpsvset, -- SERVER_SET NUMBER
b.kxfpdpsvnum, -- SERVER# NUMBER
b.kxfpdpadg, -- DEGREE NUMBER
b.kxfpdprdg, -- REQ_DEGREE NUMBER
c.ksusestn, -- STATISTIC# NUMBER
c.ksusestv -- VALUE NUMBER
from
x$ksuse a,
x$kxfpdp b,
x$ksusesta c
where
bitand (a.ksspaflg, 1) !=0 and bitand (a.ksuseflg, 1) !=0 and a.KSUSEQCSID > 0 and a.ksusepro = b.kxfpdppro (+) and a.inst_id = c.inst_id and a.indx = c.indx and c.ksusestn < (
select
count (*)
from
x$ksusd)
GV$QUEUE
select
inst_id, -- INST_ID NUMBER
kmcqspro, -- PADDR RAW(4)
decode -- TYPE VARCHAR2(10)
(indx, 0, 'COMMON', 'DISPATCHER'),
kmcqsncq, -- QUEUED NUMBER
kmcqswat, -- WAIT NUMBER
kmcqstnc -- TOTALQ NUMBER
from
x$kmcqs
where
indx=0 or kmcqspro!=hextoraw ('00')
GV$RECOVERY_FILE_STATUS
select
fn.inst_id, -- INST_ID NUMBER
fn.fnfno, -- FILENUM NUMBER
fn.fnnam, -- FILENAME VARCHAR2(513)
decode -- STATUS VARCHAR2(13)
(nvl (mf.cps, 0), 0, 'NOT RECOVERED', 281474976710655, 'CURRENT', 'IN RECOVERY')
from
x$kcrmx mx,
x$kccfn fn,
x$kccfe fe,
x$kcrmf mf
where
fn.fntyp = 4 and mf.fno (+) = fn.fnfno and ((bitand (mx.flg, 2) != 0 and fe.fedup != 0) or mf.fno = fn.fnfno) and fe.fenum = fn.fnfno
GV$RECOVERY_LOG
select
inst_id, -- INST_ID NUMBER
lhthp, -- THREAD# NUMBER
lhseq, -- SEQUENCE# NUMBER
to_date -- TIME DATE
(lhlot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
lhnam -- ARCHIVE_NAME VARCHAR2(513)
from
x$kcclh
where
to_number (lhnxs) > (
select
min (to_number (fhscn))
from
x$kcvfhmrr
where
hxerr = 0) and lhseq not in (
select
leseq
from
x$kccle
where
lethr = lhthp) and to_number (lhlos) < (
select
max (to_number (hxsts))
from
x$kcvfhmrr
where
hxerr = 0)
GV$RECOVERY_PROGRESS
select
INST_ID, -- INST_ID NUMBER
OPNAME, -- TYPE VARCHAR2(64)
TARGET_DESC, -- ITEM VARCHAR2(32)
SOFAR, -- SOFAR NUMBER
TOTALWORK -- TOTAL NUMBER
from
GV$SESSION_LONGOPS
GV$RECOVERY_STATUS
select
fx.inst_id, -- INST_ID NUMBER
to_date -- RECOVERY_CHECKPOINT DATE
(mx.ckptim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
mx.thr, -- THREAD NUMBER
mx.seq, -- SEQUENCE_NEEDED NUMBER
mx.los, -- SCN_NEEDED VARCHAR2(16)
to_date -- TIME_NEEDED DATE
(mx.tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
nvl (mx.nam, 'NONE'), -- PREVIOUS_LOG_NAME VARCHAR2(513)
decode -- PREVIOUS_LOG_STATUS VARCHAR2(13)
(bitand (mx.mrs, 256 + 128 + 64 + 8), 8, 'RELEASE', 64, 'WRONG LOG', 128, 'MISSING NAME', 256, 'UNNEEDED NAME', 'NONE'),
decode -- REASON VARCHAR2(13)
(nvl (fx.err, 3), 1, 'NEED LOG', 3, 'END OF THREAD', 4, 'LOG REUSED', 'UNKNOWN')
from
x$kcrmx mx,
x$kcrfx fx
where
fx.thr (+) = mx.thr
GV$RECOVER_FILE
select
inst_id, -- INST_ID NUMBER
hxfil, -- FILE# NUMBER
decode -- ONLINE VARCHAR2(7)
(hxons, 0, 'OFFLINE', 'ONLINE'),
decode -- ERROR VARCHAR2(18)
(hxerr, 0, '', 1, 'FILE MISSING', 2, 'OFFLINE NORMAL', 3, 'NOT VERIFIED', 4, 'FILE NOT FOUND', 5, 'CANNOT OPEN FILE', 6, 'CANNOT READ HEADER', 7, 'CORRUPT HEADER', 8, 'WRONG FILE TYPE', 9, 'WRONG DATABASE', 10, 'WRONG FILE NUMBER', 11, 'WRONG FILE CREATE', 12, 'WRONG FILE CREATE', 16, 'DELAYED OPEN', 'UNKNOWN ERROR'),
to_number (fhscn), -- CHANGE# NUMBER
to_date -- TIME DATE
(fhtim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kcvfhmrr
GV$REQDIST
select
inst_id, -- INST_ID NUMBER
kmmrdbuc, -- BUCKET NUMBER
sum (kmmrdcnt) -- COUNT NUMBER
from
x$kmmrd
where
kmmrdpro!=hextoraw ('00')
group by inst_id,
kmmrdbuc
GV$RESERVED_WORDS
select
inst_id, -- INST_ID NUMBER
keyword, -- KEYWORD VARCHAR2(64)
length -- LENGTH NUMBER
from
x$kwddef
GV$RESOURCE
select
inst_id, -- INST_ID NUMBER
addr, -- ADDR RAW(4)
ksqrsidt, -- TYPE VARCHAR2(2)
ksqrsid1, -- ID1 NUMBER
ksqrsid2 -- ID2 NUMBER
from
x$ksqrs
where
bitand (ksqrsflg, 2) !=0
GV$RESOURCE_LIMIT
select
inst_id, -- INST_ID NUMBER
ksurlmnm, -- RESOURCE_NAME VARCHAR2(30)
ksurlmcv, -- CURRENT_UTILIZATION NUMBER
ksurlmmv, -- MAX_UTILIZATION NUMBER
LPAD -- INITIAL_ALLOCATION VARCHAR2(10)
(decode (bitand (ksurlmfg, 1), 0, to_char (ksurlmia), 'UNLIMITED'), 10),
LPAD -- LIMIT_VALUE VARCHAR2(10)
(decode (bitand (ksurlmfg, 2), 0, to_char (ksurlmlv), 'UNLIMITED'), 10)
from
x$ksurlmt
GV$ROLLSTAT
select
inst_id, -- INST_ID NUMBER
kturdusn, -- USN NUMBER
kturdext, -- EXTENTS NUMBER
kturdsiz, -- RSSIZE NUMBER
kturdwrt, -- WRITES NUMBER
kturdnax, -- XACTS NUMBER
kturdget, -- GETS NUMBER
kturdwat, -- WAITS NUMBER
decode -- OPTSIZE NUMBER
(kturdopt, -1, to_number (null), kturdopt),
kturdhwm, -- HWMSIZE NUMBER
kturdnsh, -- SHRINKS NUMBER
kturdnwp, -- WRAPS NUMBER
kturdnex, -- EXTENDS NUMBER
kturdash, -- AVESHRINK NUMBER
kturdaae, -- AVEACTIVE NUMBER
decode -- STATUS VARCHAR2(15)
(kturdflg, 0, 'ONLINE', 2, 'PENDING OFFLINE', 3, 'OFFLINE', 4, 'FULL', 'UNKNOWN'),
kturdcex, -- CUREXT NUMBER
kturdcbk -- CURBLK NUMBER
from
x$kturd
where
kturdsiz!=0 and kturdflg != 3
GV$ROWCACHE
select
inst_id, -- INST_ID NUMBER
kqrstcid, -- CACHE# NUMBER
decode -- TYPE VARCHAR2(11)
(kqrsttyp, 1, 'PARENT', 'SUBORDINATE'),
decode -- SUBORDINATE# NUMBER
(kqrsttyp, 2, kqrstsno, null),
kqrsttxt, -- PARAMETER VARCHAR2(32)
kqrstcsz, -- COUNT NUMBER
kqrstusg, -- USAGE NUMBER
kqrstfcs, -- FIXED NUMBER
kqrstgrq, -- GETS NUMBER
kqrstgmi, -- GETMISSES NUMBER
kqrstsrq, -- SCANS NUMBER
kqrstsmi, -- SCANMISSES NUMBER
kqrstsco, -- SCANCOMPLETES NUMBER
kqrstmrq, -- MODIFICATIONS NUMBER
kqrstmfl, -- FLUSHES NUMBER
kqrstilr, -- DLM_REQUESTS NUMBER
kqrstifr, -- DLM_CONFLICTS NUMBER
kqrstisr -- DLM_RELEASES NUMBER
from
x$kqrst
GV$ROWCACHE_PARENT
select
inst_id, -- INST_ID NUMBER
indx, -- INDX NUMBER
kqrfphsh, -- HASH NUMBER
kqrfpadd, -- ADDRESS RAW(4)
kqrfpcid, -- CACHE# NUMBER
kqrfpcnm, -- CACHE_NAME VARCHAR2(64)
decode -- EXISTENT VARCHAR2(1)
(bitand (kqrfpflg, 1), 0, 'Y', 'N'),
kqrfpmod, -- LOCK_MODE NUMBER
kqrfpreq, -- LOCK_REQUEST NUMBER
kqrfptxn, -- TXN RAW(4)
kqrfpses, -- SADDR RAW(4)
kqrfpirq, -- INST_LOCK_REQUEST NUMBER
kqrfpirl, -- INST_LOCK_RELEASE NUMBER
kqrfpity, -- INST_LOCK_TYPE VARCHAR2(2)
kqrfpii1, -- INST_LOCK_ID1 RAW(4)
kqrfpii2, -- INST_LOCK_ID2 RAW(4)
kqrfpkey -- KEY RAW(100)
from
x$kqrfp
GV$ROWCACHE_SUBORDINATE
select
inst_id, -- INST_ID NUMBER
indx, -- INDX NUMBER
kqrfshsh, -- HASH NUMBER
kqrfsadd, -- ADDRESS RAW(4)
kqrfscid, -- CACHE# NUMBER
kqrfssid, -- SUBCACHE# NUMBER
kqrfssnm, -- SUBCACHE_NAME VARCHAR2(64)
decode -- EXISTENT VARCHAR2(1)
(bitand (kqrfsflg, 1), 0, 'Y', 'N'),
kqrfspar, -- PARENT RAW(4)
kqrfskey -- KEY RAW(100)
from
x$kqrfs
GV$RSRC_CONSUMER_GROUP
select
inst_id, -- INST_ID NUMBER
name_kgskcft, -- NAME VARCHAR2(32)
current_count_kgskcft, -- ACTIVE_SESSIONS NUMBER
runnable_count_kgskcft, -- EXECUTION_WAITERS NUMBER
total_count_kgskcft, -- REQUESTS NUMBER
cpu_wait_kgskcft, -- CPU_WAIT_TIME NUMBER
cpu_waits_kgskcft, -- CPU_WAITS NUMBER
total_used_kgskcft, -- CONSUMED_CPU_TIME NUMBER
yields_kgskcft, -- YIELDS NUMBER
num_queued_kgskcft -- SESSIONS_QUEUED NUMBER
from
x$kgskcft
GV$RSRC_CONSUMER_GROUP_CPU_MTH
select
inst_id, -- INST_ID NUMBER
policy_name_kgskcp -- NAME VARCHAR2(40)
from
x$kgskcp
GV$RSRC_PLAN
select
inst_id, -- INST_ID NUMBER
name_kgskpft -- NAME VARCHAR2(32)
from
x$kgskpft
GV$RSRC_PLAN_CPU_MTH
select
inst_id, -- INST_ID NUMBER
policy_name_kgskpp -- NAME VARCHAR2(40)
from
x$kgskpp
GV$SESSION
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(9)
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'))
from
x$ksuse
where
bitand (ksspaflg, 1) !=0 and bitand (ksuseflg, 1) !=0
GV$SESSION_CONNECT_INFO
select
inst_id, -- INST_ID NUMBER
ksusenum, -- SID NUMBER
decode -- AUTHENTICATION_TYPE VARCHAR2(8)
(ksuseaty, 0, 'DATABASE', 1, 'OS', 2, 'NETWORK', 3, 'PROXY', 4, 'NETWORK', 5, 'NETWORK', 6, 'OS', 7, 'OS', '?'),
ksuseunm, -- OSUSER VARCHAR2(30)
ksuseban -- NETWORK_SERVICE_BANNER VARCHAR2(4000)
from
x$ksusecon
where
bitand (ksuseflg, 1) !=0 and bitand (ksuseflg, 16) =0
GV$SESSION_CURSOR_CACHE
select
inst_id, -- INST_ID NUMBER
kgiccmax, -- MAXIMUM NUMBER
kgicccnt, -- COUNT NUMBER
kgiccopd, -- OPENED_ONCE NUMBER
kgiccope, -- OPEN NUMBER
kgiccopn, -- OPENS NUMBER
kgicchit, -- HITS NUMBER
decode -- HIT_RATIO NUMBER
(kgiccopn, 0, 1, kgicchit/kgiccopn)
from
x$kgicc
GV$SESSION_EVENT
select
s.inst_id, -- INST_ID NUMBER
s.kslessid, -- SID NUMBER
d.kslednam, -- EVENT VARCHAR2(64)
s.ksleswts, -- TOTAL_WAITS NUMBER
s.kslestmo, -- TOTAL_TIMEOUTS NUMBER
s.kslestim, -- TIME_WAITED NUMBER
s.kslestim / s.ksleswts, -- AVERAGE_WAIT NUMBER
s.kslesmxt -- MAX_WAIT NUMBER
from
x$ksles s,
x$ksled d
where
s.ksleswts != 0 and s.kslesenm = d.indx
GV$SESSION_LONGOPS
select
inst_id, -- INST_ID NUMBER
ksulosno, -- SID NUMBER
ksulosrn, -- SERIAL# NUMBER
ksulopna, -- OPNAME VARCHAR2(64)
ksulotna, -- TARGET VARCHAR2(64)
ksulotde, -- TARGET_DESC VARCHAR2(32)
ksulosfr, -- SOFAR NUMBER
ksulotot, -- TOTALWORK NUMBER
ksulouni, -- UNITS VARCHAR2(32)
to_date -- START_TIME DATE
(ksulostm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- LAST_UPDATE_TIME DATE
(ksulolut, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- TIME_REMAINING NUMBER
(ksulosfr, 0, 0, round (ksuloetm* ((ksulotot-ksulosfr) /ksulosfr))),
ksuloetm, -- ELAPSED_SECONDS NUMBER
ksuloctx, -- CONTEXT NUMBER
ksulomsg, -- MESSAGE VARCHAR2(512)
ksulounm, -- USERNAME VARCHAR2(30)
ksulosql, -- SQL_ADDRESS RAW(4)
ksulosqh, -- SQL_HASH_VALUE NUMBER
ksuloqid -- QCSID NUMBER
from
x$ksulop
GV$SESSION_OBJECT_CACHE
select
inst_id, -- INST_ID NUMBER
kocstpin, -- PINS NUMBER
kocsthit, -- HITS NUMBER
kocsttht, -- TRUE_HITS NUMBER
decode -- HIT_RATIO NUMBER
(kocstpin, 0, 1, kocsthit/kocstpin),
decode -- TRUE_HIT_RATIO NUMBER
(kocstpin, 0, 1, kocsttht/kocstpin),
kocstorf, -- OBJECT_REFRESHES NUMBER
kocstrfs, -- CACHE_REFRESHES NUMBER
kocstofs, -- OBJECT_FLUSHES NUMBER
kocstfls, -- CACHE_FLUSHES NUMBER
kocstshr, -- CACHE_SHRINKS NUMBER
kocstcnt, -- CACHED_OBJECTS NUMBER
kocstpnd, -- PINNED_OBJECTS NUMBER
kocstsiz, -- CACHE_SIZE NUMBER
kocstopt, -- OPTIMAL_SIZE NUMBER
kocstmax -- MAXIMUM_SIZE NUMBER
from
x$kocst
GV$SESSION_WAIT
select
s.inst_id, -- INST_ID NUMBER
s.indx, -- SID NUMBER
s.ksussseq, -- SEQ# NUMBER
e.kslednam, -- EVENT VARCHAR2(64)
e.ksledp1, -- P1TEXT VARCHAR2(64)
s.ksussp1, -- P1 NUMBER
s.ksussp1r, -- P1RAW RAW(4)
e.ksledp2, -- P2TEXT VARCHAR2(64)
s.ksussp2, -- P2 NUMBER
s.ksussp2r, -- P2RAW RAW(4)
e.ksledp3, -- P3TEXT VARCHAR2(64)
s.ksussp3, -- P3 NUMBER
s.ksussp3r, -- P3RAW RAW(4)
s.ksusstim, -- WAIT_TIME NUMBER
s.ksusewtm, -- SECONDS_IN_WAIT NUMBER
decode -- STATE VARCHAR2(19)
(s.ksusstim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, 'WAITED SHORT TIME', 'WAITED KNOWN TIME')
from
x$ksusecst s,
x$ksled e
where
bitand (s.ksspaflg, 1) !=0 and bitand (s.ksuseflg, 1) !=0 and s.ksussseq!=0 and s.ksussopc=e.indx
GV$SESSTAT
select
inst_id, -- INST_ID NUMBER
indx, -- SID NUMBER
ksusestn, -- STATISTIC# NUMBER
ksusestv -- VALUE NUMBER
from
x$ksusesta
where
bitand (ksspaflg, 1) !=0 and bitand (ksuseflg, 1) !=0 and ksusestn< (
select
count (*)
from
x$ksusd)
GV$SESS_IO
select
inst_id, -- INST_ID NUMBER
indx, -- SID NUMBER
ksusesbg, -- BLOCK_GETS NUMBER
ksusescg, -- CONSISTENT_GETS NUMBER
ksusespr, -- PHYSICAL_READS NUMBER
ksusesbc, -- BLOCK_CHANGES NUMBER
ksusescc -- CONSISTENT_CHANGES NUMBER
from
x$ksusio
where
bitand (ksspaflg, 1) !=0 and bitand (ksuseflg, 1) !=0
GV$SGA
select
inst_id, -- INST_ID NUMBER
ksmsdnam, -- NAME VARCHAR2(20)
ksmsdval -- VALUE NUMBER
from
x$ksmsd
GV$SGASTAT
select
inst_id, -- INST_ID NUMBER
'', -- POOL VARCHAR2(11)
ksmssnam, -- NAME VARCHAR2(26)
ksmsslen -- BYTES NUMBER
from
x$ksmfs
where
ksmsslen>1
union all
select
inst_id, -- INST_ID NUMBER
'shared pool', -- POOL VARCHAR2(11)
ksmssnam, -- NAME VARCHAR2(26)
ksmsslen -- BYTES NUMBER
from
x$ksmss
where
ksmsslen>1
union all
select
inst_id, -- INST_ID NUMBER
'large pool', -- POOL VARCHAR2(11)
ksmssnam, -- NAME VARCHAR2(26)
ksmsslen -- BYTES NUMBER
from
x$ksmls
where
ksmsslen>1
union all
select
inst_id, -- INST_ID NUMBER
'java pool', -- POOL VARCHAR2(11)
ksmssnam, -- NAME VARCHAR2(26)
ksmsslen -- BYTES NUMBER
from
x$ksmjs
where
ksmsslen>1
GV$SHARED_POOL_RESERVED
select
avg (x$ksmspr.inst_id), -- INST_ID NUMBER
sum -- FREE_SPACE NUMBER
(decode (ksmchcls, 'R-free', ksmchsiz, 0)),
avg -- AVG_FREE_SIZE NUMBER
(decode (ksmchcls, 'R-free', ksmchsiz, 0)),
sum -- FREE_COUNT NUMBER
(decode (ksmchcls, 'R-free', 1, 0)),
max -- MAX_FREE_SIZE NUMBER
(decode (ksmchcls, 'R-free', ksmchsiz, 0)),
sum -- USED_SPACE NUMBER
(decode (ksmchcls, 'R-free', 0, ksmchsiz)),
avg -- AVG_USED_SIZE NUMBER
(decode (ksmchcls, 'R-free', 0, ksmchsiz)),
sum -- USED_COUNT NUMBER
(decode (ksmchcls, 'R-free', 0, 1)),
max -- MAX_USED_SIZE NUMBER
(decode (ksmchcls, 'R-free', 0, ksmchsiz)),
avg (kghlurcn), -- REQUESTS NUMBER
avg (kghlurmi), -- REQUEST_MISSES NUMBER
avg (kghlurmz), -- LAST_MISS_SIZE NUMBER
avg (kghlurmx), -- MAX_MISS_SIZE NUMBER
avg (kghlunfu), -- REQUEST_FAILURES NUMBER
avg (kghlunfs), -- LAST_FAILURE_SIZE NUMBER
avg (kghlumxa), -- ABORTED_REQUEST_THRESHOLD NUMBER
avg (kghlumer), -- ABORTED_REQUESTS NUMBER
avg (kghlumes) -- LAST_ABORTED_SIZE NUMBER
from
x$ksmspr,
x$kghlu
where
ksmchcom not like '%reserved sto%'
GV$SHARED_SERVER
select
inst_id, -- INST_ID NUMBER
kmmsinam, -- NAME VARCHAR2(4)
kmmsiprp, -- PADDR RAW(4)
kmmsista, -- STATUS VARCHAR2(16)
kmmsinmg, -- MESSAGES NUMBER
kmmsinmb, -- BYTES NUMBER
kmmsibrk, -- BREAKS NUMBER
kmmsivcp, -- CIRCUIT RAW(4)
kmmsiidl, -- IDLE NUMBER
kmmsibsy, -- BUSY NUMBER
kmmsitnc -- REQUESTS NUMBER
from
x$kmmsi
where
bitand (kmmsiflg, 1) !=0
GV$SORT_SEGMENT
select
inst_id, -- INST_ID NUMBER
tablespace_name, -- TABLESPACE_NAME VARCHAR2(31)
segment_file, -- SEGMENT_FILE NUMBER
segment_block, -- SEGMENT_BLOCK NUMBER
extent_size, -- EXTENT_SIZE NUMBER
current_users, -- CURRENT_USERS NUMBER
total_extents, -- TOTAL_EXTENTS NUMBER
total_blocks, -- TOTAL_BLOCKS NUMBER
used_extents, -- USED_EXTENTS NUMBER
used_blocks, -- USED_BLOCKS NUMBER
free_extents, -- FREE_EXTENTS NUMBER
free_blocks, -- FREE_BLOCKS NUMBER
added_extents, -- ADDED_EXTENTS NUMBER
extent_hits, -- EXTENT_HITS NUMBER
freed_extents, -- FREED_EXTENTS NUMBER
free_requests, -- FREE_REQUESTS NUMBER
max_size, -- MAX_SIZE NUMBER
max_blocks, -- MAX_BLOCKS NUMBER
max_used_size, -- MAX_USED_SIZE NUMBER
max_used_blocks, -- MAX_USED_BLOCKS NUMBER
max_sort_size, -- MAX_SORT_SIZE NUMBER
max_sort_blocks, -- MAX_SORT_BLOCKS NUMBER
relative_fno -- RELATIVE_FNO NUMBER
from
x$ktstssd
GV$SORT_USAGE
select
x$ktsso.inst_id, -- INST_ID NUMBER
username, -- USER VARCHAR2(30)
ktssoses, -- SESSION_ADDR RAW(4)
ktssosno, -- SESSION_NUM NUMBER
prev_sql_addr, -- SQLADDR RAW(4)
prev_hash_value, -- SQLHASH NUMBER
ktssotsn, -- TABLESPACE VARCHAR2(31)
decode -- CONTENTS VARCHAR2(9)
(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
decode -- SEGTYPE VARCHAR2(9)
(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX', 'UNDEFINED'),
ktssofno, -- SEGFILE# NUMBER
ktssobno, -- SEGBLK# NUMBER
ktssoexts, -- EXTENTS NUMBER
ktssoblks, -- BLOCKS NUMBER
ktssorfno -- SEGRFNO# NUMBER
from
x$ktsso,
v$session
where
ktssoses = v$session.saddr and ktssosno = v$session.serial#
GV$SQL
select
inst_id, -- INST_ID NUMBER
kglnaobj, -- SQL_TEXT VARCHAR2(1000)
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16, -- SHARABLE_MEM NUMBER
kglobt08+kglobt11, -- PERSISTENT_MEM NUMBER
kglobt10, -- RUNTIME_MEM NUMBER
kglobt01, -- SORTS NUMBER
decode -- LOADED_VERSIONS NUMBER
(kglobhs6, 0, 0, 1),
decode -- OPEN_VERSIONS NUMBER
(kglhdlmd, 0, 0, 1),
kglhdlkc, -- USERS_OPENING NUMBER
kglhdexc, -- EXECUTIONS NUMBER
kglobpc6, -- USERS_EXECUTING NUMBER
kglhdldc, -- LOADS NUMBER
substr -- FIRST_LOAD_TIME VARCHAR2(19)
(to_char (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
kglhdivc, -- INVALIDATIONS NUMBER
kglobt12, -- PARSE_CALLS NUMBER
kglobt13, -- DISK_READS NUMBER
kglobt14, -- BUFFER_GETS NUMBER
kglobt15, -- ROWS_PROCESSED NUMBER
kglobt02, -- COMMAND_TYPE NUMBER
decode -- OPTIMIZER_MODE VARCHAR2(10)
(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN'),
kglobtn0, -- OPTIMIZER_COST NUMBER
kglobt17, -- PARSING_USER_ID NUMBER
kglobt18, -- PARSING_SCHEMA_ID NUMBER
kglhdkmk, -- KEPT_VERSIONS NUMBER
kglhdpar, -- ADDRESS RAW(4)
kglobtp0, -- TYPE_CHK_HEAP RAW(4)
kglnahsh, -- HASH_VALUE NUMBER
kglobt09, -- CHILD_NUMBER NUMBER
kglobts0, -- MODULE VARCHAR2(64)
kglobt19, -- MODULE_HASH NUMBER
kglobts1, -- ACTION VARCHAR2(64)
kglobt20, -- ACTION_HASH NUMBER
kglobt21, -- SERIALIZABLE_ABORTS NUMBER
kglobts2 -- OUTLINE_CATEGORY VARCHAR2(64)
from
x$kglcursor
where
kglhdadr != kglhdpar and kglobt02 != 0
GV$SQLAREA
select
inst_id, -- INST_ID NUMBER
kglnaobj, -- SQL_TEXT VARCHAR2(1000)
sum -- SHARABLE_MEM NUMBER
(kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6),
sum (kglobt08+kglobt11), -- PERSISTENT_MEM NUMBER
sum (kglobt10), -- RUNTIME_MEM NUMBER
sum (kglobt01), -- SORTS NUMBER
count (*) -1, -- VERSION_COUNT NUMBER
sum -- LOADED_VERSIONS NUMBER
(decode (kglobhs6, 0, 0, 1)),
decode -- OPEN_VERSIONS NUMBER
(sum (decode (kglhdlmd, 0, 0, 1)), 0, 0, sum (decode (kglhdlmd, 0, 0, 1)) -1),
sum (kglhdlkc) /2, -- USERS_OPENING NUMBER
sum (kglhdexc), -- EXECUTIONS NUMBER
sum (kglobpc6), -- USERS_EXECUTING NUMBER
sum (kglhdldc) -1, -- LOADS NUMBER
substr -- FIRST_LOAD_TIME VARCHAR2(19)
(to_char (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
sum (kglhdivc), -- INVALIDATIONS NUMBER
sum (kglobt12), -- PARSE_CALLS NUMBER
sum (kglobt13), -- DISK_READS NUMBER
sum (kglobt14), -- BUFFER_GETS NUMBER
sum (kglobt15), -- ROWS_PROCESSED NUMBER
sum -- COMMAND_TYPE NUMBER
(decode (kglobt09, 0, kglobt02, 0)),
decode -- OPTIMIZER_MODE VARCHAR2(25)
(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 -- PARSING_USER_ID NUMBER
(decode (kglobt09, 0, kglobt17, 0)),
sum -- PARSING_SCHEMA_ID NUMBER
(decode (kglobt09, 0, kglobt18, 0)),
decode -- KEPT_VERSIONS NUMBER
(sum (decode (kglhdkmk, 0, 0, 1)), 0, 0, sum (decode (kglhdkmk, 0, 0, 1)) -1),
kglhdpar, -- ADDRESS RAW(4)
kglnahsh, -- HASH_VALUE NUMBER
kglobts0, -- MODULE VARCHAR2(64)
kglobt19, -- MODULE_HASH NUMBER
kglobts1, -- ACTION VARCHAR2(64)
kglobt20, -- ACTION_HASH NUMBER
sum (kglobt21) -- SERIALIZABLE_ABORTS NUMBER
from
x$kglcursor
group by inst_id,
kglnaobj,
kglhdpar,
kglnahsh,
kglnatim,
kglobts0,
kglobt19,
kglobts1,
kglobt20
having sum (decode (kglobt09, 0, kglobt02, 0)) != 0
GV$SQLTEXT
select
inst_id, -- INST_ID NUMBER
kglhdadr, -- ADDRESS RAW(4)
kglnahsh, -- HASH_VALUE NUMBER
kgloboct, -- COMMAND_TYPE NUMBER
piece, -- PIECE NUMBER
name -- SQL_TEXT VARCHAR2(64)
from
x$kglna
where
kgloboct != 0
GV$SQLTEXT_WITH_NEWLINES
select
inst_id, -- INST_ID NUMBER
kglhdadr, -- ADDRESS RAW(4)
kglnahsh, -- HASH_VALUE NUMBER
kgloboct, -- COMMAND_TYPE NUMBER
piece, -- PIECE NUMBER
name -- SQL_TEXT VARCHAR2(64)
from
x$kglna1
where
kgloboct != 0
GV$SQL_BIND_DATA
select
inst_id, -- INST_ID NUMBER
kxsbdcur, -- CURSOR_NUM NUMBER
kxsbdbnd, -- POSITION NUMBER
kxsbddty, -- DATATYPE NUMBER
kxsbdmxl, -- SHARED_MAX_LEN NUMBER
kxsbdpmx, -- PRIVATE_MAX_LEN NUMBER
kxsbdmal, -- ARRAY_SIZE NUMBER
kxsbdpre, -- PRECISION NUMBER
kxsbdscl, -- SCALE NUMBER
kxsbdofl, -- SHARED_FLAG NUMBER
kxsbdof2, -- SHARED_FLAG2 NUMBER
kxsbdbfp, -- BUF_ADDRESS RAW(4)
kxsbdbln, -- BUF_LENGTH NUMBER
kxsbdavl, -- VAL_LENGTH NUMBER
kxsbdbfl, -- BUF_FLAG NUMBER
kxsbdind, -- INDICATOR NUMBER
kxsbdval -- VALUE VARCHAR2(4000)
from
x$kxsbd
GV$SQL_BIND_METADATA
select
inst_id, -- INST_ID NUMBER
kglhdadr, -- ADDRESS RAW(4)
position, -- POSITION NUMBER
kkscbndt, -- DATATYPE NUMBER
kkscbndl, -- MAX_LENGTH NUMBER
kkscbnda, -- ARRAY_LEN NUMBER
kksbvnnam -- BIND_NAME VARCHAR2(30)
from
x$kksbv
GV$SQL_CURSOR
select
inst_id, -- INST_ID NUMBER
kxscccur, -- CURNO NUMBER
kxscccfl, -- FLAG NUMBER
decode -- STATUS VARCHAR2(9)
(kxsccsta, 0, 'CURNULL', 1, 'CURSYNTAX', 2, 'CURPARSE', 3, 'CURBOUND', 4, 'CURFETCH', 5, 'CURROW', 'ERROR'),
kxsccphd, -- PARENT_HANDLE RAW(4)
kxsccplk, -- PARENT_LOCK RAW(4)
kxsccclk, -- CHILD_LOCK RAW(4)
kxscccpn, -- CHILD_PIN RAW(4)
kxscctbm, -- PERS_HEAP_MEM NUMBER
kxscctwm, -- WORK_HEAP_MEM NUMBER
kxscctbv, -- BIND_VARS NUMBER
kxscctdv, -- DEFINE_VARS NUMBER
kxsccbdf, -- BIND_MEM_LOC VARCHAR2(64)
kxsccflg, -- INST_FLAG VARCHAR2(64)
kxsccfl2 -- INST_FLAG2 VARCHAR2(64)
from
x$kxscc
GV$SQL_SHARED_CURSOR
select
inst_id, -- INST_ID NUMBER
kglhdadr, -- ADDRESS RAW(4)
kglhdpar, -- KGLHDPAR RAW(4)
decode -- UNBOUND_CURSOR VARCHAR2(1)
(bitand (bitvector, POWER (2, 0)), POWER (2, 0), 'Y', 'N'),
decode -- SQL_TYPE_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 1)), POWER (2, 1), 'Y', 'N'),
decode -- OPTIMIZER_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 2)), POWER (2, 2), 'Y', 'N'),
decode -- OUTLINE_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 3)), POWER (2, 3), 'Y', 'N'),
decode -- STATS_ROW_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 4)), POWER (2, 4), 'Y', 'N'),
decode -- LITERAL_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 5)), POWER (2, 5), 'Y', 'N'),
decode -- SEC_DEPTH_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 6)), POWER (2, 6), 'Y', 'N'),
decode -- EXPLAIN_PLAN_CURSOR VARCHAR2(1)
(bitand (bitvector, POWER (2, 7)), POWER (2, 7), 'Y', 'N'),
decode -- BUFFERED_DML_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 8)), POWER (2, 8), 'Y', 'N'),
decode -- PDML_ENV_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 9)), POWER (2, 9), 'Y', 'N'),
decode -- INST_DRTLD_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 10)), POWER (2, 10), 'Y', 'N'),
decode -- SLAVE_QC_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 11)), POWER (2, 11), 'Y', 'N'),
decode -- TYPECHECK_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 12)), POWER (2, 12), 'Y', 'N'),
decode -- AUTH_CHECK_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 13)), POWER (2, 13), 'Y', 'N'),
decode -- BIND_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 14)), POWER (2, 14), 'Y', 'N'),
decode -- DESCRIBE_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 15)), POWER (2, 15), 'Y', 'N'),
decode -- LANGUAGE_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 16)), POWER (2, 16), 'Y', 'N'),
decode -- TRANSLATION_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 17)), POWER (2, 17), 'Y', 'N'),
decode -- ROW_LEVEL_SEC_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 18)), POWER (2, 18), 'Y', 'N'),
decode -- INSUFF_PRIVS VARCHAR2(1)
(bitand (bitvector, POWER (2, 19)), POWER (2, 19), 'Y', 'N'),
decode -- INSUFF_PRIVS_REM VARCHAR2(1)
(bitand (bitvector, POWER (2, 20)), POWER (2, 20), 'Y', 'N'),
decode -- REMOTE_TRANS_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 21)), POWER (2, 21), 'Y', 'N'),
decode -- SQL_REDIRECT_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 22)), POWER (2, 22), 'Y', 'N')
from
x$kkscs
GV$SQL_SHARED_MEMORY
select
/*+use_nl (h, c) */
c.inst_id, -- INST_ID NUMBER
kglnaobj, -- SQL_TEXT VARCHAR2(1000)
kglnahsh, -- HASH_VALUE NUMBER
kglobhd6, -- HEAP_DESC RAW(4)
rtrim -- STRUCTURE VARCHAR2(16)
(substr (ksmchcom, 1, instr (ksmchcom, ':', 1, 1) - 1)),
ltrim -- FUNCTION VARCHAR2(16)
(substr (ksmchcom, - (length (ksmchcom) - (instr (ksmchcom, ':', 1, 1))), (length (ksmchcom) - (instr (ksmchcom, ':', 1, 1)) + 1))),
ksmchcom, -- CHUNK_COM VARCHAR2(16)
ksmchptr, -- CHUNK_PTR RAW(4)
ksmchsiz, -- CHUNK_SIZE NUMBER
ksmchcls, -- ALLOC_CLASS VARCHAR2(8)
ksmchtyp, -- CHUNK_TYPE NUMBER
ksmchpar -- SUBHEAP_DESC RAW(4)
from
x$kglcursor c,
x$ksmhp h
where
ksmchds = kglobhd6 and kglhdadr != kglhdpar
GV$STATNAME
select
inst_id, -- INST_ID NUMBER
indx, -- STATISTIC# NUMBER
ksusdnam, -- NAME VARCHAR2(64)
ksusdcls -- CLASS NUMBER
from
x$ksusd
GV$SUBCACHE
select
inst_id, -- INST_ID NUMBER
kglnaown, -- OWNER_NAME VARCHAR2(64)
kglnaobj, -- NAME VARCHAR2(1000)
kglobtyp, -- TYPE NUMBER
kqlfshpn, -- HEAP_NUM NUMBER
kqlfscid, -- CACHE_ID NUMBER
kqlfsscc, -- CACHE_CNT NUMBER
kqlfsesp, -- HEAP_SZ NUMBER
kqlfsasp, -- HEAP_ALOC NUMBER
kqlfsusp -- HEAP_USED NUMBER
from
x$kqlset
GV$SYSSTAT
select
inst_id, -- INST_ID NUMBER
indx, -- STATISTIC# NUMBER
ksusdnam, -- NAME VARCHAR2(64)
ksusdcls, -- CLASS NUMBER
ksusgstv -- VALUE NUMBER
from
x$ksusgsta
GV$SYSTEM_CURSOR_CACHE
select
inst_id, -- INST_ID NUMBER
kgicsopn, -- OPENS NUMBER
kgicshit, -- HITS NUMBER
decode -- HIT_RATIO NUMBER
(kgicsopn, 0, 1, kgicshit/kgicsopn)
from
x$kgics
GV$SYSTEM_EVENT
select
d.inst_id, -- INST_ID NUMBER
d.kslednam, -- EVENT VARCHAR2(64)
s.ksleswts, -- TOTAL_WAITS NUMBER
s.kslestmo, -- TOTAL_TIMEOUTS NUMBER
s.kslestim, -- TIME_WAITED NUMBER
s.kslestim / s.ksleswts -- AVERAGE_WAIT NUMBER
from
x$kslei s,
x$ksled d
where
s.ksleswts != 0 and s.indx = d.indx
GV$SYSTEM_PARAMETER
select
x.inst_id, -- INST_ID NUMBER
x.indx+1, -- NUM NUMBER
ksppinm, -- NAME VARCHAR2(64)
ksppity, -- TYPE NUMBER
ksppstvl, -- VALUE VARCHAR2(512)
ksppstdf, -- ISDEFAULT VARCHAR2(9)
decode -- ISSES_MODIFIABLE VARCHAR2(5)
(bitand (ksppiflg/256, 1), 1, 'TRUE', 'FALSE'),
decode -- ISSYS_MODIFIABLE VARCHAR2(9)
(bitand (ksppiflg/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE'),
decode -- ISMODIFIED VARCHAR2(8)
(bitand (ksppstvf, 7), 1, 'MODIFIED', 'FALSE'),
decode -- ISADJUSTED VARCHAR2(5)
(bitand (ksppstvf, 2), 2, 'TRUE', 'FALSE'),
ksppdesc -- DESCRIPTION VARCHAR2(64)
from
x$ksppi x,
x$ksppsv y
where
(x.indx = y.indx) and ((translate (ksppinm,
'_',
'#') not like '#%') or (ksppstdf = 'FALSE'))
GV$SYSTEM_PARAMETER2
select
x.inst_id, -- INST_ID NUMBER
kspftctxpn, -- NUM NUMBER
ksppinm, -- NAME VARCHAR2(64)
ksppity, -- TYPE NUMBER
kspftctxvl, -- VALUE VARCHAR2(512)
kspftctxdf, -- ISDEFAULT VARCHAR2(6)
decode -- ISSES_MODIFIABLE VARCHAR2(5)
(bitand (ksppiflg/256, 1), 1, 'TRUE', 'FALSE'),
decode -- ISSYS_MODIFIABLE VARCHAR2(9)
(bitand (ksppiflg/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE'),
decode -- ISMODIFIED VARCHAR2(8)
(bitand (kspftctxvf, 7), 1, 'MODIFIED', 'FALSE'),
decode -- ISADJUSTED VARCHAR2(5)
(bitand (kspftctxvf, 2), 2, 'TRUE', 'FALSE'),
ksppdesc -- DESCRIPTION VARCHAR2(64)
from
x$ksppi x,
x$ksppsv2 y
where
((x.indx+1) = kspftctxpn) and ((translate (ksppinm,
'_',
'#') not like '#%') or (kspftctxdf = 'FALSE'))
GV$TABLESPACE
select
inst_id, -- INST_ID NUMBER
tstsn, -- TS# NUMBER
tsnam -- NAME VARCHAR2(30)
from
x$kccts
where
tstsn != -1
GV$TARGETRBA
select
INST_ID, -- INST_ID NUMBER
LOGFILESZ, -- LOG_FILE_SIZE NUMBER
LOGINTERVAL, -- LOG_CKPT_INTERVAL NUMBER
INC_EST_RCV_READS, -- INC_EST_RCV_READS NUMBER
CUR_EST_RCV_READS, -- CUR_EST_RCV_READS NUMBER
TARGET_SEQ, -- TARGET_RBA_SEQ NUMBER
TARGET_BLK, -- TARGET_RBA_BLK NUMBER
ODRBA_SEQ, -- ON_DISK_RBA_SEQ NUMBER
ODRBA_BLK, -- ON_DISK_RBA_BLK NUMBER
INC_CKP_SEQ, -- INC_CKPT_SEQ NUMBER
INC_CKP_BLK, -- INC_CKPT_BLK NUMBER
TIMEOUT_SEQ, -- LOG_TIMEOUT_SEQ NUMBER
TIMEOUT_BLK, -- LOG_TIMEOUT_BLK NUMBER
RCV_READ_SEQ, -- RCV_READS_RBA_SEQ NUMBER
RCV_READ_BLK -- RCV_READS_RBA_BLK NUMBER
from
X$TARGETRBA
GV$TEMPFILE
select
tf.inst_id, -- INST_ID NUMBER
tf.tfnum, -- FILE# NUMBER
to_number -- CREATION_CHANGE# NUMBER
(tf.tfcrc_scn),
to_date -- CREATION_TIME DATE
(tf.tfcrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
tf.tftsn, -- TS# NUMBER
tf.tfrfn, -- RFILE# NUMBER
decode -- STATUS VARCHAR2(7)
(bitand (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
decode -- ENABLED VARCHAR2(10)
(bitand (tf.tfsta, 12), 0, 'DISABLED', 4, 'READ ONLY', 12, 'READ WRITE', 'UNKNOWN'),
fh.fhtmpfsz*tf.tfbsz, -- BYTES NUMBER
fh.fhtmpfsz, -- BLOCKS NUMBER
tf.tfcsz*tf.tfbsz, -- CREATE_BYTES NUMBER
tf.tfbsz, -- BLOCK_SIZE NUMBER
fn.fnnam -- NAME VARCHAR2(513)
from
x$kcctf tf,
x$kccfn fn,
x$kcvfhtmp fh
where
fn.fnfno=tf.tfnum and fn.fnfno=fh.htmpxfil and tf.tffnh=fn.fnnum and tf.tfdup!=0 and fn.fntyp=7 and fn.fnnam is not null
GV$TEMPORARY_LOBS
select
inst_id, -- INST_ID NUMBER
kdltsno, -- SID NUMBER
kdltctmp, -- CACHE_LOBS NUMBER
kdltnctmp -- NOCACHE_LOBS NUMBER
from
X$KDLT order by kdltsno
GV$TEMPSTAT
select
k.inst_id, -- INST_ID NUMBER
k.kcftiofno, -- FILE# NUMBER
k.kcftiopyr, -- PHYRDS NUMBER
k.kcftiopyw, -- PHYWRTS NUMBER
k.kcftiopbr, -- PHYBLKRD NUMBER
k.kcftiopbw, -- PHYBLKWRT NUMBER
k.kcftioprt, -- READTIM NUMBER
k.kcftiopwt, -- WRITETIM NUMBER
k.kcftioavg, -- AVGIOTIM NUMBER
k.kcftiolst, -- LSTIOTIM NUMBER
k.kcftiomin, -- MINIOTIM NUMBER
k.kcftiormx, -- MAXIORTM NUMBER
k.kcftiowmx -- MAXIOWTM NUMBER
from
x$kcftio k,
x$kcctf f
where
f.tfdup <> 0 and f.tfnum=k.kcftiofno
GV$TEMP_EXTENT_MAP
select
/*+ ordered use_nl (me) */
me.inst_id, -- INST_ID NUMBER
ts.name, -- TABLESPACE_NAME VARCHAR2(30)
me.ktftmetfno, -- FILE_ID NUMBER
me.ktftmebno, -- BLOCK_ID NUMBER
me.ktftmeblks*ts.blocksize, -- BYTES NUMBER
me.ktftmeblks, -- BLOCKS NUMBER
me.ktftmeinst, -- OWNER NUMBER
me.ktftmefno -- RELATIVE_FNO NUMBER
from
ts$ ts,
x$ktftme me
where
ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = me.ktftmetsn
GV$TEMP_EXTENT_POOL
select
/*+ ordered use_nl (fc) */
fc.inst_id, -- INST_ID NUMBER
ts.name, -- TABLESPACE_NAME VARCHAR2(30)
fc.ktstfctfno, -- FILE_ID NUMBER
fc.ktstfcec, -- EXTENTS_CACHED NUMBER
fc.ktstfceu, -- EXTENTS_USED NUMBER
fc.ktstfcbc, -- BLOCKS_CACHED NUMBER
fc.ktstfcbu, -- BLOCKS_USED NUMBER
fc.ktstfcbc*ts.blocksize, -- BYTES_CACHED NUMBER
fc.ktstfcbu*ts.blocksize, -- BYTES_USED NUMBER
fc.ktstfcfno -- RELATIVE_FNO NUMBER
from
ts$ ts,
x$ktstfc fc
where
ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = fc.ktstfctsn
GV$TEMP_PING
select
x.inst_id, -- INST_ID NUMBER
kcftiofno, -- FILE_NUMBER NUMBER
0, -- FREQUENCY NUMBER
KCFTIOX2NC, -- X_2_NULL NUMBER
KCFTIOX2NFWC, -- X_2_NULL_FORCED_WRITE NUMBER
KCFTIOX2NFSC, -- X_2_NULL_FORCED_STALE NUMBER
KCFTIOX2SC, -- X_2_S NUMBER
KCFTIOX2SFWC, -- X_2_S_FORCED_WRITE NUMBER
0, -- X_2_SSX NUMBER
0, -- X_2_SSX_FORCED_WRITE NUMBER
KCFTIOS2NC, -- S_2_NULL NUMBER
KCFTIOS2NFSC, -- S_2_NULL_FORCED_STALE NUMBER
0, -- SS_2_NULL NUMBER
0, -- SS_2_RLS NUMBER
0, -- WRB NUMBER
0, -- WRB_FORCED_WRITE NUMBER
KCFTIORBRC, -- RBR NUMBER
KCFTIORBRFWC, -- RBR_FORCED_WRITE NUMBER
0, -- RBR_FORCED_STALE NUMBER
0, -- CBR NUMBER
0, -- CBR_FORCED_WRITE NUMBER
KCFTION2XC, -- NULL_2_X NUMBER
KCFTIOS2XC, -- S_2_X NUMBER
0, -- SSX_2_X NUMBER
KCFTION2SC, -- NULL_2_S NUMBER
0, -- NULL_2_SS NUMBER
0 -- OP_2_SS NUMBER
from
x$kcftio x,
x$kcctf tf
where
x.kcftiofno = tf.tfnum
GV$TEMP_SPACE_HEADER
select
/*+ ordered use_nl (hc) */
hc.inst_id, -- INST_ID NUMBER
ts.name, -- TABLESPACE_NAME VARCHAR2(30)
hc.ktfthctfno, -- FILE_ID NUMBER
(hc.ktfthcsz -- BYTES_USED NUMBER
- hc.ktfthcfree) *ts.blocksize,
(hc.ktfthcsz -- BLOCKS_USED NUMBER
- hc.ktfthcfree),
hc.ktfthcfree*ts.blocksize, -- BYTES_FREE NUMBER
hc.ktfthcfree, -- BLOCKS_FREE NUMBER
hc.ktfthcfno -- RELATIVE_FNO NUMBER
from
ts$ ts,
x$ktfthc hc
where
ts.contents$ = 1 and ts.bitmapped <> 0 and ts.online$ = 1 and ts.ts# = hc.ktfthctsn and hc.ktfthccval = 0
GV$THREAD
select
inst_id, -- INST_ID NUMBER
rtnum, -- THREAD# NUMBER
decode -- STATUS VARCHAR2(6)
(bitand (rtsta, 1), 1, 'OPEN', 'CLOSED'),
decode -- ENABLED VARCHAR2(8)
(bitand (rtsta, 6), 0, 'DISABLED', 2, 'PRIVATE', 6, 'PUBLIC', 'UNKNOWN'),
rtnlf, -- GROUPS NUMBER
rtsid, -- INSTANCE VARCHAR2(16)
to_date -- OPEN_TIME DATE
(rtots, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
rtcln, -- CURRENT_GROUP# NUMBER
rtseq, -- SEQUENCE# NUMBER
to_number (rtckp_scn), -- CHECKPOINT_CHANGE# NUMBER
to_date -- CHECKPOINT_TIME DATE
(rtckp_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (rtenb), -- ENABLE_CHANGE# NUMBER
to_date -- ENABLE_TIME DATE
(rtets, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (rtdis), -- DISABLE_CHANGE# NUMBER
to_date -- DISABLE_TIME DATE
(rtdit, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kccrt
where
rtnlf!=0
GV$TIMER
select
inst_id, -- INST_ID NUMBER
ksutmtim -- HSECS NUMBER
from
x$ksutm
GV$TRANSACTION
select
inst_id, -- INST_ID NUMBER
ktcxbxba, -- ADDR RAW(4)
kxidusn, -- XIDUSN NUMBER
kxidslt, -- XIDSLOT NUMBER
kxidsqn, -- XIDSQN NUMBER
ktcxbkfn, -- UBAFIL NUMBER
kubablk, -- UBABLK NUMBER
kubaseq, -- UBASQN NUMBER
kubarec, -- UBAREC NUMBER
decode -- STATUS VARCHAR2(16)
(ktcxbsta, 0, 'IDLE', 1, 'COLLECTING', 2, 'PREPARED', 3, 'COMMITTED', 4, 'HEURISTIC ABORT', 5, 'HEURISTIC COMMIT', 6, 'HEURISTIC DAMAGE', 7, 'TIMEOUT', 9, 'INACTIVE', 10, 'ACTIVE', 11, 'PTX PREPARED', 12, 'PTX COMMITTED', 'UNKNOWN'),
ktcxbstm, -- START_TIME VARCHAR2(20)
ktcxbssb, -- START_SCNB NUMBER
ktcxbssw, -- START_SCNW NUMBER
ktcxbsen, -- START_UEXT NUMBER
ktcxbsfl, -- START_UBAFIL NUMBER
ktcxbsbk, -- START_UBABLK NUMBER
ktcxbssq, -- START_UBASQN NUMBER
ktcxbsrc, -- START_UBAREC NUMBER
ktcxbses, -- SES_ADDR RAW(4)
ktcxbflg, -- FLAG NUMBER
decode -- SPACE VARCHAR2(3)
(bitand (ktcxbflg, 16), 0, 'NO', 'YES'),
decode -- RECURSIVE VARCHAR2(3)
(bitand (ktcxbflg, 32), 0, 'NO', 'YES'),
decode -- NOUNDO VARCHAR2(3)
(bitand (ktcxbflg, 64), 0, 'NO', 'YES'),
decode -- PTX VARCHAR2(3)
(bitand (ktcxbflg, 8388608), 0, 'NO', 'YES'),
ktcxbpus, -- PRV_XIDUSN NUMBER
ktcxbpsl, -- PRV_XIDSLT NUMBER
ktcxbpsq, -- PRV_XIDSQN NUMBER
ktcxbpxu, -- PTX_XIDUSN NUMBER
ktcxbpxs, -- PTX_XIDSLT NUMBER
ktcxbpxq, -- PTX_XIDSQN NUMBER
ktcxbdsb, -- DSCN-B NUMBER
ktcxbdsw, -- DSCN-W NUMBER
ktcxbubk, -- USED_UBLK NUMBER
ktcxburc, -- USED_UREC NUMBER
ktcxblio, -- LOG_IO NUMBER
ktcxbpio, -- PHY_IO NUMBER
ktcxbcrg, -- CR_GET NUMBER
ktcxbcrc -- CR_CHANGE NUMBER
from
x$ktcxb
where
bitand (ksspaflg, 1) !=0 and bitand (ktcxbflg, 2) !=0
GV$TRANSACTION_ENQUEUE
select
/*+ ordered use_nl (l), use_nl (s), use_nl (r) +*/
s.inst_id, -- INST_ID NUMBER
l.addr, -- ADDR RAW(4)
l.ksqlkadr, -- KADDR RAW(4)
s.ksusenum, -- SID NUMBER
r.ksqrsidt, -- TYPE VARCHAR2(2)
r.ksqrsid1, -- ID1 NUMBER
r.ksqrsid2, -- ID2 NUMBER
l.ksqlkmod, -- LMODE NUMBER
l.ksqlkreq, -- REQUEST NUMBER
l.ksqlkctim, -- CTIME NUMBER
l.ksqlklblk -- BLOCK NUMBER
from
x$ktcxb l,
x$ksuse s,
x$ksqrs r
where
l.ksqlkses=s.addr and bitand (l.ksspaflg, 1) !=0 and (l.ksqlkmod!=0 or l.ksqlkreq!=0) and l.ksqlkres=r.addr
GV$TYPE_SIZE
select
inst_id, -- INST_ID NUMBER
kqfszcom, -- COMPONENT VARCHAR2(8)
kqfsztyp, -- TYPE VARCHAR2(8)
kqfszdsc, -- DESCRIPTION VARCHAR2(32)
kqfszsiz -- TYPE_SIZE NUMBER
from
x$kqfsz
GV$VERSION
select
inst_id, -- INST_ID NUMBER
banner -- BANNER VARCHAR2(64)
from
x$version
GV$WAITSTAT
select
inst_id, -- INST_ID NUMBER
decode -- CLASS VARCHAR2(18)
(indx, 1, 'data block', 2, 'sort block', 3, 'save undo block', 4, 'segment header', 5, 'save undo header', 6, 'free list', 7, 'extent map', 8, 'bitmap block', 9, 'bitmap index block', 10, 'unused', 11, 'system undo header', 12, 'system undo block', 13, 'undo header', 14, 'undo block'),
count, -- COUNT NUMBER
time -- TIME NUMBER
from
x$kcbwait
where
indx!=0
GV$_LOCK
select
USERENV ('Instance'), -- INST_ID NUMBER
laddr, -- LADDR RAW(4)
kaddr, -- KADDR RAW(4)
saddr, -- SADDR RAW(4)
raddr, -- RADDR RAW(4)
lmode, -- LMODE NUMBER
request, -- REQUEST NUMBER
ctime, -- CTIME NUMBER
block -- BLOCK NUMBER
from
v$_lock1
union all
select
inst_id, -- INST_ID NUMBER
addr, -- LADDR RAW(4)
ksqlkadr, -- KADDR RAW(4)
ksqlkses, -- SADDR RAW(4)
ksqlkres, -- RADDR RAW(4)
ksqlkmod, -- LMODE NUMBER
ksqlkreq, -- REQUEST NUMBER
ksqlkctim, -- CTIME NUMBER
ksqlklblk -- BLOCK NUMBER
from
x$ktadm
where
bitand (kssobflg, 1) !=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select
inst_id, -- INST_ID NUMBER
addr, -- LADDR RAW(4)
ksqlkadr, -- KADDR RAW(4)
ksqlkses, -- SADDR RAW(4)
ksqlkres, -- RADDR RAW(4)
ksqlkmod, -- LMODE NUMBER
ksqlkreq, -- REQUEST NUMBER
ksqlkctim, -- CTIME NUMBER
ksqlklblk -- BLOCK NUMBER
from
x$ktcxb
where
bitand (ksspaflg, 1) !=0 and (ksqlkmod!=0 or ksqlkreq!=0)
GV$_LOCK1
select
inst_id, -- INST_ID NUMBER
addr, -- LADDR RAW(4)
ksqlkadr, -- KADDR RAW(4)
ksqlkses, -- SADDR RAW(4)
ksqlkres, -- RADDR RAW(4)
ksqlkmod, -- LMODE NUMBER
ksqlkreq, -- REQUEST NUMBER
ksqlkctim, -- CTIME NUMBER
ksqlklblk -- BLOCK NUMBER
from
x$kdnssf
where
bitand (kssobflg, 1) !=0 and (ksqlkmod!=0 or ksqlkreq!=0)
union all
select
inst_id, -- INST_ID NUMBER
addr, -- LADDR RAW(4)
ksqlkadr, -- KADDR RAW(4)
ksqlkses, -- SADDR RAW(4)
ksqlkres, -- RADDR RAW(4)
ksqlkmod, -- LMODE NUMBER
ksqlkreq, -- REQUEST NUMBER
ksqlkctim, -- CTIME NUMBER
ksqlklblk -- BLOCK NUMBER
from
x$ksqeq
where
bitand (kssobflg, 1) !=0 and (ksqlkmod!=0 or ksqlkreq!=0)
GV$_SEQUENCES
select
inst_id, -- INST_ID NUMBER
KGLNAOWN, -- SEQUENCE_OWNER VARCHAR2(64)
KGLNAOBJ, -- SEQUENCE_NAME VARCHAR2(1000)
KGLOBT08, -- OBJECT# NUMBER
decode -- ACTIVE_FLAG VARCHAR2(1)
(bitand (KGLOBT00, 1), 0, 'N', 'Y'),
decode -- REPLENISH_FLAG VARCHAR2(1)
(bitand (KGLOBT00, 2), 0, 'N', 'Y'),
decode -- WRAP_FLAG VARCHAR2(1)
(bitand (KGLOBT00, 16), 0, 'N', 'Y'),
KGLOBTN0, -- NEXTVALUE NUMBER
KGLOBTN2, -- MIN_VALUE NUMBER
KGLOBTN3, -- MAX_VALUE NUMBER
KGLOBTN1, -- INCREMENT_BY NUMBER
decode -- CYCLE_FLAG VARCHAR2(1)
(bitand (KGLOBT09, 1), 0, 'N', 'Y'),
decode -- ORDER_FLAG VARCHAR2(1)
(bitand (KGLOBT09, 2), 0, 'N', 'Y'),
KGLOBTN4, -- CACHE_SIZE NUMBER
KGLOBTN5, -- HIGHWATER NUMBER
decode -- BACKGROUND_INSTANCE_LOCK VARCHAR2(1)
(KGLOBT10, 1, 'Y', 'N'),
decode -- INSTANCE_LOCK_FLAGS NUMBER
(KGLOBT10, 1, KGLOBT02, null)
from
X$KGLOB
where
KGLOBTYP = 6 and KGLOBT11 = 1
mailbox for questions, comments and corrections