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$ACTIVE_SESS_POOL_MTH
select
inst_id, -- INST_ID NUMBER
policy_name_kgskasp -- NAME VARCHAR2(40)
from
x$kgskasp
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 -- ISCURRENT VARCHAR2(3)
(bitand (le.leflg, 8), 0, 'NO', 'YES'),
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)
aldst, -- DEST_ID NUMBER
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(7)
(bitand (alflg, 16+32+64+128+256), 16, 'ARCH', 32, 'FGRD', 64, 'RMAN', 128, 'SRMN', 256, 'LGWR', 'UNKNOWN'),
decode -- REGISTRAR VARCHAR2(7)
(bitand (alflg, 4), 4, 'RFS', decode (bitand (alflg, 16+32+64+128+256), 16, 'ARCH', 32, 'FGRD', 64, 'RMAN', 128, 'SRMN', 256, 'LGWR', 'UNKNOWN')),
decode -- STANDBY_DEST VARCHAR2(3)
(bitand (alflg, 8), 0, 'NO', 'YES'),
decode -- ARCHIVED VARCHAR2(3)
(bitand (alflg, 2), 0, 'NO', 'YES'),
decode -- APPLIED VARCHAR2(3)
(bitand (alflg, 1024), 0, 'NO', 'YES'),
decode -- DELETED VARCHAR2(3)
(bitand (alflg, 1), 0, 'NO', 'YES'),
decode -- STATUS VARCHAR2(1)
(bitand (alflg, 1+2048+4096), 0, 'A', 1, 'D', 2048, 'X', 4096, 'U', '?'),
to_date -- COMPLETION_TIME DATE
(altsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- DICTIONARY_BEGIN VARCHAR2(3)
(bitand (alflg, 8192), 0, 'NO', 'YES'),
decode -- DICTIONARY_END VARCHAR2(3)
(bitand (alflg, 16384), 0, 'NO', 'YES'),
decode -- END_OF_REDO VARCHAR2(3)
(bitand (alflg, 32768), 0, 'NO', 'YES'),
to_number -- BACKUP_COUNT NUMBER
(bitand (alfl2, 15)),
altoa, -- ARCHIVAL_THREAD# NUMBER
alacd -- ACTIVATION# NUMBER
from
x$kccal
GV$ARCHIVE_DEST
select
inst_id, -- INST_ID NUMBER
to_number (ADDID), -- DEST_ID NUMBER
ADDXX, -- DEST_NAME VARCHAR2(256)
decode -- STATUS VARCHAR2(9)
(ADSTS, 1, 'VALID', 2, 'INACTIVE', 3, 'DEFERRED', 4, 'ERROR', 5, 'DISABLED', 6, 'BAD PARAM', 7, 'ALTERNATE', 8, 'FULL', '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', 1, 'STANDBY', 2, 'LOCAL', 3, 'REMOTE', 'UNKNOWN'),
decode -- ARCHIVER VARCHAR2(10)
(ADPRC, 0, 'ARCH', 1, 'LGWR', 2, 'FOREGROUND', 3, 'RFS', 'UNKNOWN'),
decode -- SCHEDULE VARCHAR2(8)
(ADSCH, 0, 'INACTIVE', 1, 'PENDING', 2, 'ACTIVE', 3, 'LATENT', 'UNKNOWN'),
ADDNM, -- DESTINATION VARCHAR2(256)
to_number (ADLSQ), -- LOG_SEQUENCE NUMBER
to_number (ADROP), -- REOPEN_SECS NUMBER
to_number (ADDLY), -- DELAY_MINS NUMBER
to_number (ADNTT), -- NET_TIMEOUT NUMBER
decode -- PROCESS VARCHAR2(10)
(ADWHO, 0, 'ARCH', 1, 'LGWR', 2, 'FOREGROUND', 3, 'RFS', 'UNKNOWN'),
decode -- REGISTER VARCHAR2(3)
(ADREG, 0, 'NO', 'YES'),
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
to_number (ADFCT), -- FAILURE_COUNT NUMBER
to_number (ADMXF), -- MAX_FAILURE NUMBER
ADFER, -- ERROR VARCHAR2(256)
ADALT, -- ALTERNATE VARCHAR2(256)
ADDPD, -- DEPENDENCY VARCHAR2(256)
ADRFT, -- REMOTE_TEMPLATE VARCHAR2(256)
to_number (ADQSZ), -- QUOTA_SIZE NUMBER
to_number (ADQSD), -- QUOTA_USED NUMBER
ADMID, -- MOUNTID NUMBER
decode -- TRANSMIT_MODE VARCHAR2(12)
(ADLAB, 0, decode (ADPAR, 0, 'SYNCHRONOUS', 'PARALLELSYNC'), 'ASYNCHRONOUS'),
to_number (ADLAB), -- ASYNC_BLOCKS NUMBER
decode -- AFFIRM VARCHAR2(3)
(ADAFF, 0, 'NO', 'YES'),
decode -- TYPE VARCHAR2(7)
(ADDTG, 0, 'PUBLIC', 'PRIVATE')
from
x$kcrrdest
GV$ARCHIVE_DEST_STATUS
select
inst_id, -- INST_ID NUMBER
to_number (DSDID), -- DEST_ID NUMBER
DSDXX, -- DEST_NAME VARCHAR2(256)
decode -- STATUS VARCHAR2(9)
(DSSTS, 1, 'VALID', 2, 'INACTIVE', 3, 'DEFERRED', 4, 'ERROR', 5, 'DISABLED', 6, 'BAD PARAM', 'UNKNOWN'),
decode -- TYPE VARCHAR2(14)
(DSTYP, 1, 'LOCAL', 2, 'PHYSICAL', 3, 'LOGICAL', 4, 'CROSS-INSTANCE', 'UNKNOWN'),
decode -- DATABASE_MODE VARCHAR2(15)
(DSDMD, 1, 'STARTED', 2, 'MOUNTED', 3, 'MOUNTED-STANDBY', 4, 'OPEN', 5, 'OPEN_READ-ONLY', 'UNKNOWN'),
decode -- RECOVERY_MODE VARCHAR2(7)
(DSRMD, 1, 'IDLE', 2, 'MANUAL', 3, 'MANAGED', 'UNKNOWN'),
decode -- PROTECTION_MODE VARCHAR2(20)
(DSPRT, 0, 'MAXIMUM PERFORMANCE', 1, 'MAXIMUM PROTECTION', 2, 'MAXIMUM AVAILABILITY', 3, 'RESYNCHRONIZATION', 'UNKNOWN'),
DSDNM, -- DESTINATION VARCHAR2(256)
to_number (DSCNT), -- STANDBY_LOGFILE_COUNT NUMBER
to_number (DSACT), -- STANDBY_LOGFILE_ACTIVE NUMBER
to_number (DSLTA), -- ARCHIVED_THREAD# NUMBER
to_number (DSLSA), -- ARCHIVED_SEQ# NUMBER
to_number (DSLTR), -- APPLIED_THREAD# NUMBER
to_number (DSLSR), -- APPLIED_SEQ# NUMBER
DSERR, -- ERROR VARCHAR2(256)
decode -- SRL VARCHAR2(3)
(DSSRL, 0, 'NO', 'YES')
from
x$kcrrdstat
GV$ARCHIVE_GAP
select
USERENV ('Instance'), -- INST_ID NUMBER
high.thread#, -- THREAD# NUMBER
"LOW_SEQUENCE#", -- LOW_SEQUENCE# NUMBER
"HIGH_SEQUENCE#" -- HIGH_SEQUENCE# NUMBER
from
(
select
thread#,
min (sequence#) -1 "HIGH_SEQUENCE#"
from
(
select
a.thread#,
a.sequence#
from
(
select
thread#,
sequence#
from
v$archived_log) a,
(
select
thread#,
max (sequence#) gap1
from
v$log_history
group by thread#) b
where
a.thread# = b.thread# and a.sequence# > gap1)
group by thread#) high,
(
select
thread#,
min (gap2) "LOW_SEQUENCE#"
from
(
select
thread#,
sequence#+1 gap2
from
v$log_history h,
v$datafile d
where
checkpoint_change# <= next_change# and checkpoint_change# >= first_change# and enabled = 'READ WRITE')
group by thread#) low
where
low.thread# = high.thread# and "LOW_SEQUENCE#" <= "HIGH_SEQUENCE#" and "HIGH_SEQUENCE#" < (
select
MAX (sequence#)
from
v$archived_log)
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$AW_CALC
select
inst_id, -- INST_ID NUMBER
agcachhit, -- AGGREGATE_CACHE_HITS NUMBER
agcachmiss, -- AGGREGATE_CACHE_MISSES NUMBER
scachesuccess, -- SESSION_CACHE_HITS NUMBER
scachefailure, -- SESSION_CACHE_MISSES NUMBER
pgcachhit, -- POOL_HITS NUMBER
pgcachmiss, -- POOL_MISSES NUMBER
pgnewpage, -- POOL_NEW_PAGES NUMBER
pgscrounge, -- POOL_RECLAIMED_PAGES NUMBER
pgcachewrite, -- CACHE_WRITES NUMBER
pgpoolsize -- POOL_SIZE NUMBER
from
x$xsaggr
GV$AW_OLAP
select
a.inst_id, -- INST_ID NUMBER
s.ksusenum, -- SESSION_ID NUMBER
a.awnum, -- AW_NUMBER NUMBER
decode -- ATTACH_MODE VARCHAR2(10)
(mod (a.at_mode, 128), 1, 'READ WRITE', 'READ ONLY'),
a.gen_xsawso, -- GENERATION NUMBER
a.temp_lob_count, -- TEMP_SPACE_PAGES NUMBER
a.temp_lob_read, -- TEMP_SPACE_READS NUMBER
a.perm_lob_read, -- LOB_READS NUMBER
a.changed_cache, -- POOL_CHANGED_PAGES NUMBER
a.unchanged_cache -- POOL_UNCHANGED_PAGES NUMBER
from
x$ksuse s,
x$xsawso a
where
s.addr = a.KSSOBOWN and a.at_mode < 128
GV$AW_SESSION_INFO
select
inst_id, -- INST_ID NUMBER
client, -- CLIENT_TYPE VARCHAR2(64)
state, -- SESSION_STATE VARCHAR2(64)
sesshandle, -- SESSION_HANDLE NUMBER
userid, -- USERID VARCHAR2(64)
cdmlcmd, -- CURR_DML_COMMAND VARCHAR2(64)
pdmlcmd, -- PREV_DML_COMMAND VARCHAR2(64)
tottrns, -- TOTAL_TRANSACTION NUMBER
tottrntime, -- TOTAL_TRANSACTION_TIME NUMBER
avgtrntime, -- AVERAGE_TRANSACTION_TIME NUMBER
trncputime, -- TRANSACTION_CPU_TIME NUMBER
tottrncputime, -- TOTAL_TRANSACTION_CPU_TIME NUMBER
avgtrncputime -- AVERAGE_TRANSACTION_CPU_TIME NUMBER
from
x$xssinfo
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'),
decode -- CORRUPTION_TYPE VARCHAR2(9)
(bitand (fcflg, 30), 2, 'ALL ZERO', 4, 'FRACTURED', 8, 'CHECKSUM', 16, 'CORRUPT', 'LOGICAL')
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 + (bitand (bpext, 64-1) * 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+8192), 0, 'A', 1, 'D', 4096, 'X', 8192, 'U', '?'),
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'),
decode -- KEEP VARCHAR2(3)
(bitand (bstyp, 1792), 0, 'NO', 'YES'),
to_date -- KEEP_UNTIL DATE
(bskpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- KEEP_OPTIONS VARCHAR2(10)
(bitand (bstyp, 1792), 256, 'LOGS', 512, 'NOLOGS', 1024, 'CONSISTENT', NULL)
from
x$kccbs
GV$BACKUP_SPFILE
select
inst_id, -- INST_ID NUMBER
birid, -- RECID NUMBER
bistm, -- STAMP NUMBER
bibss, -- SET_STAMP NUMBER
bibsc, -- SET_COUNT NUMBER
to_date -- MODIFICATION_TIME DATE
(bimdt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
bifsz, -- BYTES NUMBER
to_date -- COMPLETION_TIME DATE
(bitsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kccbi
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(5)
(state, 0, 'free', 1, 'xcur', 2, 'scur', 3, 'cr', 4, 'read', 5, 'mrec', 6, 'irec', 7, 'write', 8, 'pi'),
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_blksz, -- BLOCK_SIZE NUMBER
decode -- RESIZE_STATE VARCHAR2(10)
(bp_state, 0, 'STATIC', 1, 'ALLOCATING', 2, 'ACTIVATING', 3, 'SHRINKING'),
bp_currgrans -- CURRENT_SIZE NUMBER
* bp_gransz,
bp_size, -- BUFFERS NUMBER
bp_tgtgrans * bp_gransz, -- TARGET_SIZE NUMBER
bp_tgtgrans -- TARGET_BUFFERS NUMBER
* bp_bufpergran,
bp_prevgrans -- PREV_SIZE NUMBER
* bp_gransz,
bp_prevgrans -- PREV_BUFFERS NUMBER
* bp_bufpergran,
0, -- LO_BNUM NUMBER
0, -- HI_BNUM NUMBER
bp_lo_sid, -- LO_SETID NUMBER
bp_hi_sid, -- HI_SETID NUMBER
bp_set_ct -- SET_COUNT NUMBER
from
x$kcbwbpd
where
bp_id > 0 and bp_currgrans > 0 and bp_tgtgrans > 0
GV$BUFFER_POOL_STATISTICS
select
kcbwbpd.inst_id, -- INST_ID NUMBER
kcbwbpd.bp_id, -- ID NUMBER
kcbwbpd.bp_name, -- NAME VARCHAR2(20)
kcbwbpd.bp_blksz, -- BLOCK_SIZE NUMBER
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
sum (kcbwds.dbbchg), -- DB_BLOCK_CHANGE NUMBER
sum (kcbwds.dbbget), -- DB_BLOCK_GETS NUMBER
sum (kcbwds.conget), -- 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,
kcbwbpd.bp_blksz
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(256)
kmcvcpvc -- PCIRCUIT RAW(4)
from
x$kmcvc
where
bitand (ksspaflg, 1) != 0
GV$CLASS_CACHE_TRANSFER
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, '1st level bmb', 9, '2nd level bmb', 10, '3rd level bmb', 11, 'bitmap block', 12, 'bitmap index block', 13, 'unused', 14, 'undo header', 15, '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
CLASS_S2NC, -- S_2_NULL NUMBER
CLASS_S2NFSC, -- S_2_NULL_FORCED_STALE NUMBER
CLASS_N2XC, -- NULL_2_X NUMBER
CLASS_S2XC, -- S_2_X NUMBER
CLASS_N2SC -- NULL_2_S NUMBER
from
x$class_stat
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, '1st level bmb', 9, '2nd level bmb', 10, '3rd level bmb', 11, 'bitmap block', 12, 'bitmap index block', 13, 'unused', 14, 'undo header', 15, '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(31)
attribute, -- ATTRIBUTE VARCHAR2(31)
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(20)
(indx, 0, 'DATABASE', 1, 'CKPT PROGRESS', 2, 'REDO THREAD', 3, 'REDO LOG', 4, 'DATAFILE', 5, 'FILENAME', 6, 'TABLESPACE', 7, 'TEMPORARY FILENAME', 8, 'RMAN CONFIGURATION', 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, 'BACKUP SPFILE', 23, 'DATABASE INCARNATION', '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
where
indx not in (22)
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'),
decode -- CORRUPTION_TYPE VARCHAR2(9)
(bitand (ccflg, 30), 2, 'ALL ZERO', 4, 'FRACTURED', 8, 'CHECKSUM', 16, 'CORRUPT', 'LOGICAL')
from
x$kcccc
GV$CR_BLOCK_SERVER
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_GC_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$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')),
decode -- PROTECTION_MODE VARCHAR2(20)
(bitand (diflg, 65536), 65536, 'MAXIMUM PROTECTION', decode (bitand (diflg, 128), 128, 'MAXIMUM AVAILABILITY', decode (bitand (diflg, 134217728), 134217728, 'RESYNCHRONIZATION', decode (bitand (diflg, 8), 8, 'UNPROTECTED', 'MAXIMUM PERFORMANCE')))),
decode -- PROTECTION_LEVEL VARCHAR2(20)
(diprt, 1, 'MAXIMUM PROTECTION', 2, 'MAXIMUM AVAILABILITY', 3, 'RESYNCHRONIZATION', 4, 'MAXIMUM PERFORMANCE', 5, 'UNPROTECTED', 'UNKNOWN'),
decode -- REMOTE_ARCHIVE VARCHAR2(8)
(dirae, 0, 'DISABLED', 1, 'SEND', 2, 'RECEIVE', 3, 'ENABLED', 'UNKNOWN'),
to_number (diacid), -- ACTIVATION# NUMBER
decode -- DATABASE_ROLE VARCHAR2(16)
(bitand (diflg, 33554432), 33554432, 'LOGICAL STANDBY', decode (bitand (diflg, 1024), 1024, 'PHYSICAL STANDBY', 'PRIMARY')),
to_number (diars), -- ARCHIVELOG_CHANGE# NUMBER
decode -- SWITCHOVER_STATUS VARCHAR2(18)
(disos, 0, 'IMPOSSIBLE', 1, 'NOT ALLOWED', 2, 'SWITCHOVER LATENT', 3, 'SWITCHOVER PENDING', 4, 'TO PRIMARY', 5, 'TO STANDBY', 6, 'RECOVERY NEEDED', 7, 'SESSIONS ACTIVE', 'UNKNOWN'),
decode -- DATAGUARD_BROKER VARCHAR2(8)
(didgd, 0, 'DISABLED', 'ENABLED'),
decode -- GUARD_STATUS VARCHAR2(7)
(bitand (diflg, 1048576), 1048576, 'ALL', decode (bitand (diflg, 2097152), 2097152, 'STANDBY', 'NONE')),
decode -- SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(3)
(bitand (diflg, 1073741824), 1073741824, 'YES', 'NO'),
decode -- SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
(bitand (diflg, 131072), 131072, 'YES', 'NO'),
decode -- SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
(bitand (diflg, 262144), 262144, 'YES', 'NO'),
decode -- FORCE_LOGGING VARCHAR2(3)
(bitand (diflg, 268435456), 268435456, 'YES', 'NO')
from
x$kccdi
GV$DATABASE_BLOCK_CORRUPTION
select
distinct -- INST_ID NUMBER
userenv ('Instance'),
file#, -- FILE# NUMBER
block#, -- BLOCK# NUMBER
blocks, -- BLOCKS NUMBER
corruption_change#, -- CORRUPTION_CHANGE# NUMBER
corruption_type -- CORRUPTION_TYPE VARCHAR2(9)
from
(
select
file#,
block#,
blocks,
corruption_change#,
copy_stamp stamp,
corruption_type
from
v$copy_corruption
union select
file#,
block#,
blocks,
corruption_change#,
bs.stamp,
corruption_type
from
v$backup_corruption bc,
v$backup_set bs
where
bc.set_count = bs.set_count and bc.set_stamp = bs.set_stamp) outer
where
not exists (
select
1
from
v$datafile_copy
where
scanned = 'YES' and outer.file# = file# and outer.stamp < stamp
union select
1
from
v$backup_datafile bdf,
v$backup_set bs
where
bdf.set_count = bs.set_count and bdf.set_stamp = bs.set_stamp and outer.file# = file# and outer.stamp < bs.stamp)
GV$DATABASE_INCARNATION
select
userenv ('Instance'), -- INST_ID NUMBER
to_number (icrls), -- RESETLOGS_CHANGE# NUMBER
to_date -- RESETLOGS_TIME DATE
(icrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (icprs), -- PRIOR_RESETLOGS_CHANGE# NUMBER
to_date -- PRIOR_RESETLOGS_TIME DATE
(icprc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kccic
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
fn.fnbof, -- BLOCK1_OFFSET NUMBER
decode -- AUX_NAME VARCHAR2(513)
(fe.fepax, 0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam)
from
x$kccfe fe,
x$kccfn fn,
x$kccfn fnaux,
x$kcvfh fh
where
((fe.fepax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum) or ((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno and fnaux.fntyp=4 and fnaux.fnnam is not null and fnaux.fnfno=fh.hxfil and fe.fefnh=fnaux.fnnum)) and 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'),
decode -- STATUS VARCHAR2(1)
(bitand (dcflg, 1+32+64), 0, 'A', 1, 'D', 32, 'X', 64, 'U', '?'),
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),
decode -- KEEP VARCHAR2(3)
(bitand (dcflg, 1792), 0, 'NO', 'YES'),
to_date -- KEEP_UNTIL DATE
(dckpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- KEEP_OPTIONS VARCHAR2(10)
(bitand (dcflg, 1792), 256, 'LOGS', 512, 'NOLOGS', 1024, 'CONSISTENT', NULL),
decode -- SCANNED VARCHAR2(3)
(bitand (dcflg, 128), 0, 'NO', 'YES')
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$DATAGUARD_STATUS
select
inst_id, -- INST_ID NUMBER
decode -- FACILITY VARCHAR2(24)
(agfac, 1, 'Crash Recovery', 2, 'Log Transport Services', 3, 'Log Apply Services', 4, 'Role Management Services', 5, 'Remote File Server', 6, 'Fetch Archive Log', 7, 'Data Guard', 8, 'Network Services', 'UNKNOWN'),
decode -- SEVERITY VARCHAR2(13)
(agsev, 1, 'Informational', 2, 'Warning', 3, 'Error', 4, 'Fatal', 5, 'Control', 'UNKNOWN'),
agdid, -- DEST_ID NUMBER
agseq, -- MESSAGE_NUM NUMBER
agoer, -- ERROR_CODE NUMBER
decode -- CALLOUT VARCHAR2(3)
(bitand (agflg, 1), 0, 'NO', 'YES'),
to_date -- TIMESTAMP DATE
(agdat, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
agtxt -- MESSAGE VARCHAR2(256)
from
x$kcrralg order by agseq
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.blksz, -- BLOCK_SIZE NUMBER
decode -- ADVICE_STATUS VARCHAR2(3)
(A.status, 2, 'ON', 'OFF'),
A.poolsz, -- SIZE_FOR_ESTIMATE NUMBER
round -- SIZE_FACTOR NUMBER
((A.poolsz / A.actual_poolsz), 4),
A.nbufs, -- BUFFERS_FOR_ESTIMATE NUMBER
decode -- ESTD_PHYSICAL_READ_FACTOR NUMBER
(A.base_preads, 0, to_number (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 order by A.inst_id,
A.bpid,
A.poolsz
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', 30, 'REMOTE OBJECT', 31, 'SNAPSHOT METADATA', 32, 'JAVA SHARED DATA', 33, 'SECURITY PROFILE', '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', 53, 'REMOTE OBJECT', 54, 'SNAPSHOT METADATA', 55, 'IFS', 56, 'JAVA SHARED DATA', 57, 'SECURITY PROFILE', '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'),
kglhdclt -- CHILD_LATCH NUMBER
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(26)
(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', 251, 'BACKUP PIECE UNAVAILABLE', 250, 'PROXY COPY UNAVAILABLE', 249, 'DATAFILE COPY AVAILABLE', 248, 'DATAFILE COPY EXPIRED', 247, 'DATAFILE COPY UNAVAILABLE', 246, 'ARCHIVED LOG AVAILABLE', 245, 'ARCHIVED LOG EXPIRED', 244, 'ARCHIVED LOG UNAVAILABLE', 243, 'BACKUP SET KEEP OPTIONS', 242, 'BACKUP SET KEEP UNTIL', 241, 'PROXY COPY KEEP OPTIONS', 240, 'PROXY COPY KEEP UNTIL', 239, 'DATAFILE COPY KEEP OPTIONS', 238, 'DATAFILE COPY KEEP UNTIL', 'UNKNOWN'),
dlobp, -- OBJECT_RECID NUMBER
dlosm, -- OBJECT_STAMP NUMBER
dltsd -- OBJECT_DATA 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
USERENV ('Instance'), -- INST_ID NUMBER
HANDLE, -- 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
STATE, -- LOCKSTATE VARCHAR2(64)
AST_EVENT0, -- AST_EVENT0 NUMBER
OWNER_NODE, -- OWNER_NODE NUMBER
BLOCKED, -- BLOCKED NUMBER
BLOCKER -- BLOCKER NUMBER
from
V$GES_ENQUEUE
GV$DLM_CONVERT_LOCAL
select
inst_id, -- INST_ID NUMBER
kjicvtnam, -- CONVERT_TYPE VARCHAR2(16)
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(16)
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
wait_time -- WAIT_TIME NUMBER
from
V$LATCH
where
NAME like 'ges %' or NAME like 'gcs %'
GV$DLM_LOCKS
select
USERENV ('Instance'), -- INST_ID NUMBER
HANDLE, -- 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
STATE, -- LOCKSTATE VARCHAR2(64)
AST_EVENT0, -- AST_EVENT0 NUMBER
OWNER_NODE, -- OWNER_NODE NUMBER
BLOCKED, -- BLOCKED NUMBER
BLOCKER -- BLOCKER NUMBER
from
V$GES_BLOCKING_ENQUEUE
GV$DLM_MISC
select
inst_id, -- INST_ID NUMBER
indx, -- STATISTIC# NUMBER
kjisftdesc, -- NAME VARCHAR2(38)
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
kjirftmn, -- MASTER_NODE NUMBER
kjirftncl, -- NEXT_CVT_LEVEL VARCHAR2(9)
kjirftvs, -- VALUE_BLK_STATE VARCHAR2(32)
kjirftvb -- VALUE_BLK VARCHAR2(64)
from
x$kjirft
union all
select
inst_id, -- INST_ID NUMBER
kjbrresp, -- RESP RAW(4)
kjbrname, -- RESOURCE_NAME VARCHAR2(30)
decode -- ON_CONVERT_Q NUMBER
(kjbrcvtq, '00', 0, 1),
decode -- ON_GRANT_Q NUMBER
(kjbrgrantq, '00', 0, 1),
1, -- PERSISTENT_RES NUMBER
kjbrmaster, -- MASTER_NODE NUMBER
kjbrncvl, -- NEXT_CVT_LEVEL VARCHAR2(9)
'KJUSERVS_NOVALUE', -- VALUE_BLK_STATE VARCHAR2(32)
'0x0' -- VALUE_BLK VARCHAR2(64)
from
x$kjbr
GV$DLM_TRAFFIC_CONTROLLER
select
inst_id, -- INST_ID NUMBER
kjitrftlid, -- LOCAL_NID NUMBER
kjitrftrid, -- REMOTE_NID NUMBER
kjitrftrrd, -- REMOTE_RID NUMBER
kjitrftinc, -- REMOTE_INC NUMBER
kjitrftta, -- TCKT_AVAIL NUMBER
kjitrfttl, -- TCKT_LIMIT NUMBER
kjitrfttr, -- TCKT_RCVD NUMBER
decode -- TCKT_WAIT VARCHAR2(10)
(kjitrfttw, 0, 'NO ', 'YES '),
kjitrftss, -- SND_SEQ_NO NUMBER
kjitrftsr, -- RCV_SEQ_NO NUMBER
kjitrftsql, -- SND_Q_LEN NUMBER
kjitrftsqm, -- SND_Q_MAX NUMBER
kjitrftsqt, -- SND_Q_TOT NUMBER
kjitrftqtb, -- SND_Q_TM_BASE NUMBER
kjitrftqtw, -- SND_Q_TM_WRAP NUMBER
kjitrftst, -- STATUS NUMBER
kjitrftpxy -- SND_PROXY 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$ENQUEUE_STAT
select
inst_id, -- INST_ID NUMBER
ksqsttyp, -- EQ_TYPE VARCHAR2(2)
ksqstreq, -- TOTAL_REQ# NUMBER
ksqstwat, -- TOTAL_WAIT# NUMBER
ksqstsgt, -- SUCC_REQ# NUMBER
ksqstfgt, -- FAILED_REQ# NUMBER
ksqstwtm -- CUM_WAIT_TIME NUMBER
from
X$KSQST
where
ksqstreq > 0
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.kcfiosbr, -- SINGLEBLKRDS NUMBER
k.kcfioprt, -- READTIM NUMBER
k.kcfiopwt, -- WRITETIM NUMBER
k.kcfiosbt, -- SINGLEBLKRDTIM 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_CACHE_TRANSFER
select
x.inst_id, -- INST_ID NUMBER
kcfiofno, -- FILE_NUMBER 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
KCFIOS2NC, -- S_2_NULL NUMBER
KCFIOS2NFSC, -- S_2_NULL_FORCED_STALE NUMBER
KCFIORBRC, -- RBR NUMBER
KCFIORBRFWC, -- RBR_FORCED_WRITE NUMBER
KCFIORBRFSC, -- RBR_FORCED_STALE NUMBER
KCFION2XC, -- NULL_2_X NUMBER
KCFIOS2XC, -- S_2_X NUMBER
KCFION2SC, -- NULL_2_S NUMBER
KCFIOCRTR, -- CR_TRANSFERS NUMBER
KCFIOCURTR -- CUR_TRANSFERS NUMBER
from
x$kcfio x,
x$kccfe fe
where
x.kcfiofno = fe.fenum
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$GCSHVMASTER_INFO
select
inst_id, -- INST_ID NUMBER
KJDRPCMHVID, -- HV_ID NUMBER
KJDRPCMHVCMAS, -- CURRENT_MASTER NUMBER
KJDRPCMHVPMAS, -- PREVIOUS_MASTER NUMBER
KJDRPCMHVRMCNT -- REMASTER_CNT NUMBER
from
x$kjdrpcmhv
GV$GCSPFMASTER_INFO
select
inst_id, -- INST_ID NUMBER
KJDRPCMPFID, -- FILE_ID NUMBER
KJDRPCMPFCMAS, -- CURRENT_MASTER NUMBER
KJDRPCMPFPMAS, -- PREVIOUS_MASTER NUMBER
KJDRPCMPFRMCNT -- REMASTER_CNT NUMBER
from
x$kjdrpcmpf
GV$GC_ELEMENT
select
inst_id, -- INST_ID NUMBER
le_addr, -- GC_ELEMENT_ADDR RAW(4)
indx, -- INDX NUMBER
le_class, -- CLASS NUMBER
name, -- GC_ELEMENT_NAME NUMBER
le_mode, -- MODE_HELD NUMBER
le_blks, -- BLOCK_COUNT NUMBER
le_rls, -- RELEASING NUMBER
le_acq, -- ACQUIRING NUMBER
le_write, -- WRITING NUMBER
le_recovery, -- RECOVERING NUMBER
le_local, -- LOCAL NUMBER
le_flags -- FLAGS NUMBER
from
x$le
GV$GC_ELEMENTS_WITH_COLLISIONS
select
USERENV ('Instance'), -- INST_ID NUMBER
lock_element_addr -- GC_ELEMENT_ADDR RAW(4)
from
v$bh
where
(forced_writes + forced_reads) > 10
group by lock_element_addr
having count (*) >= 2
GV$GES_BLOCKING_ENQUEUE
select
USERENV ('Instance'), -- INST_ID NUMBER
HANDLE, -- HANDLE 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
STATE, -- STATE VARCHAR2(64)
AST_EVENT0, -- AST_EVENT0 NUMBER
OWNER_NODE, -- OWNER_NODE NUMBER
BLOCKED, -- BLOCKED NUMBER
BLOCKER -- BLOCKER NUMBER
from
V$GES_ENQUEUE
where
(REQUEST_LEVEL != 'KJUSERNL') and (BLOCKED = 1 or BLOCKER = 1)
GV$GES_ENQUEUE
select
inst_id, -- INST_ID NUMBER
kjilkftlkp, -- HANDLE 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, -- STATE VARCHAR2(64)
kjilkftaste0, -- AST_EVENT0 NUMBER
kjilkfton, -- OWNER_NODE NUMBER
kjilkftblked, -- BLOCKED NUMBER
kjilkftblker -- BLOCKER NUMBER
from
x$kjilkft
union all
select
inst_id, -- INST_ID NUMBER
kjbllockp, -- HANDLE RAW(4)
kjblgrant, -- GRANT_LEVEL VARCHAR2(9)
kjblrequest, -- REQUEST_LEVEL VARCHAR2(9)
kjblname, -- RESOURCE_NAME1 VARCHAR2(30)
kjblname2, -- RESOURCE_NAME2 VARCHAR2(30)
0, -- PID NUMBER
0, -- TRANSACTION_ID0 NUMBER
0, -- TRANSACTION_ID1 NUMBER
0, -- GROUP_ID NUMBER
0, -- OPEN_OPT_DEADLOCK NUMBER
1, -- OPEN_OPT_PERSISTENT NUMBER
0, -- OPEN_OPT_PROCESS_OWNED NUMBER
1, -- OPEN_OPT_NO_XID NUMBER
0, -- CONVERT_OPT_GETVALUE NUMBER
0, -- CONVERT_OPT_PUTVALUE NUMBER
0, -- CONVERT_OPT_NOVALUE NUMBER
0, -- CONVERT_OPT_DUBVALUE NUMBER
0, -- CONVERT_OPT_NOQUEUE NUMBER
0, -- CONVERT_OPT_EXPRESS NUMBER
0, -- CONVERT_OPT_NODEADLOCKWAIT NUMBER
0, -- CONVERT_OPT_NODEADLOCKBLOCK NUMBER
kjblqueue, -- WHICH_QUEUE NUMBER
kjbllockst, -- STATE VARCHAR2(64)
0, -- AST_EVENT0 NUMBER
kjblowner, -- OWNER_NODE NUMBER
kjblblocked, -- BLOCKED NUMBER
kjblblocker -- BLOCKER NUMBER
from
x$kjbl
GV$GLOBALCONTEXT
select
namespace, -- NAMESPACE VARCHAR2(31)
attribute, -- ATTRIBUTE VARCHAR2(31)
value, -- VALUE VARCHAR2(4000)
username, -- USERNAME VARCHAR2(31)
clientidentifier -- CLIENTIDENTIFIER VARCHAR2(65)
from
x$globalcontext
where
upper (namespace) not like 'SYS_%'
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$HVMASTER_INFO
select
inst_id, -- INST_ID NUMBER
KJDRHVID, -- HV_ID NUMBER
KJDRHVCMAS, -- CURRENT_MASTER NUMBER
KJDRHVPMAS, -- PREVIOUS_MASTER NUMBER
KJDRHVRMCNT -- REMASTER_CNT NUMBER
from
x$kjdrhv
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(12)
(ksuxssts, 0, 'STARTED', 1, 'MOUNTED', 2, 'OPEN', 3, 'OPEN MIGRATE', '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'),
decode -- ACTIVE_STATE VARCHAR2(9)
(qui_state, 0, 'NORMAL', 1, 'QUIESCING', 2, 'QUIESCED', 'UNKNOWN')
from
x$ksuxsinst ks,
x$kvit kv,
x$quiesce qu
where
kvittag = 'kcbwst'
GV$INSTANCE_RECOVERY
select
T.INST_ID, -- INST_ID NUMBER
to_number -- RECOVERY_ESTIMATED_IOS NUMBER
(decode (CUR_EST_RCV_READS, -1, NULL, CUR_EST_RCV_READS)),
ACTUAL_REDO_BLKS, -- ACTUAL_REDO_BLKS NUMBER
to_number -- TARGET_REDO_BLKS NUMBER
(decode (MIN_LAG, -1, NULL, MIN_LAG)),
to_number -- LOG_FILE_SIZE_REDO_BLKS NUMBER
(decode (LOGFILESZ, -1, NULL, LOGFILESZ)),
to_number -- LOG_CHKPT_TIMEOUT_REDO_BLKS NUMBER
(decode (CT_LAG, -1, NULL, CT_LAG)),
to_number -- LOG_CHKPT_INTERVAL_REDO_BLKS NUMBER
(decode (CI_LAG, -1, NULL, CI_LAG)),
to_number -- FAST_START_IO_TARGET_REDO_BLKS NUMBER
(decode (ACTUAL_REDO_BLKS, 0, NULL, NULL)),
INUSE_EST_MTTR_SEC, -- TARGET_MTTR NUMBER
CUR_EST_MTTR_SEC, -- ESTIMATED_MTTR NUMBER
(
select
ksusgstv - (
select
ksusgstv
from
X$KSUSGSTA
where
ksusdnam='physical writes non checkpoint' and inst_id=t.inst_id)
from
X$KSUSGSTA
where
ksusdnam = 'physical writes' and inst_id=t.inst_id)
from
X$TARGETRBA T,
X$ESTIMATED_MTTR E
where
T.INST_ID=E.INST_ID
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
la.wait_time -- WAIT_TIME 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,
sum (kslltwtt) wait_time
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
t.kslltwtt -- WAIT_TIME 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
t2.ksllwnam -- LOCATION VARCHAR2(64)
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
t.kslltwtt -- WAIT_TIME 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', 13, 'JAVA SOURCE', 14, 'JAVA RESOURCE', 32, 'JAVA DATA', '?'),
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 or indx=13 or indx=14 or indx=32
GV$LIBRARY_CACHE_MEMORY
select
inst_id, -- INST_ID NUMBER
decode -- LC_NAMESPACE VARCHAR2(15)
(kglsim_namespace, 0, 'SQL AREA', 1, 'TABLE/PROCEDURE', 2, 'BODY', 3, 'TRIGGER', 4, 'INDEX', 5, 'CLUSTER', 6, 'OBJECT', 7, 'PIPE', 13, 'JAVA SOURCE', 14, 'JAVA RESOURCE', 32, 'JAVA DATA', '?'),
kglsim_pincnt, -- LC_INUSE_MEMORY_OBJECTS NUMBER
kglsim_pinmem, -- LC_INUSE_MEMORY_SIZE NUMBER
kglsim_unpincnt, -- LC_FREEABLE_MEMORY_OBJECTS NUMBER
kglsim_unpinmem -- LC_FREEABLE_MEMORY_SIZE NUMBER
from
x$kglmem
where
kglsim_namespace<8 or kglsim_namespace=13 or kglsim_namespace=14 or kglsim_namespace=32
union select
inst_id, -- INST_ID NUMBER
'OTHER/SYSTEM', -- LC_NAMESPACE VARCHAR2(15)
sum -- LC_INUSE_MEMORY_OBJECTS NUMBER
(kglsim_pincnt) sum_pincnt,
sum -- LC_INUSE_MEMORY_SIZE NUMBER
(kglsim_pinmem) sum_pinmem,
sum -- LC_FREEABLE_MEMORY_OBJECTS NUMBER
(kglsim_unpincnt) sum_unpincnt,
sum -- LC_FREEABLE_MEMORY_SIZE NUMBER
(kglsim_unpinmem) sum_unpinmem
from
x$kglmem
where
not (kglsim_namespace<8 or kglsim_namespace=13 or kglsim_namespace=14 or kglsim_namespace=32)
group by inst_id
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$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$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
decode -- BLOCK NUMBER
(l.lmode, 0, 0, l.block)
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(12)
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
USERENV ('Instance'), -- INST_ID NUMBER
lock_element_addr -- LOCK_ELEMENT_ADDR RAW(4)
from
v$bh
where
(forced_writes + forced_reads) > 10
group by lock_element_addr
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
0, -- 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, decode (bitand (le.leflg, 2), 2, 'INVALIDATED', '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, '', decode (bitand (fnflg, 1), 1, 'INVALID', decode (bitand (fnflg, 2), 2, 'STALE', decode (bitand (fnflg, 4), 4, 'DELETED', decode (bitand (fnflg, 8), 8, '', 'UNKNOWN'))))),
decode -- TYPE VARCHAR2(7)
(bitand (fnflg, 8), 0, 'ONLINE', 'STANDBY'),
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_CALLBACK
select
inst_id, -- INST_ID NUMBER
decode -- STATE VARCHAR2(8)
(state, 0, 'ENABLED', 1, 'DISABLED', 'UNKNOWN'),
decode -- TYPE VARCHAR2(19)
(type, 0, 'EVERY_CHANGE_RECORD', 1, 'ON_COMMIT', 2, 'ON_DDL', 3, 'ON_SPECIAL_MARKER', 'UNKNOWN'),
decode -- CAPABILITY VARCHAR2(8)
(capability, 0, 'ANYORDER', 1, 'INORDER', 'UNKNOWN')
from
x$logmnr_callback
GV$LOGMNR_CONTENTS
select
INST_ID, -- INST_ID NUMBER
SCN, -- SCN NUMBER
CSCN, -- CSCN NUMBER
TIMESTAMP, -- TIMESTAMP DATE
COMMIT_TIMESTAMP, -- COMMIT_TIMESTAMP DATE
THREAD#, -- THREAD# NUMBER
LOG_ID, -- LOG_ID NUMBER
XIDUSN, -- XIDUSN NUMBER
XIDSLT, -- XIDSLT NUMBER
XIDSQN, -- XIDSQN NUMBER
PXIDUSN, -- PXIDUSN NUMBER
PXIDSLT, -- PXIDSLT NUMBER
PXIDSQN, -- PXIDSQN 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(256)
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(30)
SESSION_INFO, -- SESSION_INFO VARCHAR2(4000)
TX_NAME, -- TX_NAME VARCHAR2(256)
ROLLBACK, -- ROLLBACK NUMBER
OPERATION, -- OPERATION VARCHAR2(32)
OPERATION_CODE, -- OPERATION_CODE NUMBER
SQL_REDO, -- SQL_REDO VARCHAR2(4000)
SQL_UNDO, -- SQL_UNDO VARCHAR2(4000)
RS_ID, -- RS_ID VARCHAR2(32)
SEQUENCE#, -- SEQUENCE# NUMBER
SSN, -- SSN NUMBER
CSF, -- CSF NUMBER
INFO, -- INFO VARCHAR2(32)
STATUS, -- STATUS NUMBER
REDO_VALUE, -- REDO_VALUE RAW(4)
UNDO_VALUE, -- UNDO_VALUE RAW(4)
SQL_COLUMN_TYPE, -- SQL_COLUMN_TYPE VARCHAR2(32)
SQL_COLUMN_NAME, -- SQL_COLUMN_NAME VARCHAR2(32)
REDO_LENGTH, -- REDO_LENGTH NUMBER
REDO_OFFSET, -- REDO_OFFSET NUMBER
UNDO_LENGTH, -- UNDO_LENGTH NUMBER
UNDO_OFFSET -- UNDO_OFFSET NUMBER
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_LOGFILE
select
inst_id, -- INST_ID NUMBER
log_id, -- LOG_ID NUMBER
filename, -- FILENAME VARCHAR2(513)
low_time, -- LOW_TIME NUMBER
next_time, -- NEXT_TIME NUMBER
db_id, -- DB_ID NUMBER
db_name, -- DB_NAME VARCHAR2(8)
reset_scnwrp, -- RESET_SCNWRP NUMBER
reset_scnbas, -- RESET_SCNBAS NUMBER
reset_scn_time, -- RESET_SCN_TIME NUMBER
thread_id, -- THREAD_ID NUMBER
thread_sqn, -- THREAD_SQN NUMBER
low_scnwrp, -- LOW_SCNWRP NUMBER
low_scnbas, -- LOW_SCNBAS NUMBER
next_scnwrp, -- NEXT_SCNWRP NUMBER
next_scnbas, -- NEXT_SCNBAS NUMBER
decode -- FILE_STATE VARCHAR2(13)
(state, 0, 'FILE_NOT_OPEN', 1, 'FILE_OPEN', 2, 'DONE')
from
x$logmnr_logfile
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_SCN, -- RESET_SCN NUMBER
RESET_SCN_TIME, -- RESET_SCN_TIME DATE
THREAD_ID, -- THREAD_ID NUMBER
THREAD_SQN, -- THREAD_SQN NUMBER
LOW_SCN, -- LOW_SCN NUMBER
NEXT_SCN, -- NEXT_SCN NUMBER
DICTIONARY_BEGIN, -- DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END, -- DICTIONARY_END VARCHAR2(3)
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$LOGMNR_PROCESS
select
a.inst_id, -- INST_ID NUMBER
a.pid, -- PID NUMBER
b.spid, -- SPID VARCHAR2(12)
a.role, -- ROLE VARCHAR2(32)
b.username, -- USERNAME VARCHAR2(15)
b.serial#, -- SERIAL# NUMBER
b.latchwait, -- LATCHWAIT VARCHAR2(8)
b.latchspin -- LATCHSPIN VARCHAR2(8)
from
x$logmnr_process a,
v$process b
where
a.pid = b.pid
GV$LOGMNR_REGION
select
inst_id, -- INST_ID NUMBER
memstate, -- MEMSTATE NUMBER
decode -- STATE VARCHAR2(9)
(state, 0, 'INIT', 1, 'AVAIL', 2, 'ASSIGNED', 3, 'PREPARED', 4, 'ASSEMBLED'),
owning_process -- OWNING_PROCESS NUMBER
from
x$logmnr_region
GV$LOGMNR_SESSION
select
INST_ID, -- INST_ID NUMBER
SESSION_ID, -- SESSION_ID NUMBER
SESSION_NAME, -- SESSION_NAME VARCHAR2(32)
decode -- SESSION_STATE VARCHAR2(9)
(state, 1, 'READY', 2, 'STARTED', 3, 'ACTIVE', 4, 'DISCARDED', 5, 'DETACHED', 'UNKNOWN'),
start_scnwrp, -- START_SCNWRP NUMBER
start_scnbas, -- START_SCNBAS NUMBER
end_scnwrp, -- END_SCNWRP NUMBER
end_scnbas, -- END_SCNBAS NUMBER
spill_scnwrp, -- SPILL_SCNWRP NUMBER
spill_scnbas, -- SPILL_SCNBAS NUMBER
required_slave_num, -- REQUIRED_SLAVE_NUM NUMBER
eager_threshold, -- EAGER_THRESHOLD NUMBER
stopmining_threshold, -- STOPMINING_THRESHOLD NUMBER
memory_size, -- MEMORY_SIZE NUMBER
client_id, -- CLIENT_ID NUMBER
db_id, -- DB_ID NUMBER
reset_scnwrp, -- RESET_SCNWRP NUMBER
reset_scnbas, -- RESET_SCNBAS NUMBER
callback_count, -- CALLBACK_COUNT NUMBER
slave_count -- SLAVE_COUNT NUMBER
from
x$logmnr_session
GV$LOGMNR_STATS
select
inst_id, -- INST_ID NUMBER
session_id, -- SESSION_ID NUMBER
name, -- NAME VARCHAR2(32)
value -- VALUE NUMBER
from
x$krvxsv
GV$LOGMNR_TRANSACTION
select
inst_id, -- INST_ID NUMBER
xidusn, -- XIDUSN NUMBER
xidslt, -- XIDSLT NUMBER
xidsqn, -- XIDSQN NUMBER
commit_scnwrp, -- COMMIT_SCNWRP NUMBER
commit_scnbas, -- COMMIT_SCNBAS NUMBER
num_change_record, -- NUM_CHANGE_RECORD NUMBER
flags, -- FLAGS NUMBER
chunk_index, -- CHUNK_INDEX NUMBER
total_chunks -- TOTAL_CHUNKS NUMBER
from
x$logmnr_transaction
GV$LOGSTDBY
select
inst_id, -- INST_ID NUMBER
serial#, -- SERIAL# NUMBER
logstdby_id, -- LOGSTDBY_ID NUMBER
pid, -- PID VARCHAR2(12)
type, -- TYPE VARCHAR2(30)
status_code, -- STATUS_CODE NUMBER
status, -- STATUS VARCHAR2(256)
high_scn -- HIGH_SCN NUMBER
from
x$krvslv
where
exists (
select
1
from
v$session s,
x$knstacr x
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
GV$LOGSTDBY_STATS
select
inst_id, -- INST_ID NUMBER
name, -- NAME VARCHAR2(64)
value -- VALUE VARCHAR2(64)
from
(
select
inst_id,
name,
value
from
x$krvslvs
union all
select
inst_id,
name,
to_char (value)
from
(
select
inst_id,
session_id,
name,
value
from
x$krvxsv)
where
session_id = (
select
value
from
system.logstdby$parameters
where
name = 'LMNR_SID'))
where
exists (
select
1
from
v$session s,
x$knstacr x
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
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$MANAGED_STANDBY
select
inst_id, -- INST_ID NUMBER
decode -- PROCESS VARCHAR2(7)
(MSTYP, 1, 'RFS', 2, 'MRP0', 3, 'MR (fg) ', 4, 'ARCH', 5, 'FGRD', 6, 'LGWR', 'UNKNOWN'),
to_number (MSPID), -- PID NUMBER
decode -- STATUS VARCHAR2(12)
(MSSTS, 0, 'UNUSED', 1, 'ALLOCATED', 2, 'CONNECTED', 3, 'ATTACHED', 4, 'IDLE', 5, 'ERROR', 6, 'OPENING', 7, 'CLOSING', 8, 'WRITING', 9, 'RECEIVING', 10, 'ANNOUNCING', 11, 'REGISTERING', 12, 'WAIT_FOR_LOG', 13, 'WAIT_FOR_GAP', 14, 'APPLYING_LOG', 'UNKNOWN'),
decode -- CLIENT_PROCESS VARCHAR2(8)
(MSPAR, 0, 'N/A', 4, 'ARCH', 5, 'Archival', 6, 'LGWR', 'UNKNOWN'),
decode -- CLIENT_PID VARCHAR2(40)
(MSPPID, 0, 'N/A', to_number (MSPPID)),
decode -- CLIENT_DBID VARCHAR2(40)
(MSDBID, 0, 'N/A', to_number (MSDBID)),
decode -- GROUP# VARCHAR2(40)
(MSLNO, 0, 'N/A', to_number (MSLNO)),
to_number (MSTHR), -- THREAD# NUMBER
to_number (MSSEQ), -- SEQUENCE# NUMBER
to_number (MSBNO), -- BLOCK# NUMBER
to_number (MSBCT), -- BLOCKS NUMBER
to_number (MSDLY), -- DELAY_MINS NUMBER
to_number (MSRCLT), -- KNOWN_AGENTS NUMBER
to_number (MSACLT) -- ACTIVE_AGENTS NUMBER
from
x$kcrrms
GV$MAP_COMP_LIST
select
inst_id, -- INST_ID NUMBER
elem_idx, -- ELEM_IDX NUMBER
num_comp, -- NUM_COMP NUMBER
comp1_name, -- COMP1_NAME VARCHAR2(256)
comp1_val, -- COMP1_VAL VARCHAR2(256)
comp2_name, -- COMP2_NAME VARCHAR2(256)
comp2_val, -- COMP2_VAL VARCHAR2(256)
comp3_name, -- COMP3_NAME VARCHAR2(256)
comp3_val, -- COMP3_VAL VARCHAR2(256)
comp4_name, -- COMP4_NAME VARCHAR2(256)
comp4_val, -- COMP4_VAL VARCHAR2(256)
comp5_name, -- COMP5_NAME VARCHAR2(256)
comp5_val -- COMP5_VAL VARCHAR2(256)
from
x$ksfmcompl
GV$MAP_ELEMENT
select
inst_id, -- INST_ID NUMBER
elem_name, -- ELEM_NAME VARCHAR2(256)
elem_idx, -- ELEM_IDX NUMBER
elem_cfgid, -- ELEM_CFGID VARCHAR2(256)
decode -- ELEM_TYPE VARCHAR2(12)
(elem_type, 1, 'MIRROR', 2, 'STRIPE', 3, 'RAID5', 4, 'CONCATENATED', 5, 'PARTITION', 6, 'DISK', 7, 'NONE'),
to_number -- ELEM_SIZE NUMBER
(decode (elem_size, 4294967295, NULL, elem_size)),
elem_nsubelem, -- ELEM_NSUBELEM NUMBER
elem_descr, -- ELEM_DESCR VARCHAR2(256)
stripe_size, -- STRIPE_SIZE NUMBER
to_number -- LIB_IDX NUMBER
(decode (lib_idx, 4294967295, NULL, lib_idx))
from
x$ksfmelem
GV$MAP_EXT_ELEMENT
select
inst_id, -- INST_ID NUMBER
elem_idx, -- ELEM_IDX NUMBER
num_attrb, -- NUM_ATTRB NUMBER
attrb1_name, -- ATTRB1_NAME VARCHAR2(256)
attrb1_val, -- ATTRB1_VAL VARCHAR2(256)
attrb2_name, -- ATTRB2_NAME VARCHAR2(256)
attrb2_val, -- ATTRB2_VAL VARCHAR2(256)
attrb3_name, -- ATTRB3_NAME VARCHAR2(256)
attrb3_val, -- ATTRB3_VAL VARCHAR2(256)
attrb4_name, -- ATTRB4_NAME VARCHAR2(256)
attrb4_val, -- ATTRB4_VAL VARCHAR2(256)
attrb5_name, -- ATTRB5_NAME VARCHAR2(256)
attrb5_val -- ATTRB5_VAL VARCHAR2(256)
from
x$ksfmextelem
GV$MAP_FILE
select
inst_id, -- INST_ID NUMBER
file_idx, -- FILE_MAP_IDX NUMBER
file_cfgid, -- FILE_CFGID VARCHAR2(256)
decode -- FILE_STATUS VARCHAR2(7)
(file_status, 1, 'VALID', 2, 'INVALID'),
file_name, -- FILE_NAME VARCHAR2(256)
decode -- FILE_TYPE VARCHAR2(11)
(file_type, 1, 'DATAFILE', 2, 'SPFILE', 3, 'TEMPFILE', 4, 'CONTROLFILE', 5, 'LOGFILE', 6, 'ARCHIVEFILE'),
decode -- FILE_STRUCTURE VARCHAR2(9)
(file_struct, 1, 'FILE', 2, 'RAWVOLUME', 3, 'RAWDEVICE', 4, 'NONE'),
file_size, -- FILE_SIZE NUMBER
file_nexts, -- FILE_NEXTS NUMBER
to_number -- LIB_IDX NUMBER
(decode (lib_idx, 4294967295, NULL, lib_idx))
from
x$ksfmfile
GV$MAP_FILE_EXTENT
select
inst_id, -- INST_ID NUMBER
file_idx, -- FILE_MAP_IDX NUMBER
ext_num, -- EXT_NUM NUMBER
ext_dev_off, -- EXT_ELEM_OFF NUMBER
ext_size, -- EXT_SIZE NUMBER
ext_file_off, -- EXT_FILE_OFF NUMBER
decode -- EXT_TYPE VARCHAR2(6)
(ext_type, 1, 'DATA', 2, 'PARITY', 3, 'NONE'),
elem_idx -- ELEM_IDX NUMBER
from
x$ksfmfileext
where
elem_idx != 4294967295
GV$MAP_FILE_IO_STACK
select
inst_id, -- INST_ID NUMBER
file_idx, -- FILE_MAP_IDX NUMBER
depth, -- DEPTH NUMBER
elem_idx, -- ELEM_IDX NUMBER
cu_size, -- CU_SIZE NUMBER
stride, -- STRIDE NUMBER
num_cu, -- NUM_CU NUMBER
dev_offset, -- ELEM_OFFSET NUMBER
to_number -- FILE_OFFSET NUMBER
(decode (file_offset, 4294967295, NULL, file_offset)),
decode -- DATA_TYPE VARCHAR2(15)
(data_type, 1, 'DATA', 2, 'PARITY', 3, 'DATA AND PARITY'),
parity_pos, -- PARITY_POS NUMBER
parity_perd, -- PARITY_PERIOD NUMBER
row_id, -- ID NUMBER
prow_id -- PARENT_ID NUMBER
from
x$ksfmiost
GV$MAP_LIBRARY
select
inst_id, -- INST_ID NUMBER
lib_idx, -- LIB_IDX NUMBER
lib_name, -- LIB_NAME VARCHAR2(256)
vendor_name, -- VENDOR_NAME VARCHAR2(64)
protocol_num, -- PROTOCOL_NUM NUMBER
version_num, -- VERSION_NUM VARCHAR2(32)
path_name, -- PATH_NAME VARCHAR2(4000)
decode -- MAP_FILE VARCHAR2(1)
(bitand (cap_file, 1), 0, 'N', 1, 'Y'),
decode -- FILE_CFGID VARCHAR2(13)
(bitand (cap_file, 6), 0, 'NONE', 6, 'PERSISTENT', 2, 'NONPERSISTENT'),
decode -- MAP_ELEM VARCHAR2(1)
(bitand (cap_elem, 1), 0, 'N', 1, 'Y'),
decode -- ELEM_CFGID VARCHAR2(13)
(bitand (cap_elem, 6), 0, 'NONE', 6, 'PERSISTENT', 4, 'NONPERSISTENT'),
decode -- MAP_SYNC VARCHAR2(1)
(cap_other, 0, 'N', 1, 'Y')
from
x$ksfmlib
GV$MAP_SUBELEMENT
select
inst_id, -- INST_ID NUMBER
child_idx, -- CHILD_IDX NUMBER
parent_idx, -- PARENT_IDX NUMBER
sub_num, -- SUB_NUM NUMBER
to_number -- SUB_SIZE NUMBER
(decode (sub_size, 4294967295, NULL, sub_size)),
to_number -- ELEM_OFFSET NUMBER
(decode (elem_offset, 4294967295, NULL, elem_offset)),
sub_flags -- SUB_FLAGS NUMBER
from
x$ksfmsubelem
where
child_idx != 4294967295
GV$MAX_ACTIVE_SESS_TARGET_MTH
select
inst_id, -- INST_ID NUMBER
policy_name_kgskasp -- NAME VARCHAR2(40)
from
x$kgskasp
GV$MTTR_TARGET_ADVICE
select
distinct inst_id, -- INST_ID NUMBER
mttr_v, -- MTTR_TARGET_FOR_ESTIMATE NUMBER
decode -- ADVICE_STATUS VARCHAR2(5)
(status, 0, 'OFF', 4, 'ON', 'READY'),
decode -- DIRTY_LIMIT NUMBER
(dirty_limit, 0, to_number (NULL), dirty_limit),
decode -- ESTD_CACHE_WRITES NUMBER
(factored_sim_writes, -1, to_number (NULL), factored_sim_writes),
decode -- ESTD_CACHE_WRITE_FACTOR NUMBER
(base_real_nondirect_writes, 0, to_number (NULL), decode (factored_sim_writes, -1, to_number (NULL), round ((factored_sim_writes / base_real_nondirect_writes), 4))),
decode -- ESTD_TOTAL_WRITES NUMBER
(total_writes, -1, to_number (NULL), total_writes),
decode -- ESTD_TOTAL_WRITE_FACTOR NUMBER
(base_total_writes, 0, to_number (NULL), decode (total_writes, -1, to_number (NULL), round ((total_writes / base_total_writes), 4))),
decode -- ESTD_TOTAL_IOS NUMBER
(total_ios, -1, to_number (NULL), total_ios),
decode -- ESTD_TOTAL_IO_FACTOR NUMBER
(base_total_ios, 0, to_number (NULL), decode (total_ios, -1, to_number (NULL), round ((total_ios / base_total_ios), 4)))
from
x$kcbmmav
GV$MVREFRESH
select
inst_id, -- INST_ID NUMBER
sid_knst, -- SID NUMBER
serial_knst, -- SERIAL# NUMBER
currmvowner_knstmvr, -- CURRMVOWNER VARCHAR2(31)
currmvname_knstmvr -- CURRMVNAME VARCHAR2(31)
from
x$knstmvr x
where
type_knst=6 and exists (
select
1
from
v$session s
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
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
ksusgstl
from
x$ksusgif)
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)
ksppstcmnt -- UPDATE_COMMENT VARCHAR2(255)
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)
kspftctxvn, -- ORDINAL NUMBER
kspftctxct -- UPDATE_COMMENT VARCHAR2(255)
from
x$ksppi x,
x$ksppcv2 y
where
((x.indx+1) = kspftctxpn) and ((translate (ksppinm,
'_',
'#') not like '#%') or (kspftctxdf = 'FALSE'))
GV$PGASTAT
select
INST_ID, -- INST_ID NUMBER
QESMMSGANM, -- NAME VARCHAR2(64)
decode -- VALUE NUMBER
(QESMMSGAUN, 3, (QESMMSGAVL*QESMMSGAMU) /100, QESMMSGAVL*QESMMSGAMU),
decode -- UNIT VARCHAR2(12)
(QESMMSGAUN, 0, 'bytes', 1, 'microseconds', 3, 'percent', '')
from
X$QESMMSGA
where
QESMMSGAVS = 1
GV$PGA_TARGET_ADVICE
select
INST_ID, -- INST_ID NUMBER
PAT_PRED * 1024, -- PGA_TARGET_FOR_ESTIMATE NUMBER
round -- PGA_TARGET_FACTOR NUMBER
(PAT_PRED/PAT_CURR, 4),
decode -- ADVICE_STATUS VARCHAR2(3)
(status, 0, 'OFF', 'ON'),
BYTES_PROCESSED * 1024, -- BYTES_PROCESSED NUMBER
EXTRA_BYTES_RW * 1024, -- ESTD_EXTRA_BYTES_RW NUMBER
round -- ESTD_PGA_CACHE_HIT_PERCENTAGE NUMBER
(decode (BYTES_PROCESSED+EXTRA_BYTES_RW, 0, 0, (BYTES_PROCESSED*100) / (BYTES_PROCESSED+EXTRA_BYTES_RW))),
OVERALLOC -- ESTD_OVERALLOC_COUNT NUMBER
from
X$QESMMAPADV
GV$PGA_TARGET_ADVICE_HISTOGRAM
select
INST_ID, -- INST_ID NUMBER
PAT_PRED * 1024, -- PGA_TARGET_FOR_ESTIMATE NUMBER
round -- PGA_TARGET_FACTOR NUMBER
(PAT_PRED/PAT_CURR, 4),
decode -- ADVICE_STATUS VARCHAR2(3)
(status, 0, 'OFF', 'ON'),
LOWBND * 1024, -- LOW_OPTIMAL_SIZE NUMBER
(HIBND * 1024) -1, -- HIGH_OPTIMAL_SIZE NUMBER
OPTIMAL, -- ESTD_OPTIMAL_EXECUTIONS NUMBER
ONEPASS, -- ESTD_ONEPASS_EXECUTIONS NUMBER
MPASS, -- ESTD_MULTIPASSES_EXECUTIONS NUMBER
MPASS+ONEPASS+OPTIMAL, -- ESTD_TOTAL_EXECUTIONS NUMBER
IGNORED -- IGNORED_WORKAREAS_COUNT NUMBER
from
X$QESMMAHIST
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(12)
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),
ksuprpum, -- PGA_USED_MEM NUMBER
ksuprpnam+ksuprpram, -- PGA_ALLOC_MEM NUMBER
ksuprpfm, -- PGA_FREEABLE_MEM NUMBER
case -- PGA_MAX_MEM NUMBER
when ksuprpnam+ksuprpram > ksuprpmm then ksuprpnam+ksuprpram else ksuprpmm end
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+4), 0, 'A', 1, 'D', 2, 'X', 4, 'U', '?'),
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+4), 0, 'A', 1, 'D', 2, 'X', 4, 'U', '?'),
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),
decode -- KEEP VARCHAR2(3)
(bitand (pdflg, 1792), 0, 'NO', 'YES'),
to_date -- KEEP_UNTIL DATE
(pdkpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
decode -- KEEP_OPTIONS VARCHAR2(10)
(bitand (pdflg, 1792), 256, 'LOGS', 512, 'NOLOGS', 1024, 'CONSISTENT', 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 and username != 'INTERNAL'
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(12)
c.sid, -- SID NUMBER
c.serial# -- SERIAL# NUMBER
from
x$kxfpdp a,
V$PROCESS b,
V$SESSION c
where
bitand (kxfpdpflg, 8) != 0 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.indx = c.indx and c.ksusestn < (
select
ksusgstl
from
x$ksusgif)
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$QUEUEING_MTH
select
inst_id, -- INST_ID NUMBER
policy_name_kgskquep -- NAME VARCHAR2(40)
from
x$kgskquep
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
USERENV ('Instance'), -- INST_ID NUMBER
OPNAME, -- TYPE VARCHAR2(64)
TARGET_DESC, -- ITEM VARCHAR2(32)
SOFAR, -- SOFAR NUMBER
TOTALWORK -- TOTAL NUMBER
from
V$SESSION_LONGOPS
where
opname like '% Recovery'
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 -- ONLINE_STATUS 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$REPLPROP
select
inst_id, -- INST_ID NUMBER
sid_knst, -- SID NUMBER
serial_knst, -- SERIAL# NUMBER
decode -- NAME VARCHAR2(71)
(type_knst, 3, 'Replication Parallel Prop Slave'|| slavenum_knstrpp, 4, 'Replication Parallel Prop Coordinator'),
dblink_knstrpp, -- DBLINK VARCHAR2(128)
decode -- STATE VARCHAR2(12)
(state_knstrpp, 0, NULL, 1, 'WAIT', 2, 'SLEEP', 3, 'PUSH', 4, 'PURGE', 5, 'CREATE ERROR', 6, 'SCHEDULE TXN'),
decode -- XID VARCHAR2(22)
(type_knst, 4, NULL, xid_knstrpp),
sequence_knstrpp -- SEQUENCE NUMBER
from
x$knstrpp x
where
type_knst in (3,
4) and exists (
select
1
from
v$session s
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
GV$REPLQUEUE
select
inst_id, -- INST_ID NUMBER
txns_enqueued_knstrqu, -- TXNS_ENQUEUED NUMBER
calls_enqueued_knstrqu, -- CALLS_ENQUEUED NUMBER
txns_purged_knstrqu, -- TXNS_PURGED NUMBER
last_enqueue_time_knstrqu, -- LAST_ENQUEUE_TIME DATE
last_purge_time_knstrqu -- LAST_PURGE_TIME DATE
from
x$knstrqu
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$RESUMABLE
select
inst_id, -- INST_ID NUMBER
ktrsfaddr, -- ADDR RAW(4)
ktrsfsid, -- SID NUMBER
decode -- ENABLED VARCHAR2(3)
(bitand (ktrsfflg, 1), 0, 'NO', 'YES'),
decode -- STATUS VARCHAR2(9)
(ktrsfsta, 0, 'NORMAL', 1, 'SUSPENDED', 2, 'TIMEOUT', 3, 'ERROR', 4, 'ABORTED', ''),
ktrsftmo, -- TIMEOUT NUMBER
ktrsfspt, -- SUSPEND_TIME VARCHAR2(20)
ktrsfrst, -- RESUME_TIME VARCHAR2(20)
ktrsfnam, -- NAME VARCHAR2(4000)
ktrsferr, -- ERROR_NUMBER NUMBER
ktrsfep1, -- ERROR_PARAMETER1 VARCHAR2(80)
ktrsfep2, -- ERROR_PARAMETER2 VARCHAR2(80)
ktrsfep3, -- ERROR_PARAMETER3 VARCHAR2(80)
ktrsfep4, -- ERROR_PARAMETER4 VARCHAR2(80)
ktrsfep5, -- ERROR_PARAMETER5 VARCHAR2(80)
ktrsfems -- ERROR_MSG VARCHAR2(4000)
from
x$ktrso
GV$RMAN_CONFIGURATION
select
INST_ID, -- INST_ID NUMBER
RMRNO, -- CONF# NUMBER
RMNAM, -- NAME VARCHAR2(65)
RMVAL -- VALUE VARCHAR2(1025)
from
X$KCCRM
where
RMNAM is not null
GV$ROLLSTAT
select
inst_id, -- INST_ID NUMBER
kturdusn, -- USN NUMBER
kturdlat, -- LATCH 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)
(bitand (kturdflg, 127), 0, 'ONLINE', 2, 'PENDING OFFLINE', 3, 'OFFLINE', 4, 'FULL', 'UNKNOWN'),
kturdcex, -- CUREXT NUMBER
kturdcbk -- CURBLK NUMBER
from
x$kturd
where
kturdsiz != 0 and bitand (kturdflg, 127) != 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, -- QUEUE_LENGTH NUMBER
undo_consump_kgskcft -- CURRENT_UNDO_CONSUMPTION 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$SEGMENT_STATISTICS
select
s.inst_id, -- INST_ID NUMBER
u.name, -- OWNER VARCHAR2(30)
o.name, -- OBJECT_NAME VARCHAR2(30)
o.subname, -- SUBOBJECT_NAME VARCHAR2(30)
ts.name, -- TABLESPACE_NAME VARCHAR2(30)
s.fts_tsn, -- TS# NUMBER
o.obj#, -- OBJ# NUMBER
o.dataobj#, -- DATAOBJ# NUMBER
decode -- OBJECT_TYPE VARCHAR2(18)
(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 'UNDEFINED'),
s.fts_statnam, -- STATISTIC_NAME VARCHAR2(64)
s.fts_statid, -- STATISTIC# NUMBER
s.fts_staval -- VALUE NUMBER
from
obj$ o,
user$ u,
x$ksolsfts s,
ts$ ts
where
o.owner# = u.user# and s.fts_inte = 0 and s.fts_objn = o.obj# and s.fts_tsn = ts.ts# and s.fts_objd = o.dataobj# and o.linkname is null and (o.type# not in (1 /* INDEX - handled below */
,
10 /* NON-EXISTENT */
) or (o.type# = 1 and 1 = (
select
1
from
ind$ i
where
i.obj# = o.obj# and i.type# in (1,
2,
3,
4,
6,
7,
9)))) and o.name != '_NEXT_OBJECT' and o.name != '_default_auditing_options_'
GV$SEGSTAT
select
inst_id, -- INST_ID NUMBER
fts_tsn, -- TS# NUMBER
fts_objn, -- OBJ# NUMBER
fts_objd, -- DATAOBJ# NUMBER
fts_statnam, -- STATISTIC_NAME VARCHAR2(64)
fts_statid, -- STATISTIC# NUMBER
fts_staval -- VALUE NUMBER
from
x$ksolsfts
where
fts_inte = 0
GV$SEGSTAT_NAME
select
inst_id, -- INST_ID NUMBER
st_statid, -- STATISTIC# NUMBER
st_colname, -- NAME VARCHAR2(64)
decode -- SAMPLED VARCHAR2(3)
(st_samp, 0, 'NO', 1, 'YES')
from
x$ksolsstat
where
st_inte = 0
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(12)
ksusemnm, -- MACHINE VARCHAR2(64)
ksusetid, -- TERMINAL VARCHAR2(30)
ksusepnm, -- PROGRAM VARCHAR2(48)
decode -- TYPE VARCHAR2(10)
(bitand (ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?'),
ksusesql, -- SQL_ADDRESS RAW(4)
ksusesqh, -- SQL_HASH_VALUE NUMBER
ksusepsq, -- PREV_SQL_ADDR RAW(4)
ksusepha, -- PREV_HASH_VALUE NUMBER
ksuseapp, -- MODULE VARCHAR2(48)
ksuseaph, -- MODULE_HASH NUMBER
ksuseact, -- ACTION VARCHAR2(32)
ksuseach, -- ACTION_HASH NUMBER
ksusecli, -- CLIENT_INFO VARCHAR2(64)
ksusefix, -- FIXED_TABLE_SEQUENCE NUMBER
ksuseobj, -- ROW_WAIT_OBJ# NUMBER
ksusefil, -- ROW_WAIT_FILE# NUMBER
ksuseblk, -- ROW_WAIT_BLOCK# NUMBER
ksuseslt, -- ROW_WAIT_ROW# NUMBER
ksuseltm, -- LOGON_TIME DATE
ksusectm, -- LAST_CALL_ET NUMBER
decode -- PDML_ENABLED VARCHAR2(3)
(bitand (ksusepfl, 16), 0, 'NO', 'YES'),
decode -- FAILOVER_TYPE VARCHAR2(13)
(ksuseft, 2, 'SESSION', 4, 'SELECT', 8, 'TRANSACTIONAL', 'NONE'),
decode -- FAILOVER_METHOD VARCHAR2(10)
(ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'),
decode -- FAILED_OVER VARCHAR2(3)
(ksusefs, 1, 'YES', 'NO'),
ksusegrp, -- RESOURCE_CONSUMER_GROUP VARCHAR2(32)
decode -- PDML_STATUS VARCHAR2(8)
(bitand (ksusepfl, 16), 16, 'ENABLED', decode (bitand (ksusepfl, 32), 32, 'FORCED', 'DISABLED')),
decode -- PDDL_STATUS VARCHAR2(8)
(bitand (ksusepfl, 64), 64, 'FORCED', decode (bitand (ksusepfl, 128), 128, 'DISABLED', 'ENABLED')),
decode -- PQ_STATUS VARCHAR2(8)
(bitand (ksusepfl, 512), 512, 'FORCED', decode (bitand (ksusepfl, 256), 256, 'DISABLED', 'ENABLED')),
ksusecqd, -- CURRENT_QUEUE_DURATION NUMBER
ksuseclid -- CLIENT_IDENTIFIER VARCHAR2(64)
from
x$ksuse
where
bitand (ksspaflg, 1) !=0 and bitand (ksuseflg, 1) !=0
GV$SESSION_CONNECT_INFO
select
inst_id, -- INST_ID NUMBER
ksusenum, -- SID NUMBER
decode -- AUTHENTICATION_TYPE VARCHAR2(26)
(ksuseaty, 0, 'DATABASE', 1, 'OS', 2, 'NETWORK', 3, 'PROXY', 4, 'SERVER', 5, 'PASSWORD', 6, 'EXTERNAL ADAPTERS', 7, 'INTERNAL', 8, 'GLOBAL', 9, 'EXTERNAL', 10, 'PASSWORD BASED GLOBAL USER', '?'),
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
round -- TIME_WAITED NUMBER
(s.kslestim / 10000),
round -- AVERAGE_WAIT NUMBER
(s.kslestim / (10000 * s.ksleswts)),
round -- MAX_WAIT NUMBER
(s.kslesmxt / 10000),
s.kslestim -- TIME_WAITED_MICRO 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
(sign (ksulotot-ksulosfr), -1, to_number (NULL), decode (ksulosfr, 0, to_number (NULL), 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)
decode -- WAIT_TIME NUMBER
(s.ksusstim, 0, 0, -1, -1, -2, -2, decode (round (s.ksusstim/10000), 0, -1, round (s.ksusstim/10000))),
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
ksusgstl
from
x$ksusgif)
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)
sum (ksmsslen) -- BYTES NUMBER
from
x$ksmss
where
ksmsslen>1
group by inst_id,
'shared pool',
ksmssnam
union all
select
inst_id, -- INST_ID NUMBER
'large pool', -- POOL VARCHAR2(11)
ksmssnam, -- NAME VARCHAR2(26)
sum (ksmsslen) -- BYTES NUMBER
from
x$ksmls
where
ksmsslen>1
group by inst_id,
'large pool',
ksmssnam
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$SGA_CURRENT_RESIZE_OPS
select
sc.inst_id, -- INST_ID NUMBER
gv.component, -- COMPONENT VARCHAR2(64)
decode -- OPER_TYPE VARCHAR2(6)
(sc.opcode, 1, 'GROW', 2, 'SHRINK', NULL),
decode -- OPER_MODE VARCHAR2(6)
(sc.opmode, 1, 'MANUAL', 2, 'AUTO', NULL),
pn.name, -- PARAMETER VARCHAR2(64)
sc.initsize -- INITIAL_SIZE NUMBER
* gv.gransize,
sc.targsize -- TARGET_SIZE NUMBER
* gv.gransize,
sc.realsize -- CURRENT_SIZE NUMBER
* gv.gransize,
sc.starttime, -- START_TIME DATE
sc.lasttime -- LAST_UPDATE_TIME DATE
from
x$ksmgsc sc,
x$ksmgv gv,
v$parameter pn
where
(sc.grantype = gv.grantype) and (sc.parno = pn.num) and (sc.opcode <> 0) and (sc.starttime is not null)
GV$SGA_DYNAMIC_COMPONENTS
select
st.inst_id, -- INST_ID NUMBER
gv.component, -- COMPONENT VARCHAR2(64)
st.cursize -- CURRENT_SIZE NUMBER
* gv.gransize,
st.minsize -- MIN_SIZE NUMBER
* gv.gransize,
st.maxsize -- MAX_SIZE NUMBER
* gv.gransize,
st.opercnt, -- OPER_COUNT NUMBER
decode -- LAST_OPER_TYPE VARCHAR2(6)
(st.lastoper, 1, 'GROW', 2, 'SHRINK', NULL),
decode -- LAST_OPER_MODE VARCHAR2(6)
(st.lastmode, 1, 'MANUAL', 2, 'AUTO', NULL),
st.lasttime, -- LAST_OPER_TIME DATE
gv.gransize -- GRANULE_SIZE NUMBER
from
x$ksmgst st,
x$ksmgv gv
where
(st.grantype = gv.grantype)
GV$SGA_DYNAMIC_FREE_MEMORY
select
inst_id, -- INST_ID NUMBER
gv.gransize * (
select
count (*)
from
x$ksmge
where
granstate = 'FREE' or granstate = 'INVALID')
from
x$ksmgv gv
where
rownum=1
GV$SGA_RESIZE_OPS
select
op.inst_id, -- INST_ID NUMBER
gv.component, -- COMPONENT VARCHAR2(64)
decode -- OPER_TYPE VARCHAR2(6)
(op.opcode, 1, 'GROW', 2, 'SHRINK', NULL),
decode -- OPER_MODE VARCHAR2(6)
(op.opmode, 1, 'MANUAL', 2, 'AUTO', NULL),
pn.name, -- PARAMETER VARCHAR2(64)
op.initsize -- INITIAL_SIZE NUMBER
* gv.gransize,
op.targsize -- TARGET_SIZE NUMBER
* gv.gransize,
op.realsize -- FINAL_SIZE NUMBER
* gv.gransize,
decode -- STATUS VARCHAR2(6)
(op.status, 1, 'NORMAL', 2, 'CANCEL', 3, 'ERROR', NULL),
op.starttime, -- START_TIME DATE
op.endtime -- END_TIME DATE
from
x$ksmgop op,
x$ksmgv gv,
v$parameter pn
where
(op.grantype = gv.grantype) and (op.parno = pn.num) order by op.starttime
GV$SHARED_POOL_ADVICE
select
inst_id, -- INST_ID NUMBER
sp_size, -- SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER
round -- SHARED_POOL_SIZE_FACTOR NUMBER
(sp_size / basesp_size, 4),
kglsim_size, -- ESTD_LC_SIZE NUMBER
kglsim_objs, -- ESTD_LC_MEMORY_OBJECTS NUMBER
kglsim_timesave, -- ESTD_LC_TIME_SAVED NUMBER
decode -- ESTD_LC_TIME_SAVED_FACTOR NUMBER
(kglsim_basetimesave, 0, to_number (null), round (kglsim_timesave / kglsim_basetimesave, 4)),
kglsim_hits -- ESTD_LC_MEMORY_OBJECT_HITS NUMBER
from
x$kglsim
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$SHARED_SERVER_MONITOR
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$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, -- USERNAME VARCHAR2(30)
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$SPPARAMETER
select
INST_ID, -- INST_ID NUMBER
KSPSPFFTCTXSPSID, -- SID VARCHAR2(80)
KSPSPFFTCTXSPNAME, -- NAME VARCHAR2(80)
KSPSPFFTCTXSPVALUE, -- VALUE VARCHAR2(255)
KSPSPFFTCTXISSPECIFIED, -- ISSPECIFIED VARCHAR2(6)
KSPSPFFTCTXORDINAL, -- ORDINAL NUMBER
KSPSPFFTCTXCOMMENT -- UPDATE_COMMENT VARCHAR2(255)
from
x$kspspfile
WHERE
((translate (KSPSPFFTCTXSPNAME,
'_',
'#') not like '#%') OR KSPSPFFTCTXISSPECIFIED = 'TRUE')
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
kglobt04, -- FETCHES NUMBER
kglobt05, -- 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
kglobt30, -- PLAN_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)
kglobt06, -- CPU_TIME NUMBER
kglobt07, -- ELAPSED_TIME NUMBER
kglobt28, -- OUTLINE_SID NUMBER
kglhdadr, -- CHILD_ADDRESS RAW(4)
kglobt29, -- SQLTYPE NUMBER
decode -- REMOTE VARCHAR2(1)
(bitand (kglobt00, 64), 64, 'Y', 'N'),
decode -- OBJECT_STATUS VARCHAR2(19)
(kglobsta, 1, 'VALID', 2, 'VALID_AUTH_ERROR', 3, 'VALID_COMPILE_ERROR', 4, 'VALID_UNAUTH', 5, 'INVALID_UNAUTH', 6, 'INVALID'),
kglobt31, -- LITERAL_HASH_VALUE NUMBER
substr -- LAST_LOAD_TIME VARCHAR2(19)
(to_char (kglobtt0, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
decode -- IS_OBSOLETE VARCHAR2(1)
(kglobt33, 1, 'Y', 'N'),
kglhdclt -- CHILD_LATCH NUMBER
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 (kglobt04), -- FETCHES NUMBER
sum (kglobt05), -- 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
sum (kglobt06), -- CPU_TIME NUMBER
sum (kglobt07), -- ELAPSED_TIME NUMBER
decode -- IS_OBSOLETE VARCHAR2(1)
(kglobt33, 1, 'Y', 'N'),
kglhdclt -- CHILD_LATCH NUMBER
from
x$kglcursor
group by inst_id,
kglnaobj,
kglhdpar,
kglnahsh,
kglnatim,
kglobts0,
kglobt19,
kglobts1,
kglobt20,
decode (kglobt33, 1, 'Y', 'N'),
kglhdclt
having sum (decode (kglobt09, 0, kglobt02, 0)) != 0
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_PLAN
select
inst_id, -- INST_ID NUMBER
kqlfxpl_phad, -- ADDRESS RAW(4)
kqlfxpl_hash, -- HASH_VALUE NUMBER
kqlfxpl_chno, -- CHILD_NUMBER NUMBER
substr -- OPERATION VARCHAR2(30)
(kqlfxpl_oper, 1, 30),
substr -- OPTIONS VARCHAR2(30)
(kqlfxpl_oopt, 1, 30),
substr -- OBJECT_NODE VARCHAR2(10)
(kqlfxpl_tqid, 1, 10),
to_number -- OBJECT# NUMBER
(decode (kqlfxpl_objn, 0, NULL, kqlfxpl_objn)),
case -- OBJECT_OWNER VARCHAR2(30)
when kqlfxpl_objname is not null then 'SYS' else u.name end,
nvl -- OBJECT_NAME VARCHAR2(64)
(p.kqlfxpl_objname, o.name),
substr -- OPTIMIZER VARCHAR2(20)
(kqlfxpl_opti, 1, 20),
kqlfxpl_opid, -- ID NUMBER
to_number -- PARENT_ID NUMBER
(decode (kqlfxpl_opid, 0, NULL, kqlfxpl_paid)),
kqlfxpl_depth, -- DEPTH NUMBER
kqlfxpl_pos, -- POSITION NUMBER
kqlfxpl_scols, -- SEARCH_COLUMNS NUMBER
to_number -- COST NUMBER
(decode (kqlfxpl_cost, 0, NULL, kqlfxpl_cost)),
to_number -- CARDINALITY NUMBER
(decode (kqlfxpl_card, 0, NULL, kqlfxpl_card)),
to_number -- BYTES NUMBER
(decode (kqlfxpl_size, 0, NULL, kqlfxpl_size)),
substr -- OTHER_TAG VARCHAR2(35)
(kqlfxpl_otag, 1, 35),
substr -- PARTITION_START VARCHAR2(5)
(kqlfxpl_psta, 1, 5),
substr -- PARTITION_STOP VARCHAR2(5)
(kqlfxpl_psto, 1, 5),
to_number -- PARTITION_ID NUMBER
(decode (kqlfxpl_pnid, 0, NULL, kqlfxpl_pnid)),
kqlfxpl_other, -- OTHER VARCHAR2(4000)
substr -- DISTRIBUTION VARCHAR2(20)
(kqlfxpl_dist, 1, 20),
to_number -- CPU_COST NUMBER
(decode (kqlfxpl_cpuc, 0, NULL, kqlfxpl_cpuc)),
to_number -- IO_COST NUMBER
(decode (kqlfxpl_ioct, 0, NULL, kqlfxpl_ioct)),
to_number -- TEMP_SPACE NUMBER
(decode (kqlfxpl_temp, 0, NULL, kqlfxpl_temp)),
kqlfxpl_keys, -- ACCESS_PREDICATES VARCHAR2(4000)
kqlfxpl_filter -- FILTER_PREDICATES VARCHAR2(4000)
from
x$kqlfxpl p,
obj$ o,
user$ u
where
p.kqlfxpl_hadd != p.kqlfxpl_phad and p.kqlfxpl_objn = o.obj# (+) and o.owner# = u.user# (+)
GV$SQL_PLAN_STATISTICS
select
inst_id, -- INST_ID NUMBER
PHADD_QESRS, -- ADDRESS RAW(4)
HASHV_QESRS, -- HASH_VALUE NUMBER
CHILDNO_QESRS, -- CHILD_NUMBER NUMBER
OPERID_QESRS, -- OPERATION_ID NUMBER
EXECS_QESRS, -- EXECUTIONS NUMBER
LSTARTS_QESRS, -- LAST_STARTS NUMBER
STARTS_QESRS, -- STARTS NUMBER
LOUTROWS_QESRS, -- LAST_OUTPUT_ROWS NUMBER
OUTROWS_QESRS, -- OUTPUT_ROWS NUMBER
LCRGETS_QESRS, -- LAST_CR_BUFFER_GETS NUMBER
CRGETS_QESRS, -- CR_BUFFER_GETS NUMBER
LCUGETS_QESRS, -- LAST_CU_BUFFER_GETS NUMBER
CUGETS_QESRS, -- CU_BUFFER_GETS NUMBER
LDREADS_QESRS, -- LAST_DISK_READS NUMBER
DREADS_QESRS, -- DISK_READS NUMBER
LDWRITES_QESRS, -- LAST_DISK_WRITES NUMBER
DWRITES_QESRS, -- DISK_WRITES NUMBER
LELAPTIME_QESRS, -- LAST_ELAPSED_TIME NUMBER
ELAPTIME_QESRS -- ELAPSED_TIME NUMBER
from
X$QESRSTAT
GV$SQL_PLAN_STATISTICS_ALL
select
inst_id, -- INST_ID NUMBER
PHADD_QESRS, -- ADDRESS RAW(4)
HASHV_QESRS, -- HASH_VALUE NUMBER
CHILDNO_QESRS, -- CHILD_NUMBER NUMBER
substr -- OPERATION VARCHAR2(30)
(oper_qesrs, 1, 30),
substr -- OPTIONS VARCHAR2(30)
(oopt_qesrs, 1, 30),
substr -- OBJECT_NODE VARCHAR2(10)
(tqid_qesrs, 1, 10),
to_number -- OBJECT# NUMBER
(decode (objn_qesrs, 0, NULL, objn_qesrs)),
case -- OBJECT_OWNER VARCHAR2(30)
when objname_qesrs is not null then 'SYS' else u.name end,
nvl -- OBJECT_NAME VARCHAR2(64)
(p.objname_qesrs, o.name),
substr -- OPTIMIZER VARCHAR2(20)
(opti_qesrs, 1, 20),
opid_qesrs, -- ID NUMBER
to_number -- PARENT_ID NUMBER
(decode (opid_qesrs, 0, NULL, paid_qesrs)),
depth_qesrs, -- DEPTH NUMBER
pos_qesrs, -- POSITION NUMBER
scols_qesrs, -- SEARCH_COLUMNS NUMBER
to_number -- COST NUMBER
(decode (cost_qesrs, 0, NULL, cost_qesrs)),
to_number -- CARDINALITY NUMBER
(decode (card_qesrs, 0, NULL, card_qesrs)),
to_number -- BYTES NUMBER
(decode (size_qesrs, 0, NULL, size_qesrs)),
substr -- OTHER_TAG VARCHAR2(35)
(otag_qesrs, 1, 35),
substr -- PARTITION_START VARCHAR2(5)
(psta_qesrs, 1, 5),
substr -- PARTITION_STOP VARCHAR2(5)
(psto_qesrs, 1, 5),
to_number -- PARTITION_ID NUMBER
(decode (pnid_qesrs, 0, NULL, pnid_qesrs)),
other_qesrs, -- OTHER VARCHAR2(4000)
substr -- DISTRIBUTION VARCHAR2(20)
(dist_qesrs, 1, 20),
to_number -- CPU_COST NUMBER
(decode (cpuc_qesrs, 0, NULL, cpuc_qesrs)),
to_number -- IO_COST NUMBER
(decode (ioct_qesrs, 0, NULL, ioct_qesrs)),
to_number -- TEMP_SPACE NUMBER
(decode (temp_qesrs, 0, NULL, temp_qesrs)),
KEYS_QESRS, -- ACCESS_PREDICATES VARCHAR2(4000)
FILTER_QESRS, -- FILTER_PREDICATES VARCHAR2(4000)
EXECS_QESRS, -- EXECUTIONS NUMBER
LSTARTS_QESRS, -- LAST_STARTS NUMBER
STARTS_QESRS, -- STARTS NUMBER
LOUTROWS_QESRS, -- LAST_OUTPUT_ROWS NUMBER
OUTROWS_QESRS, -- OUTPUT_ROWS NUMBER
LCRGETS_QESRS, -- LAST_CR_BUFFER_GETS NUMBER
CRGETS_QESRS, -- CR_BUFFER_GETS NUMBER
LCUGETS_QESRS, -- LAST_CU_BUFFER_GETS NUMBER
CUGETS_QESRS, -- CU_BUFFER_GETS NUMBER
LDREADS_QESRS, -- LAST_DISK_READS NUMBER
DREADS_QESRS, -- DISK_READS NUMBER
LDWRITES_QESRS, -- LAST_DISK_WRITES NUMBER
DWRITES_QESRS, -- DISK_WRITES NUMBER
LELAPTIME_QESRS, -- LAST_ELAPSED_TIME NUMBER
ELAPTIME_QESRS, -- ELAPSED_TIME NUMBER
substr -- POLICY VARCHAR2(10)
(SIZEPOLICY_QESRS, 1, 10),
OPTIMAL_QESRS * 1024, -- ESTIMATED_OPTIMAL_SIZE NUMBER
ONEPASS_QESRS * 1024, -- ESTIMATED_ONEPASS_SIZE NUMBER
LASTMEM_QESRS * 1024, -- LAST_MEMORY_USED NUMBER
substr -- LAST_EXECUTION VARCHAR2(10)
(decode (LASTPASS_QESRS, 0, 'OPTIMAL', to_char (LASTPASS_QESRS) || ' PASS' || decode (LASTPASS_QESRS, 1, '', 'ES')), 1, 10),
LASTDOP_QESRS, -- LAST_DEGREE NUMBER
(OPTACTS_QESRS -- TOTAL_EXECUTIONS NUMBER
+ SPAACTS_QESRS + MPAACTS_QESRS),
OPTACTS_QESRS, -- OPTIMAL_EXECUTIONS NUMBER
SPAACTS_QESRS, -- ONEPASS_EXECUTIONS NUMBER
MPAACTS_QESRS, -- MULTIPASSES_EXECUTIONS NUMBER
ATIME_QESRS, -- ACTIVE_TIME NUMBER
to_number -- MAX_TEMPSEG_SIZE NUMBER
(decode (MAXTSEG_QESRS, 0, NULL, MAXTSEG_QESRS)),
to_number -- LAST_TEMPSEG_SIZE NUMBER
(decode (LASTTSEG_QESRS, 0, NULL, LASTTSEG_QESRS))
from
X$QESRSTATALL p,
obj$ o,
user$ u
where
p.haddr_qesrs != p.phadd_qesrs and p.objn_qesrs = o.obj# (+) and o.owner# = u.user# (+)
GV$SQL_REDIRECTION
select
c.inst_id, -- INST_ID NUMBER
c.kglhdadr, -- ADDRESS RAW(4)
c.kglhdpar, -- PARENT_HANDLE RAW(4)
c.kglnahsh, -- HASH_VALUE NUMBER
c.kglobt09, -- CHILD_NUMBER NUMBER
c.kglobt17, -- PARSING_USER_ID NUMBER
c.kglobt18, -- PARSING_SCHEMA_ID NUMBER
c.kglobt02, -- COMMAND_TYPE NUMBER
decode -- REASON VARCHAR2(14)
(r.reason, 1, 'INVALID OBJECT', 2, 'ROWID', 3, 'QUERY REWRITE', 'READ ONLY'),
r.error_code, -- ERROR_CODE NUMBER
r.position, -- POSITION NUMBER
r.sql_text_piece, -- SQL_TEXT_PIECE VARCHAR2(1000)
r.error_msg -- ERROR_MESSAGE VARCHAR2(1000)
from
x$kglcursor c,
x$kkssrd r
where
c.kglhdadr != c.kglhdpar and c.kglobt02 != 0 and c.kglhdpar=r.parAddr and c.kglhdadr = r.kglhdadr
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 -- LOGMINER_SESSION_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 22)), POWER (2, 22), 'Y', 'N'),
decode -- INCOMP_LTRL_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 23)), POWER (2, 23), 'Y', 'N'),
decode -- OVERLAP_TIME_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 24)), POWER (2, 24), 'Y', 'N'),
decode -- SQL_REDIRECT_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 25)), POWER (2, 25), 'Y', 'N'),
decode -- MV_QUERY_GEN_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 26)), POWER (2, 26), 'Y', 'N'),
decode -- USER_BIND_PEEK_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 27)), POWER (2, 27), 'Y', 'N'),
decode -- TYPCHK_DEP_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 28)), POWER (2, 28), 'Y', 'N'),
decode -- NO_TRIGGER_MISMATCH VARCHAR2(1)
(bitand (bitvector, POWER (2, 29)), POWER (2, 29), 'Y', 'N'),
decode -- FLASHBACK_CURSOR VARCHAR2(1)
(bitand (bitvector, POWER (2, 30)), POWER (2, 30), '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$SQL_WORKAREA
SELECT
INST_ID, -- INST_ID NUMBER
PHADD_QKSMM, -- ADDRESS RAW(4)
HASHV_QKSMM, -- HASH_VALUE NUMBER
CHILDNO_QKSMM, -- CHILD_NUMBER NUMBER
WADDR_QKSMM, -- WORKAREA_ADDRESS RAW(4)
substr -- OPERATION_TYPE VARCHAR2(20)
(OPERTYPE_QKSMM, 1, 20),
to_number -- OPERATION_ID NUMBER
(decode (OPERTID_QKSMM, 65535, NULL, OPERTID_QKSMM)),
substr -- POLICY VARCHAR2(10)
(SIZEPOLICY_QKSMM, 1, 10),
OPTIMAL_QKSMM * 1024, -- ESTIMATED_OPTIMAL_SIZE NUMBER
ONEPASS_QKSMM * 1024, -- ESTIMATED_ONEPASS_SIZE NUMBER
LASTMEM_QKSMM * 1024, -- LAST_MEMORY_USED NUMBER
substr -- LAST_EXECUTION VARCHAR2(10)
(decode (LASTPASS_QKSMM, 0, 'OPTIMAL', to_char (LASTPASS_QKSMM) || ' PASS' || decode (LASTPASS_QKSMM, 1, '', 'ES')), 1, 10),
LASTDOP_QKSMM, -- LAST_DEGREE NUMBER
(OPTACTS_QKSMM -- TOTAL_EXECUTIONS NUMBER
+ SPAACTS_QKSMM + MPAACTS_QKSMM),
OPTACTS_QKSMM, -- OPTIMAL_EXECUTIONS NUMBER
SPAACTS_QKSMM, -- ONEPASS_EXECUTIONS NUMBER
MPAACTS_QKSMM, -- MULTIPASSES_EXECUTIONS NUMBER
ATIME_QKSMM, -- ACTIVE_TIME NUMBER
to_number -- MAX_TEMPSEG_SIZE NUMBER
(decode (MAXTSEG_QKSMM, 0, NULL, MAXTSEG_QKSMM*1024)),
to_number -- LAST_TEMPSEG_SIZE NUMBER
(decode (LASTTSEG_QKSMM, 0, NULL, LASTTSEG_QKSMM*1024))
FROM
X$QKSMMWDS
GV$SQL_WORKAREA_ACTIVE
select
INST_ID, -- INST_ID NUMBER
WADDR, -- WORKAREA_ADDRESS RAW(4)
substr -- OPERATION_TYPE VARCHAR2(20)
(OPER_TYPE, 1, 20),
to_number -- OPERATION_ID NUMBER
(decode (OPID, 65535, NULL, OPID)),
substr -- POLICY VARCHAR2(6)
(decode (bitand (MEM_FLAGS, 1), 0, 'MANUAL', 'AUTO'), 1, 6),
SID, -- SID NUMBER
to_number -- QCINST_ID NUMBER
(decode (QCINSTID, 65535, NULL, QCINSTID)),
to_number -- QCSID NUMBER
(decode (QCSID, 65535, NULL, QCSID)),
ATIME, -- ACTIVE_TIME NUMBER
WA_SIZE * 1024, -- WORK_AREA_SIZE NUMBER
to_number -- EXPECTED_SIZE NUMBER
(decode (bitand (MEM_FLAGS, 1), 0, NULL, EXP_SIZE*1024)),
ACTUAL_MEM * 1024, -- ACTUAL_MEM_USED NUMBER
MAX_MEM * 1024, -- MAX_MEM_USED NUMBER
PASSES, -- NUMBER_PASSES NUMBER
to_number -- TEMPSEG_SIZE NUMBER
(decode (KTSSOTSN, '', NULL, KTSSOSIZE*1024)),
decode -- TABLESPACE VARCHAR2(31)
(KTSSOTSN, '', NULL, KTSSOTSN),
to_number -- SEGRFNO# NUMBER
(decode (KTSSOTSN, '', NULL, KTSSORFNO)),
to_number -- SEGBLK# NUMBER
(decode (KTSSOTSN, '', NULL, KTSSOBNO))
from
x$qesmmiwt
GV$SQL_WORKAREA_HISTOGRAM
select
INST_ID, -- INST_ID NUMBER
LOWBND * 1024, -- LOW_OPTIMAL_SIZE NUMBER
(HIBND * 1024) -1, -- HIGH_OPTIMAL_SIZE NUMBER
OPTIMAL, -- OPTIMAL_EXECUTIONS NUMBER
ONEPASS, -- ONEPASS_EXECUTIONS NUMBER
MPASS, -- MULTIPASSES_EXECUTIONS NUMBER
MPASS+ONEPASS+OPTIMAL -- TOTAL_EXECUTIONS NUMBER
from
X$QESMMIWH
GV$STANDBY_LOG
select
inst_id, -- INST_ID NUMBER
slnum, -- GROUP# NUMBER
slthr, -- THREAD# NUMBER
slseq, -- SEQUENCE# NUMBER
slsiz*slbsz, -- BYTES NUMBER
slnab*slbsz, -- USED NUMBER
decode -- ARCHIVED VARCHAR2(3)
(bitand (slflg, 1), 0, 'NO', 'YES'),
decode -- STATUS VARCHAR2(10)
(sign (slseq), 0, 'UNASSIGNED', 'ACTIVE'),
to_number (sllos), -- FIRST_CHANGE# NUMBER
to_date -- FIRST_TIME DATE
(sllot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_number (slnxs), -- LAST_CHANGE# NUMBER
to_date -- LAST_TIME DATE
(slnxt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')
from
x$kccsl
GV$STATISTICS_LEVEL
select
inst_id, -- INST_ID NUMBER
name, -- STATISTICS_NAME VARCHAR2(64)
description, -- DESCRIPTION VARCHAR2(4000)
decode -- SESSION_STATUS VARCHAR2(8)
(session_status, 0, 'DISABLED', 1, 'ENABLED', 'UNKNOWN'),
decode -- SYSTEM_STATUS VARCHAR2(8)
(system_status, 0, 'DISABLED', 1, 'ENABLED', 'UNKNOWN'),
decode -- ACTIVATION_LEVEL VARCHAR2(7)
(activation_level, 0, 'BASIC', 1, 'TYPICAL', 'ALL'),
view_name, -- STATISTICS_VIEW_NAME VARCHAR2(64)
decode -- SESSION_SETTABLE VARCHAR2(3)
(session_changeable, 0, 'NO', 'YES')
from
x$prmsltyx
GV$STATNAME
select
inst_id, -- INST_ID NUMBER
indx, -- STATISTIC# NUMBER
ksusdnam, -- NAME VARCHAR2(64)
ksusdcls -- CLASS NUMBER
from
x$ksusd
GV$STREAMS_APPLY_COORDINATOR
select
inst_id, -- INST_ID NUMBER
sid_knst, -- SID NUMBER
serial_knst, -- SERIAL# NUMBER
applynum_knstacr, -- APPLY# NUMBER
applyname_knstacr, -- APPLY_NAME VARCHAR2(30)
decode -- STATE VARCHAR2(21)
(state_knstacr, 0, 'INITIALIZING', 1, 'APPLYING', 2, 'SHUTTING DOWN CLEANLY', 3, 'ABORTING'),
total_applied_knstacr, -- TOTAL_APPLIED NUMBER
total_waitdeps_knstacr, -- TOTAL_WAIT_DEPS NUMBER
total_waitcommits_knstacr, -- TOTAL_WAIT_COMMITS NUMBER
total_admin_knstacr, -- TOTAL_ADMIN NUMBER
total_assigned_knstacr, -- TOTAL_ASSIGNED NUMBER
total_received_knstacr, -- TOTAL_RECEIVED NUMBER
total_errors_knstacr, -- TOTAL_ERRORS NUMBER
lwm_time_knstacr, -- LWM_TIME DATE
lwm_msg_num_knstacr, -- LWM_MESSAGE_NUMBER NUMBER
lwm_msg_time_knstacr, -- LWM_MESSAGE_CREATE_TIME DATE
hwm_time_knstacr, -- HWM_TIME DATE
hwm_msg_num_knstacr, -- HWM_MESSAGE_NUMBER NUMBER
hwm_msg_time_knstacr, -- HWM_MESSAGE_CREATE_TIME DATE
startup_time_knstacr, -- STARTUP_TIME DATE
elapsed_schedule_time_knstacr -- ELAPSED_SCHEDULE_TIME NUMBER
from
x$knstacr x
where
type_knst=1 and exists (
select
1
from
v$session s
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
GV$STREAMS_APPLY_READER
select
inst_id, -- INST_ID NUMBER
sid_knst, -- SID NUMBER
serial_knst, -- SERIAL# NUMBER
applynum_knstasl, -- APPLY# NUMBER
applyname_knstasl, -- APPLY_NAME VARCHAR2(30)
decode -- STATE VARCHAR2(17)
(state_knstasl, 0, 'IDLE', 8, 'DEQUEUE MESSAGES', 10, 'SCHEDULE MESSAGES'),
total_msg_knstasl, -- TOTAL_MESSAGES_DEQUEUED NUMBER
last_rcv_time_knstasl, -- DEQUEUE_TIME DATE
last_rcv_msg_num_knstasl, -- DEQUEUED_MESSAGE_NUMBER NUMBER
last_rcv_msg_time_knstasl, -- DEQUEUED_MESSAGE_CREATE_TIME DATE
sga_used_knstasl, -- SGA_USED NUMBER
elapsed_dequeue_time_knstasl, -- ELAPSED_DEQUEUE_TIME NUMBER
elapsed_schedule_time_knstasl -- ELAPSED_SCHEDULE_TIME NUMBER
from
x$knstasl x
where
type_knst=7 and exists (
select
1
from
v$session s
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
GV$STREAMS_APPLY_SERVER
select
inst_id, -- INST_ID NUMBER
sid_knst, -- SID NUMBER
serial_knst, -- SERIAL# NUMBER
applynum_knstasl, -- APPLY# NUMBER
applyname_knstasl, -- APPLY_NAME VARCHAR2(30)
slavid_knstasl, -- SERVER_ID NUMBER
decode -- STATE VARCHAR2(20)
(state_knstasl, 0, 'IDLE', 1, 'POLL SHUTDOWN', 2, 'RECORD LOW-WATERMARK', 3, 'ADD PARTITION', 4, 'DROP PARTITION', 5, 'EXECUTE TRANSACTION', 6, 'WAIT COMMIT', 7, 'WAIT DEPENDENCY', 8, 'GET TRANSACTIONS', 9, 'WAIT FOR NEXT CHUNK'),
xid_usn_knstasl, -- XIDUSN NUMBER
xid_slt_knstasl, -- XIDSLT NUMBER
xid_sqn_knstasl, -- XIDSQN NUMBER
cscn_knstasl, -- COMMITSCN NUMBER
depxid_usn_knstasl, -- DEP_XIDUSN NUMBER
depxid_slt_knstasl, -- DEP_XIDSLT NUMBER
depxid_sqn_knstasl, -- DEP_XIDSQN NUMBER
depcscn_knstasl, -- DEP_COMMITSCN NUMBER
msg_num_knstasl, -- MESSAGE_SEQUENCE NUMBER
total_assigned_knstasl, -- TOTAL_ASSIGNED NUMBER
total_admin_knstasl, -- TOTAL_ADMIN NUMBER
total_msg_knstasl, -- TOTAL_MESSAGES_APPLIED NUMBER
last_apply_time_knstasl, -- APPLY_TIME DATE
last_apply_msg_num_knstasl, -- APPLIED_MESSAGE_NUMBER NUMBER
last_apply_msg_time_knstasl, -- APPLIED_MESSAGE_CREATE_TIME DATE
elapsed_dequeue_time_knstasl, -- ELAPSED_DEQUEUE_TIME NUMBER
elapsed_apply_time_knstasl -- ELAPSED_APPLY_TIME NUMBER
from
x$knstasl x
where
type_knst=2 and exists (
select
1
from
v$session s
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
GV$STREAMS_CAPTURE
select
inst_id, -- INST_ID NUMBER
sid_knst, -- SID NUMBER
serial_knst, -- SERIAL# NUMBER
capnum_knstcap, -- CAPTURE# NUMBER
capname_knstcap, -- CAPTURE_NAME VARCHAR2(30)
startup_time_knstcap, -- STARTUP_TIME DATE
decode -- STATE VARCHAR2(17)
(state_knstcap, 0, 'INITIALIZING', 1, 'CAPTURING CHANGES', 2, 'EVALUATING RULE', 3, 'ENQUEUING MESSAGE', 4, 'SHUTTING DOWN', 5, 'ABORTING', 6, 'CREATING LCR'),
total_captured_knstcap, -- TOTAL_MESSAGES_CAPTURED NUMBER
recent_time_knstcap, -- CAPTURE_TIME DATE
recent_msg_num_knstcap, -- CAPTURE_MESSAGE_NUMBER NUMBER
recent_msg_time_knstcap, -- CAPTURE_MESSAGE_CREATE_TIME DATE
total_msg_enq_knstcap, -- TOTAL_MESSAGES_ENQUEUED NUMBER
enqueue_time_knstcap, -- ENQUEUE_TIME DATE
enqueue_msg_num_knstcap, -- ENQUEUE_MESSAGE_NUMBER NUMBER
enqueue_msg_time_knstcap, -- ENQUEUE_MESSAGE_CREATE_TIME DATE
elapsed_capture_time_knstcap, -- ELAPSED_CAPTURE_TIME NUMBER
elapsed_rule_time_knstcap, -- ELAPSED_RULE_TIME NUMBER
elapsed_enqueue_time_knstcap, -- ELAPSED_ENQUEUE_TIME NUMBER
elapsed_lcr_time_knstcap -- ELAPSED_LCR_TIME NUMBER
from
x$knstcap x
where
type_knst=8 and exists (
select
1
from
v$session s
where
s.sid=x.sid_knst and s.serial#=x.serial_knst)
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
round -- TIME_WAITED NUMBER
(s.kslestim / 10000),
round -- AVERAGE_WAIT NUMBER
(s.kslestim / (10000 * s.ksleswts)),
s.kslestim -- TIME_WAITED_MICRO 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)
ksppstcmnt -- UPDATE_COMMENT VARCHAR2(255)
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)
kspftctxvn, -- ORDINAL NUMBER
kspftctxct -- UPDATE_COMMENT VARCHAR2(255)
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)
decode -- INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
(bitand (tsflg, 2), 2, 'NO', 'YES')
from
x$kccts
where
tstsn != -1
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
sum (kdltctmp), -- CACHE_LOBS NUMBER
sum (kdltnctmp) -- NOCACHE_LOBS NUMBER
from
X$KDLT
group by inst_id,
kdltsno 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.kcftiosbr, -- SINGLEBLKRDS NUMBER
k.kcftioprt, -- READTIM NUMBER
k.kcftiopwt, -- WRITETIM NUMBER
k.kcftiosbt, -- SINGLEBLKRDTIM 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_CACHE_TRANSFER
select
x.inst_id, -- INST_ID NUMBER
kcftiofno, -- FILE_NUMBER 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
KCFTIOS2NC, -- S_2_NULL NUMBER
KCFTIOS2NFSC, -- S_2_NULL_FORCED_STALE NUMBER
KCFTIORBRC, -- RBR NUMBER
KCFTIORBRFWC, -- RBR_FORCED_WRITE NUMBER
KCFTION2XC, -- NULL_2_X NUMBER
KCFTIOS2XC, -- S_2_X NUMBER
KCFTION2SC -- NULL_2_S NUMBER
from
x$kcftio x,
x$kcctf tf
where
x.kcftiofno = tf.tfnum
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$TIMEZONE_NAMES
select
TZNAME, -- TZNAME VARCHAR2(64)
TZABBREV -- TZABBREV VARCHAR2(64)
from
X$TIMEZONE_NAMES
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'),
ktcxbnam, -- NAME VARCHAR2(256)
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.ktcxbxba, -- ADDR RAW(4)
l.ktcxblkp, -- 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$UNDOSTAT
select
inst_id, -- INST_ID NUMBER
to_date -- BEGIN_TIME DATE
(KTUSMSTRBEGTIME, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
to_date -- END_TIME DATE
(KTUSMSTRENDTIME, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
KTUSMSTTSN, -- UNDOTSN NUMBER
KTUSMSTUSU, -- UNDOBLKS NUMBER
KTUSMSTTCT, -- TXNCOUNT NUMBER
KTUSMSTMQL, -- MAXQUERYLEN NUMBER
KTUSMSTMTC, -- MAXCONCURRENCY NUMBER
KTUSMSTUAC, -- UNXPSTEALCNT NUMBER
KTUSMSTUBS, -- UNXPBLKRELCNT NUMBER
KTUSMSTUBR, -- UNXPBLKREUCNT NUMBER
KTUSMSTXAC, -- EXPSTEALCNT NUMBER
KTUSMSTXBS, -- EXPBLKRELCNT NUMBER
KTUSMSTXBR, -- EXPBLKREUCNT NUMBER
KTUSMSTSOC, -- SSOLDERRCNT NUMBER
KTUSMSTOOS -- NOSPACEERRCNT NUMBER
from
X$KTUSMST
GV$VERSION
select
inst_id, -- INST_ID NUMBER
banner -- BANNER VARCHAR2(64)
from
x$version
GV$VPD_POLICY
select
c.inst_id, -- INST_ID NUMBER
c.kglhdadr, -- ADDRESS RAW(4)
c.kglhdpar, -- PARADDR RAW(4)
c.kglnahsh, -- SQL_HASH NUMBER
c.kglobt09, -- CHILD_NUMBER NUMBER
p.kzrtpdow, -- OBJECT_OWNER VARCHAR2(30)
p.kzrtpdon, -- OBJECT_NAME VARCHAR2(30)
p.kzrtpdgp, -- POLICY_GROUP VARCHAR2(30)
p.kzrtpdpy, -- POLICY VARCHAR2(30)
p.kzrtpdpo, -- POLICY_FUNCTION_OWNER VARCHAR2(30)
p.kzrtpdtx -- PREDICATE VARCHAR2(4096)
from
x$kglcursor c,
x$kzrtpd p
where
c.kglhdadr != c.kglhdpar and c.kglobt02 != 0 and c.kglhdpar=p.kzrtpdpa and c.kglhdadr = p.kzrtpdad
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, '1st level bmb', 9, '2nd level bmb', 10, '3rd level bmb', 11, 'bitmap block', 12, 'bitmap index block', 13, 'file header block', 14, 'unused', 15, 'system undo header', 16, 'system undo block', 17, 'undo header', 18, '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
ktcxbxba, -- LADDR RAW(4)
ktcxblkp, -- 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