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