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_SERVICES
select inst_id, -- INST_ID NUMBER kswsastabsi, -- SERVICE_ID NUMBER kswsastabnm, -- NAME VARCHAR2(64) kswsastabnmh, -- NAME_HASH NUMBER kswsastabnn, -- NETWORK_NAME VARCHAR2(512) kswsastabcd, -- CREATION_DATE DATE kswsastabcdh -- CREATION_DATE_HASH NUMBER from x$kswsastab where kswsastabact = 1
GV$ACTIVE_SESSION_HISTORY
SELECT /*+ no_merge ordered use_nl (s, a) */ a.inst_id, -- INST_ID NUMBER s.sample_id, -- SAMPLE_ID NUMBER s.sample_time, -- SAMPLE_TIME TIMESTAMP(3) a.session_id, -- SESSION_ID NUMBER a.session_serial#, -- SESSION_SERIAL# NUMBER a.user_id, -- USER_ID NUMBER a.sql_id, -- SQL_ID VARCHAR2(13) a.sql_child_number, -- SQL_CHILD_NUMBER NUMBER a.sql_plan_hash_value, -- SQL_PLAN_HASH_VALUE NUMBER a.sql_opcode, -- SQL_OPCODE NUMBER a.service_hash, -- SERVICE_HASH NUMBER decode -- SESSION_TYPE VARCHAR2(10) (a.session_type, 1, 'FOREGROUND', 2, 'BACKGROUND', 'UNKNOWN'), decode -- SESSION_STATE VARCHAR2(7) (a.wait_time, 0, 'WAITING', 'ON CPU'), a.qc_session_id, -- QC_SESSION_ID NUMBER a.qc_instance_id, -- QC_INSTANCE_ID NUMBER a.event, -- EVENT VARCHAR2(64) a.event_id, -- EVENT_ID NUMBER a.event#, -- EVENT# NUMBER a.seq#, -- SEQ# NUMBER a.p1, -- P1 NUMBER a.p2, -- P2 NUMBER a.p3, -- P3 NUMBER a.wait_time, -- WAIT_TIME NUMBER a.time_waited, -- TIME_WAITED NUMBER a.current_obj#, -- CURRENT_OBJ# NUMBER a.current_file#, -- CURRENT_FILE# NUMBER a.current_block#, -- CURRENT_BLOCK# NUMBER a.program, -- PROGRAM VARCHAR2(48) a.module, -- MODULE VARCHAR2(48) a.action, -- ACTION VARCHAR2(32) a.client_id -- CLIENT_ID VARCHAR2(64) FROM x$kewash s, x$ash a WHERE s.sample_addr = a.sample_addr and s.sample_id = a.sample_id and s.sample_time = a.sample_time
GV$ACTIVE_SESS_POOL_MTH
select inst_id, -- INST_ID NUMBER policy_name_kgskasp -- NAME VARCHAR2(40) from x$kgskasp
GV$ADVISOR_PROGRESS
select inst_id, -- INST_ID NUMBER ksulosno, -- SID NUMBER ksulosrn, -- SERIAL# NUMBER ksulounm, -- USERNAME VARCHAR2(30) ksulopna, -- OPNAME VARCHAR2(64) ksuloctx, -- TASK_ID NUMBER ksulotna, -- TARGET VARCHAR2(64) ksulotde, -- TARGET_DESC VARCHAR2(32) ksulosfr, -- SOFAR NUMBER ksulotot, -- TOTALWORK NUMBER ksulouni, -- UNITS VARCHAR2(32) ksuloif0, -- FINDINGS NUMBER 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 ksuloif1d, -- INFO1_DESC VARCHAR2(64) decode -- INFO1 NUMBER (ksuloif1d, null, to_number (null), ksuloif1), ksuloif2d, -- INFO2_DESC VARCHAR2(64) decode -- INFO2 NUMBER (ksuloif2d, null, to_number (null), ksuloif2), ksuloif3d, -- INFO3_DESC VARCHAR2(64) decode -- INFO3 NUMBER (ksuloif3d, null, to_number (null), ksuloif3), ksuloif4d, -- INFO4_DESC VARCHAR2(64) decode -- INFO4 NUMBER (ksuloif4d, null, to_number (null), ksuloif4) from x$ksulop where ksulopna in ('SQL Tune')
GV$ALERT_TYPES
SELECT t.inst_id, -- INST_ID NUMBER rid_keltsd, -- REASON_ID NUMBER typnam_keltosd, -- OBJECT_TYPE VARCHAR2(64) decode -- TYPE VARCHAR2(9) (typ_keltsd, 1, 'Stateful', 'Stateless'), nam_keltgsd, -- GROUP_NAME VARCHAR2(64) decode -- SCOPE VARCHAR2(8) (scp_keltsd, 1, 'Database', 'Instance'), mtn_keltsd, -- INTERNAL_METRIC_CATEGORY VARCHAR2(64) mtc_keltsd -- INTERNAL_METRIC_NAME VARCHAR2(64) FROM x$keltsd t, x$keltosd, x$keltgsd WHERE grp_keltsd = id_keltgsd AND otyp_keltsd = typid_keltosd
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 (alxlc), -- RESETLOGS_ID NUMBER 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 decode -- IS_RECOVERY_DEST_FILE VARCHAR2(3) (bitand (alfl2, 64), 0, 'NO', 'YES'), decode -- COMPRESSED VARCHAR2(3) (bitand (alfl2, 128), 0, 'NO', 'YES'), decode -- FAL VARCHAR2(3) (bitand (alflg, 512), 0, 'NO', 'YES') 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', 4, 'ONDEMAND', 'UNKNOWN'), decode -- SCHEDULE VARCHAR2(8) (ADSCH, 0, 'INACTIVE', 1, 'PENDING', 2, 'ACTIVE', 3, 'LATENT', 'UNKNOWN'), decode -- DESTINATION VARCHAR2(256) (ADOMF, 0, ADDNM, 'USE_DB_RECOVERY_FILE_DEST'), 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', 4, 'ONDEMAND', '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'), decode -- VALID_NOW VARCHAR2(16) (ADNOW, 1, 'YES', 2, 'WRONG VALID_TYPE', 3, 'WRONG VALID_ROLE', 4, 'INACTIVE', 'UNKNOWN'), decode -- VALID_TYPE VARCHAR2(15) (ADVLD, 11, 'ONLINE_LOGFILE', 12, 'ONLINE_LOGFILE', 13, 'ONLINE_LOGFILE', 21, 'STANDBY_LOGFILE', 22, 'STANDBY_LOGFILE', 23, 'STANDBY_LOGFILE', 31, 'ALL_LOGFILES', 32, 'ALL_LOGFILES', 33, 'ALL_LOGFILES', 'UNKNOWN'), decode -- VALID_ROLE VARCHAR2(12) (ADVLD, 11, 'PRIMARY_ROLE', 12, 'STANDBY_ROLE', 13, 'ALL_ROLES', 21, 'PRIMARY_ROLE', 22, 'STANDBY_ROLE', 23, 'ALL_ROLES', 31, 'PRIMARY_ROLE', 32, 'STANDBY_ROLE', 33, 'ALL_ROLES', 'UNKNOWN'), ADDBUN, -- DB_UNIQUE_NAME VARCHAR2(30) decode -- VERIFY VARCHAR2(3) (ADVER, 0, 'NO', 'YES'), decode -- EXPEDITE VARCHAR2(3) (ADEXP, 0, 'NO', 'YES') 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(23) (DSRMD, 1, 'IDLE', 2, 'MANUAL', 3, 'MANAGED', 4, 'MANAGED REAL TIME APPLY', 5, 'LOGICAL REAL TIME APPLY', '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'), DSDBUN -- DB_UNIQUE_NAME VARCHAR2(30) from x$kcrrdstat
GV$ARCHIVE_GAP
select USERENV ('Instance'), -- INST_ID NUMBER high.thread#, -- THREAD# NUMBER low.lsq, -- LOW_SEQUENCE# NUMBER high.hsq -- HIGH_SEQUENCE# NUMBER from ( select a.thread#, rcvsq, min (a.sequence#) -1 hsq from v$archived_log a, ( select thread#, max (sequence#) rcvsq from v$log_history group by thread#) b where a.thread# = b.thread# and a.sequence# > rcvsq group by a.thread#, rcvsq) high, ( select thread#, min (sequence#) +1 lsq from v$log_history, v$datafile where checkpoint_change# <= next_change# and checkpoint_change# >= first_change# and enabled = 'READ WRITE' group by thread#) low where low.thread# = high.thread# and lsq < = hsq and hsq > rcvsq
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$ASM_ALIAS
select inst_id, -- INST_ID NUMBER name_kfals, -- NAME VARCHAR2(48) group_kfals, -- GROUP_NUMBER NUMBER number_kfals, -- FILE_NUMBER NUMBER incarn_kfals, -- FILE_INCARNATION NUMBER entnum_kfals, -- ALIAS_INDEX NUMBER entinc_kfals, -- ALIAS_INCARNATION NUMBER parent_kfals, -- PARENT_INDEX NUMBER refer_kfals, -- REFERENCE_INDEX NUMBER decode -- ALIAS_DIRECTORY VARCHAR2(1) (bitand (entflg_kfals, 12), 4, 'Y', 8, 'Y', 'N'), decode -- SYSTEM_CREATED VARCHAR2(1) (bitand (entflg_kfals, 15), 1, 'N', 2, 'Y', 4, 'Y', 8, 'N') from x$kfals where decode (bitand (entflg_kfals, 12), 4, 'Y', 8, 'Y', 'N') = 'Y' OR decode (bitand (entflg_kfals, 16), 16, 'Y', 'N') = 'Y'
GV$ASM_CLIENT
select inst_id, -- INST_ID NUMBER gn_kfncl, -- GROUP_NUMBER NUMBER instname_kfncl, -- INSTANCE_NAME VARCHAR2(64) dbname_kfncl, -- DB_NAME VARCHAR2(8) decode -- STATUS VARCHAR2(12) (status_kfncl, 1, 'CONNECTED', 2, 'DISCONNECTED', 3, 'BROKEN') from x$kfncl
GV$ASM_DISK
select d.inst_id, -- INST_ID NUMBER d.grpnum_kfdsk, -- GROUP_NUMBER NUMBER d.number_kfdsk, -- DISK_NUMBER NUMBER d.compound_kfdsk, -- COMPOUND_INDEX NUMBER d.incarn_kfdsk, -- INCARNATION NUMBER decode -- MOUNT_STATUS VARCHAR2(7) (d.mntsts_kfdsk, 1, 'MISSING', 2, 'CLOSED', 3, 'OPENED', 4, 'CACHED', 'INVALID'), decode -- HEADER_STATUS VARCHAR2(12) (d.hdrsts_kfdsk, 1, 'UNKNOWN', 2, 'CANDIDATE', 3, 'MEMBER', 4, 'FORMER', 5, 'CONFLICT', 6, 'INCOMPATIBLE', 7, 'PROVISIONED', 'INVALID'), decode -- MODE_STATUS VARCHAR2(7) (d.mode_kfdsk, 0, 'UNKNOWN', 4, 'OFFLINE', 5, 'PENDING', 6, 'PROTECT', 7, 'ONLINE', 'INVALID'), decode -- STATE VARCHAR2(8) (d.state_kfdsk, 1, 'UNKNOWN', 2, 'NORMAL', 3, 'FAILING', 4, 'DROPPING', 5, 'HUNG', 6, 'FORCING', 7, 'DROPPED', 8, 'ADDING', 'INVALID'), decode -- REDUNDANCY VARCHAR2(7) (d.redun_kfdsk, 16, 'UNPROT', 17, 'UNPROT', 18, 'MIRROR', 19, 'MIRROR', 20, 'MIRROR', 21, 'MIRROR', 22, 'MIRROR', 23, 'MIRROR', 32, 'PARITY', 33, 'PARITY', 34, 'PARITY', 35, 'PARITY', 36, 'PARITY', 37, 'PARITY', 38, 'PARITY', 39, 'PARITY', 'UNKNOWN'), d.libnam_kfdsk, -- LIBRARY VARCHAR2(64) d.totmb_kfdsk, -- TOTAL_MB NUMBER d.totmb_kfdsk -- FREE_MB NUMBER - d.usedmb_kfdsk, d.asmname_kfdsk, -- NAME VARCHAR2(30) d.failname_kfdsk, -- FAILGROUP VARCHAR2(30) d.label_kfdsk, -- LABEL VARCHAR2(31) d.path_kfdsk, -- PATH VARCHAR2(256) d.udid_kfdsk, -- UDID VARCHAR2(64) d.crdate_kfdsk, -- CREATE_DATE DATE d.mtdate_kfdsk, -- MOUNT_DATE DATE d.timer_kfdsk, -- REPAIR_TIMER NUMBER k.read_kfkid, -- READS NUMBER k.write_kfkid, -- WRITES NUMBER k.rerr_kfkid, -- READ_ERRS NUMBER k.werr_kfkid, -- WRITE_ERRS NUMBER k.rtime_kfkid, -- READ_TIME NUMBER k.wtime_kfkid, -- WRITE_TIME NUMBER k.bytesr_kfkid, -- BYTES_READ NUMBER k.bytesw_kfkid -- BYTES_WRITTEN NUMBER from x$kfdsk d, x$kfkid k where d.mntsts_kfdsk != 0 and d.kfkid_kfdsk = k.idptr_kfkid (+)
GV$ASM_DISKGROUP
select g.inst_id, -- INST_ID NUMBER g.number_kfgrp, -- GROUP_NUMBER NUMBER g.name_kfgrp, -- NAME VARCHAR2(30) g.sector_kfgrp, -- SECTOR_SIZE NUMBER g.blksize_kfgrp, -- BLOCK_SIZE NUMBER g.ausize_kfgrp, -- ALLOCATION_UNIT_SIZE NUMBER decode -- STATE VARCHAR2(11) (g.state_kfgrp, 0, 'INVALID', 1, 'UNKNOWN', 2, 'DISMOUNTED', 3, 'CREATING', 4, 'MOUNTING', 5, 'MOUNTED', 6, 'DISMOUNTING', 7, 'CONNECTED', 8, 'BROKEN', 9, 'CONNECTING', 10, 'BREAKING', 11, 'DROPPING'), decode -- TYPE VARCHAR2(6) (g.type_kfgrp, 1, 'EXTERN', 2, 'NORMAL', 3, 'HIGH'), ( select sum (d.totmb_kfdsk) from x$kfdsk d where g.number_kfgrp = d.grpnum_kfdsk), ( select sum (d.totmb_kfdsk - d.usedmb_kfdsk) from x$kfdsk d where g.number_kfgrp = d.grpnum_kfdsk) from x$kfgrp g where state_kfgrp != 0
GV$ASM_FILE
select inst_id, -- INST_ID NUMBER group_kffil, -- GROUP_NUMBER NUMBER number_kffil, -- FILE_NUMBER NUMBER compound_kffil, -- COMPOUND_INDEX NUMBER incarn_kffil, -- INCARNATION NUMBER blksiz_kffil, -- BLOCK_SIZE NUMBER blkcnt_kffil, -- BLOCKS NUMBER filsiz_kffil, -- BYTES NUMBER filspc_kffil, -- SPACE NUMBER sftype_kffil, -- TYPE VARCHAR2(64) decode -- REDUNDANCY VARCHAR2(6) (redun_kffil, 17, 'UNPROT', 18, 'MIRROR', 19, 'MIRROR', 35, 'PARITY', 36, 'PARITY', 37, 'PARITY', 38, 'PARITY'), decode -- STRIPED VARCHAR2(6) (bitand (fdflg_kffil, 2), 2, 'FINE', 'COARSE'), crdate_kffil, -- CREATION_DATE DATE mddate_kffil -- MODIFICATION_DATE DATE from x$kffil where incarn_kffil <> 0 and number_kffil > 255
GV$ASM_OPERATION
select inst_id, -- INST_ID NUMBER number_kfgmg, -- GROUP_NUMBER NUMBER decode -- OPERATION VARCHAR2(5) (bitand (op_kfgmg, 64), 64, 'EXPEL', decode (bitand (op_kfgmg, 32), 32, 'RFRSH', decode (bitand (op_kfgmg, 16), 16, 'ACD', decode (bitand (op_kfgmg, 8), 8, 'DSCV', decode (bitand (op_kfgmg, 4), 4, 'SYNC', decode (bitand (op_kfgmg, 2), 2, 'COD', decode (bitand (op_kfgmg, 1), 1, 'REBAL', 'NONE'))))))), decode -- STATE VARCHAR2(4) (state_kfgmg, 1, 'WAIT', 2, 'RUN', 3, 'REAP'), power_kfgmg, -- POWER NUMBER actual_kfgmg, -- ACTUAL NUMBER sofar_kfgmg, -- SOFAR NUMBER work_kfgmg, -- EST_WORK NUMBER rate_kfgmg, -- EST_RATE NUMBER time_kfgmg -- EST_MINUTES NUMBER from x$kfgmg
GV$ASM_TEMPLATE
select inst_id, -- INST_ID NUMBER group_kftmta, -- GROUP_NUMBER NUMBER entry_kftmta, -- ENTRY_NUMBER NUMBER decode -- REDUNDANCY VARCHAR2(6) (redundancy_kftmta, 17, 'UNPROT', 18, 'MIRROR', 19, 'MIRROR', 35, 'PARITY', 36, 'PARITY', 37, 'PARITY', 38, 'PARITY'), decode -- STRIPE VARCHAR2(6) (bitand (flags_kftmta, 1), 1, 'FINE', 'COARSE'), decode -- SYSTEM VARCHAR2(1) (bitand (flags_kftmta, 4), 4, 'Y', 'N'), name_kftmta -- NAME VARCHAR2(30) from x$kftmta
GV$AW_AGGREGATE_OP
select inst_id, -- INST_ID NUMBER name_xsagopft as name, -- NAME VARCHAR2(14) desc_xsagopft -- LONGNAME VARCHAR2(30) as longname, case -- DEFAULT_WEIGHT NUMBER when weight_xsagopft >= 0 then weight_xsagopft else null end as default_weight from x$xsagop where not bitand (flags_xsagopft, 32) = 0
GV$AW_ALLOCATE_OP
select inst_id, -- INST_ID NUMBER name_xsagopft as name, -- NAME VARCHAR2(14) desc_xsagopft -- LONGNAME VARCHAR2(30) as longname from x$xsagop where not bitand (flags_xsagopft, 64) = 0
GV$AW_CALC
select inst_id, -- INST_ID NUMBER session_id, -- SESSION_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 cdmlcmd, -- CURR_DML_COMMAND VARCHAR2(64) pdmlcmd, -- PREV_DML_COMMAND VARCHAR2(64) aggr_func_logical_na, -- AGGR_FUNC_LOGICAL_NA NUMBER aggr_func_precompute, -- AGGR_FUNC_PRECOMPUTE NUMBER aggr_func_calcs -- AGGR_FUNC_CALCS NUMBER from x$xsaggr
GV$AW_LONGOPS
select inst_id, -- INST_ID NUMBER session_id, -- SESSION_ID NUMBER cursor_name, -- CURSOR_NAME VARCHAR2(64) decode -- COMMAND VARCHAR2(7) (command, 1, 'FETCH', 2, 'IMPORT', 3, 'EXECUTE', '?'), decode -- STATUS VARCHAR2(9) (status, 4, 'EXECUTING', 5, 'FETCHING', 6, 'FINISHED', '?'), rows_processed, -- ROWS_PROCESSED NUMBER start_time -- START_TIME TIMESTAMP(3) from x$xslongops
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 session_id, -- SESSION_ID NUMBER client, -- CLIENT_TYPE VARCHAR2(64) state, -- SESSION_STATE VARCHAR2(64) sesshandle, -- SESSION_HANDLE NUMBER userid, -- USERID 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 rman_status_recid, -- RMAN_STATUS_RECID NUMBER rman_status_stamp, -- RMAN_STATUS_STAMP 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'), abs -- ELAPSED_TIME NUMBER ((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')) * 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) / abs (((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', decode (to_number (fcscn), 0, 'UNKNOWN', '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), decode -- USED_CHANGE_TRACKING VARCHAR2(3) (bitand (bfflg, 4), 4, 'YES', 'NO'), bfbrd -- BLOCKS_READ NUMBER from x$kccbf
GV$BACKUP_DEVICE
select inst_id, -- INST_ID NUMBER devtype, -- DEVICE_TYPE VARCHAR2(17) devname -- DEVICE_NAME VARCHAR2(513) from x$ksfqdvnt
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(64) 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'), abs -- ELAPSED_SECONDS NUMBER ((to_date (bptim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (bptsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) *86400), ((floor -- BYTES NUMBER (bpext/512) * 4294967296) + bpsz1) * 512, decode -- IS_RECOVERY_DEST_FILE VARCHAR2(3) (bitand (bpflg, 16384), 0, 'NO', 'YES'), bprsi, -- RMAN_STATUS_RECID NUMBER bprst, -- RMAN_STATUS_STAMP NUMBER decode -- COMPRESSED VARCHAR2(3) (bitand (bpext, 64), 64, 'YES', 'NO') 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'), decode -- COMPLETION_TIME DATE (bitand (bstyp, 4096), 4096, to_date (bsbst, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), to_date (bstsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')), decode -- ELAPSED_SECONDS NUMBER (bitand (bstyp, 4096), 4096, 0, abs ((to_date (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 where bitand (bstyp, 32) != 32
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 rman_status_recid, -- RMAN_STATUS_RECID NUMBER rman_status_stamp, -- RMAN_STATUS_STAMP 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'), abs -- ELAPSED_TIME NUMBER ((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')) * 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) / abs (((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.ksbdpser, -- PSERIAL# NUMBER 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 and p.ksbdpnam not like 'TEST%'
GV$BH
select bh.inst_id, -- INST_ID NUMBER file#, -- FILE# NUMBER dbablk, -- BLOCK# NUMBER class, -- CLASS# NUMBER decode -- STATUS VARCHAR2(7) (state, 0, 'free', 1, 'xcur', 2, 'scur', 3, 'cr', 4, 'read', 5, 'mrec', 6, 'irec', 7, 'write', 8, 'pi', 9, 'memory', 10, 'mwrite', 11, 'donated'), 0, -- XNC NUMBER 0, -- FORCED_READS NUMBER 0, -- 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 CHAR(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 0, -- FREE_LOCK_ELEMENTS NUMBER flush, -- FLUSHES NUMBER 0, -- FLUSHES_QUEUED NUMBER flushf, -- FLUSH_QUEUE_FULL NUMBER flushmx, -- FLUSH_MAX_TIME NUMBER light, -- LIGHT_WORKS NUMBER signal -- ERRORS NUMBER from x$kclcrst
GV$BUFFERED_PUBLISHERS
select inst_id, -- INST_ID NUMBER queue_id, -- QUEUE_ID NUMBER queue_schema, -- QUEUE_SCHEMA VARCHAR2(30) queue_name, -- QUEUE_NAME VARCHAR2(30) sender_name, -- SENDER_NAME VARCHAR2(30) sender_address, -- SENDER_ADDRESS VARCHAR2(1024) sender_protocol, -- SENDER_PROTOCOL NUMBER num_msgs, -- NUM_MSGS NUMBER cnum_msgs, -- CNUM_MSGS NUMBER last_enqueued_msg -- LAST_ENQUEUED_MSG NUMBER from x$buffered_publishers where (bitand (publisher_flags, 2) != 2)
GV$BUFFERED_QUEUES
select inst_id, -- INST_ID NUMBER queue_id, -- QUEUE_ID NUMBER queue_schema, -- QUEUE_SCHEMA VARCHAR2(30) queue_name, -- QUEUE_NAME VARCHAR2(30) startup_time, -- STARTUP_TIME DATE num_msgs, -- NUM_MSGS NUMBER spill_msgs, -- SPILL_MSGS NUMBER cnum_msgs, -- CNUM_MSGS NUMBER cspill_msgs -- CSPILL_MSGS NUMBER from x$buffered_queues
GV$BUFFERED_SUBSCRIBERS
select s.inst_id, -- INST_ID NUMBER s.queue_id, -- QUEUE_ID NUMBER q.queue_schema, -- QUEUE_SCHEMA VARCHAR2(30) q.queue_name, -- QUEUE_NAME VARCHAR2(30) s.subscriber_id, -- SUBSCRIBER_ID NUMBER s.subscriber_name, -- SUBSCRIBER_NAME VARCHAR2(30) s.subscriber_address, -- SUBSCRIBER_ADDRESS VARCHAR2(1024) s.protocol, -- PROTOCOL NUMBER s.subscriber_type, -- SUBSCRIBER_TYPE VARCHAR2(30) q.startup_time, -- STARTUP_TIME DATE s.last_browsed_seq, -- LAST_BROWSED_SEQ NUMBER s.last_browsed_num, -- LAST_BROWSED_NUM NUMBER s.last_dequeued_seq, -- LAST_DEQUEUED_SEQ NUMBER s.last_dequeued_num, -- LAST_DEQUEUED_NUM NUMBER s.current_enq_seq, -- CURRENT_ENQ_SEQ NUMBER s.num_msgs, -- NUM_MSGS NUMBER s.cnum_msgs, -- CNUM_MSGS NUMBER s.total_dequeued_msg, -- TOTAL_DEQUEUED_MSG NUMBER s.total_spilled_msg -- TOTAL_SPILLED_MSG NUMBER from x$buffered_subscribers s, x$buffered_queues q where s.inst_id = q.inst_id and s.queue_id = q.queue_id
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 kmcvcadr, -- 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(257) kmcvcpvc -- PCIRCUIT RAW(4) from x$kmcvc where bitand (ksspaflg, 1) != 0
GV$CLASS_CACHE_TRANSFER
select 0, -- INST_ID NUMBER 'data block', -- CLASS CHAR(10) 0, -- X_2_NULL NUMBER 0, -- X_2_NULL_FORCED_WRITE NUMBER 0, -- X_2_NULL_FORCED_STALE NUMBER 0, -- X_2_S NUMBER 0, -- X_2_S_FORCED_WRITE NUMBER 0, -- S_2_NULL NUMBER 0, -- S_2_NULL_FORCED_STALE NUMBER 0, -- NULL_2_X NUMBER 0, -- S_2_X NUMBER 0, -- NULL_2_S NUMBER 0, -- CR_TRANSFER NUMBER 0 -- CURRENT_TRANSFER NUMBER from dual
GV$CLASS_PING
select 0, -- INST_ID NUMBER 'data block', -- CLASS CHAR(10) 0, -- X_2_NULL NUMBER 0, -- X_2_NULL_FORCED_WRITE NUMBER 0, -- X_2_NULL_FORCED_STALE NUMBER 0, -- X_2_S NUMBER 0, -- X_2_S_FORCED_WRITE NUMBER 0, -- X_2_SSX NUMBER 0, -- X_2_SSX_FORCED_WRITE NUMBER 0, -- S_2_NULL NUMBER 0, -- S_2_NULL_FORCED_STALE NUMBER 0, -- SS_2_NULL NUMBER 0, -- SS_2_RLS NUMBER 0, -- OP_2_SS NUMBER 0, -- NULL_2_X NUMBER 0, -- S_2_X NUMBER 0, -- SSX_2_X NUMBER 0, -- NULL_2_S NUMBER 0 -- NULL_2_SS NUMBER from dual
GV$CLIENT_STATS
select c.inst_id, -- INST_ID NUMBER c.clsnam, -- CLIENT_IDENTIFIER VARCHAR2(64) m.extid, -- STAT_ID NUMBER m.sname, -- STAT_NAME VARCHAR2(64) c.statval -- VALUE NUMBER from x$kewecls c, x$kewssmap m where c.clspos = m.offst and m.aggid = 4
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) decode -- IS_RECOVERY_DEST_FILE VARCHAR2(3) (bitand (cffl2, 1), 0, 'NO', 'YES') from x$kcccf
GV$CONTROLFILE_RECORD_SECTION
select inst_id, -- INST_ID NUMBER decode -- TYPE VARCHAR2(28) (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', 24, 'FLASHBACK LOG', 25, 'RECOVERY DESTINATION', 26, 'INSTANCE SPACE RESERVATION', 27, 'REMOVABLE RECOVERY FILES', 28, 'RMAN STATUS', 29, 'THREAD INSTANCE NAME MAPPING', 30, 'MTTR', 31, 'DATAFILE HISTORY', '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', decode (to_number (ccscn), 0, 'UNKNOWN', '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 0, -- FREE_GC_ELEMENTS NUMBER flush, -- FLUSHES NUMBER 0, -- FLUSHES_QUEUED NUMBER flushf, -- FLUSH_QUEUE_FULL NUMBER flushmx, -- FLUSH_MAX_TIME NUMBER light, -- LIGHT_WORKS NUMBER signal -- ERRORS NUMBER from x$kclcrst
GV$CURRENT_BLOCK_SERVER
select inst_id, -- INST_ID NUMBER pin1, -- PIN1 NUMBER pin10, -- PIN10 NUMBER pin100, -- PIN100 NUMBER pin1000, -- PIN1000 NUMBER pin10000, -- PIN10000 NUMBER flush1, -- FLUSH1 NUMBER flush10, -- FLUSH10 NUMBER flush100, -- FLUSH100 NUMBER flush1000, -- FLUSH1000 NUMBER flush10000, -- FLUSH10000 NUMBER write1, -- WRITE1 NUMBER write10, -- WRITE10 NUMBER write100, -- WRITE100 NUMBER write1000, -- WRITE1000 NUMBER write10000 -- WRITE10000 NUMBER from x$kclcurst
GV$DATABASE
select di.inst_id, -- INST_ID NUMBER di.didbi, -- DBID NUMBER di.didbn, -- NAME VARCHAR2(9) to_date -- CREATED DATE (di.dicts, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), to_number (di.dirls), -- RESETLOGS_CHANGE# NUMBER to_date -- RESETLOGS_TIME DATE (di.dirlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), to_number (di.diprs), -- PRIOR_RESETLOGS_CHANGE# NUMBER to_date -- PRIOR_RESETLOGS_TIME DATE (di.diprc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), decode -- LOG_MODE VARCHAR2(12) (di.dimla, 0, 'NOARCHIVELOG', 1, 'ARCHIVELOG', 'MANUAL'), to_number (di.discn), -- CHECKPOINT_CHANGE# NUMBER to_number (di.difas), -- ARCHIVE_CHANGE# NUMBER decode -- CONTROLFILE_TYPE VARCHAR2(7) (bitand (di.diflg, 256), 256, 'CREATED', decode (bitand (di.diflg, 1024), 1024, 'STANDBY', decode (bitand (di.diflg, 32768), 32768, 'CLONE', decode (bitand (di.diflg, 4096), 4096, 'BACKUP', 'CURRENT')))), to_date -- CONTROLFILE_CREATED DATE (di.dicct, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), di.dicsq, -- CONTROLFILE_SEQUENCE# NUMBER to_number -- CONTROLFILE_CHANGE# NUMBER (di.dickp_scn), to_date -- CONTROLFILE_TIME DATE (di.dickp_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), decode -- OPEN_RESETLOGS VARCHAR2(11) (bitand (di.diflg, 4), 4, 'REQUIRED', decode (di.diirs, 0, 'NOT ALLOWED', 'ALLOWED')), to_date -- VERSION_TIME DATE (di.divts, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), decode -- OPEN_MODE VARCHAR2(10) (di.didor, 0, 'MOUNTED', decode (di.didor, 1, 'READ WRITE', 'READ ONLY')), decode -- PROTECTION_MODE VARCHAR2(20) (bitand (di.diflg, 65536), 65536, 'MAXIMUM PROTECTION', decode (bitand (di.diflg, 128), 128, 'MAXIMUM AVAILABILITY', decode (bitand (di.diflg, 134217728), 134217728, 'RESYNCHRONIZATION', decode (bitand (di.diflg, 8), 8, 'UNPROTECTED', 'MAXIMUM PERFORMANCE')))), decode -- PROTECTION_LEVEL VARCHAR2(20) (di.diprt, 1, 'MAXIMUM PROTECTION', 2, 'MAXIMUM AVAILABILITY', 3, 'RESYNCHRONIZATION', 4, 'MAXIMUM PERFORMANCE', 5, 'UNPROTECTED', 'UNKNOWN'), decode -- REMOTE_ARCHIVE VARCHAR2(8) (di.dirae, 0, 'DISABLED', 1, 'SEND', 2, 'RECEIVE', 3, 'ENABLED', 'UNKNOWN'), to_number (di.diacid), -- ACTIVATION# NUMBER to_number (di.diacid), -- SWITCHOVER# NUMBER decode -- DATABASE_ROLE VARCHAR2(16) (bitand (di.diflg, 33554432), 33554432, 'LOGICAL STANDBY', decode (bitand (di.diflg, 1024), 1024, 'PHYSICAL STANDBY', 'PRIMARY')), to_number (di.diars), -- ARCHIVELOG_CHANGE# NUMBER decode -- ARCHIVELOG_COMPRESSION VARCHAR2(8) (bitand (difl2, 1), 1, 'ENABLED', 'DISABLED'), decode -- SWITCHOVER_STATUS VARCHAR2(20) (di.disos, 0, 'IMPOSSIBLE', 1, 'NOT ALLOWED', 2, 'SWITCHOVER LATENT', 3, 'SWITCHOVER PENDING', 4, 'TO PRIMARY', 5, 'TO STANDBY', 6, 'RECOVERY NEEDED', 7, 'SESSIONS ACTIVE', 8, 'PREPARING SWITCHOVER', 9, 'PREPARING DICTIONARY', 10, 'TO LOGICAL STANDBY', 'UNKNOWN'), decode -- DATAGUARD_BROKER VARCHAR2(8) (di.didgd, 0, 'DISABLED', 'ENABLED'), decode -- GUARD_STATUS VARCHAR2(7) (bitand (di.diflg, 1048576), 1048576, 'ALL', decode (bitand (di.diflg, 2097152), 2097152, 'STANDBY', 'NONE')), decode -- SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8) (bitand (diflg, 1073741824), 1073741824, 'YES', decode (bitand (diflg, 131072 + 262144 + 524288), 0, decode (bitand (difl2, 2), 0, 'NO', 'IMPLICIT'), 'IMPLICIT')), decode -- SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3) (bitand (di.diflg, 131072), 131072, 'YES', 'NO'), decode -- SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3) (bitand (di.diflg, 262144), 262144, 'YES', 'NO'), decode -- FORCE_LOGGING VARCHAR2(3) (bitand (di.diflg, 268435456), 268435456, 'YES', 'NO'), di.diplid, -- PLATFORM_ID NUMBER di.dipln, -- PLATFORM_NAME VARCHAR2(101) di2.di2rdi, -- RECOVERY_TARGET_INCARNATION# NUMBER di2.di2inc, -- LAST_OPEN_INCARNATION# NUMBER to_number -- CURRENT_SCN NUMBER (di.dicur_scn), decode -- FLASHBACK_ON VARCHAR2(3) (bitand (di2.di2flg, 1), 1, 'YES', 'NO'), decode -- SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3) (bitand (diflg, 524288), 524288, 'YES', 'NO'), decode -- SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3) (bitand (difl2, 2), 2, 'YES', 'NO'), di.didbun, -- DB_UNIQUE_NAME VARCHAR2(30) to_number -- STANDBY_BECAME_PRIMARY_SCN NUMBER (di2.di2actiscn) from x$kccdi di, x$kccdi2 di2
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 and datafile_blocks = blocks_read)
GV$DATABASE_INCARNATION
select userenv ('Instance'), -- INST_ID NUMBER icrid, -- INCARNATION# 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'), decode -- STATUS VARCHAR2(7) (icflg, 1, 'ORPHAN', 2, 'CURRENT', 0, 'PARENT', null), icrlc_i, -- RESETLOGS_ID NUMBER icpinc -- PRIOR_INCARNATION# NUMBER from x$kccic
GV$DATAFILE
select /*+ rule */ 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 bitand (fnaux.fnflg, 4) != 4 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 and bitand (fn.fnflg, 4) != 4
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'), decode -- IS_RECOVERY_DEST_FILE VARCHAR2(3) (bitand (dcflg, 2048), 0, 'NO', 'YES'), dcrsi, -- RMAN_STATUS_RECID NUMBER dcrst -- RMAN_STATUS_STAMP NUMBER 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_CONFIG
select DGCDBUN -- DB_UNIQUE_NAME VARCHAR2(30) from x$kcrrdgc
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$DATAPUMP_JOB
SELECT inst_id, -- INST_ID NUMBER kupvjid, -- JOB_ID RAW(16) kupvjjob, -- JOB_NAME VARCHAR2(30) kupvjowner, -- OWNER_NAME VARCHAR2(30) kupvjctrlque, -- MSG_CTRL_QUEUE VARCHAR2(30) kupvjstatque, -- STATUS_QUEUE VARCHAR2(30) kupvjoperation, -- OPERATION VARCHAR2(30) kupvjmode, -- JOB_MODE VARCHAR2(30) kupvjmasterid, -- MASTER_ID NUMBER kupvjstate, -- STATE VARCHAR2(30) kupvjworkers, -- WORKERS NUMBER kupvjflags -- FLAGS NUMBER FROM x$kupvj
GV$DATAPUMP_SESSION
SELECT inst_id, -- INST_ID NUMBER kupvaid, -- ATTACH_ID NUMBER kupvajobid, -- JOB_ID RAW(16) kupvasesaddr -- SADDR RAW(4) FROM x$kupva
GV$DBFILE
select inst_id, -- INST_ID NUMBER fnfno, -- FILE# NUMBER fnnam -- NAME VARCHAR2(513) from x$kccfn where fnnam is not null and bitand (fnflg, 4) != 4 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)), A.estd_time_for_disk_reads, -- ESTD_PHYSICAL_READ_TIME NUMBER A.estd_disk_as_pct_of_total -- ESTD_PCT_OF_DB_TIME_FOR_READS NUMBER 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', 237, 'DATAFILE RENAME ON RESTORE', '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_CONFIG
select inst_id, -- INST_ID NUMBER indx, -- CONF_INDX NUMBER kmmdpnet, -- NETWORK VARCHAR2(128) kmmdpopt, -- DISPATCHERS NUMBER kmmdpcon, -- CONNECTIONS NUMBER kmmdpses, -- SESSIONS NUMBER decode -- POOL VARCHAR2(4) (bitand (kmmdpflg, 3), 0, 'OFF', 1, 'IN', 2, 'OUT', 'BOTH'), kmmdptck, -- TICKS NUMBER kmmdptin, -- INBD_TIMOUT NUMBER kmmdptou, -- OUTBD_TIMOUT NUMBER decode -- MULTIPLEX VARCHAR2(4) (bitand (kmmdpflg, 12), 0, 'OFF', 4, 'IN', 8, 'OUT', 'BOTH'), kmmdplsn, -- LISTENER VARCHAR2(1200) kmmdpsnm -- SERVICE VARCHAR2(128) from x$kmmdp
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(50) 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 -kzsprprv -- PRIV_NUMBER NUMBER from x$kzspr
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) sum (ksqstreq), -- TOTAL_REQ# NUMBER sum (ksqstwat), -- TOTAL_WAIT# NUMBER sum (ksqstsgt), -- SUCC_REQ# NUMBER sum (ksqstfgt), -- FAILED_REQ# NUMBER sum (ksqstwtm) -- CUM_WAIT_TIME NUMBER from X$KSQST group by inst_id, ksqsttyp having sum (ksqstreq) > 0
GV$ENQUEUE_STATISTICS
select st.inst_id, -- INST_ID NUMBER eqt.name, -- EQ_NAME VARCHAR2(64) st.ksqsttyp, -- EQ_TYPE VARCHAR2(2) st.ksqstrsn, -- REQ_REASON VARCHAR2(64) st.ksqstreq, -- TOTAL_REQ# NUMBER st.ksqstwat, -- TOTAL_WAIT# NUMBER st.ksqstsgt, -- SUCC_REQ# NUMBER st.ksqstfgt, -- FAILED_REQ# NUMBER st.ksqstwtm, -- CUM_WAIT_TIME NUMBER st.ksqstexpl, -- REQ_DESCRIPTION VARCHAR2(4000) st.ksqstevidx -- EVENT# NUMBER from X$KSQST st, X$KSQEQTYP eqt where (st.inst_id = eqt.inst_id) and (st.ksqsttyp = eqt.resname) and (st.indx > 0)
GV$EVENTMETRIC
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER wait#, -- EVENT# NUMBER wait_id, -- EVENT_ID NUMBER nsess_wait, -- NUM_SESS_WAITING NUMBER time_waited, -- TIME_WAITED NUMBER wait_count -- WAIT_COUNT NUMBER FROM x$kewmevmv WHERE flag1 = 1 AND GROUPID = 0
GV$EVENT_HISTOGRAM
select d.inst_id, -- INST_ID NUMBER d.indx, -- EVENT# NUMBER d.kslednam, -- EVENT VARCHAR2(64) s.kslsesmaxdur, -- WAIT_TIME_MILLI NUMBER s.kslsesval -- WAIT_COUNT NUMBER from x$kslseshist s, x$ksled d where s.kslsesenum = d.indx
GV$EVENT_NAME
select inst_id, -- INST_ID NUMBER indx, -- EVENT# NUMBER ksledhash, -- EVENT_ID NUMBER kslednam, -- NAME VARCHAR2(64) ksledp1, -- PARAMETER1 VARCHAR2(64) ksledp2, -- PARAMETER2 VARCHAR2(64) ksledp3, -- PARAMETER3 VARCHAR2(64) ksledclassid, -- WAIT_CLASS_ID NUMBER ksledclass#, -- WAIT_CLASS# NUMBER ksledclass -- WAIT_CLASS 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 xid -- XID RAW(8) 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 xid, -- XID RAW(8) pxid, -- PXID RAW(8) svrs -- RCVSERVERS NUMBER from x$ktprxrt UNION ALL SELECT inst_id, -- INST_ID NUMBER usn, -- USN NUMBER slt, -- SLT NUMBER seq, -- SEQ NUMBER state, -- STATE VARCHAR2(16) twk-wkl, -- UNDOBLOCKSDONE NUMBER twk, -- UNDOBLOCKSTOTAL NUMBER NULL, -- PID NUMBER etime, -- CPUTIME NUMBER NULL, -- PARENTUSN NUMBER NULL, -- PARENTSLT NUMBER NULL, -- PARENTSEQ NUMBER xid, -- XID RAW(8) NULL, -- PXID RAW(8) svrs -- RCVSERVERS NUMBER from x$kturhist
GV$FILEMETRIC
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER fileid, -- FILE_ID NUMBER creationtime, -- CREATION_TIME NUMBER avrdtime, -- AVERAGE_READ_TIME NUMBER avwrtime, -- AVERAGE_WRITE_TIME NUMBER phyread, -- PHYSICAL_READS NUMBER phywrite, -- PHYSICAL_WRITES NUMBER phybkrd, -- PHYSICAL_BLOCK_READS NUMBER phybkwr -- PHYSICAL_BLOCK_WRITES NUMBER FROM x$kewmflmv WHERE flag1 = 1
GV$FILEMETRIC_HISTORY
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER fileid, -- FILE_ID NUMBER creationtime, -- CREATION_TIME NUMBER avrdtime, -- AVERAGE_READ_TIME NUMBER avwrtime, -- AVERAGE_WRITE_TIME NUMBER phyread, -- PHYSICAL_READS NUMBER phywrite, -- PHYSICAL_WRITES NUMBER phybkrd, -- PHYSICAL_BLOCK_READS NUMBER phybkwr -- PHYSICAL_BLOCK_WRITES NUMBER FROM x$kewmflmv
GV$FILESPACE_USAGE
SELECT inst_id, -- INST_ID NUMBER KTTEFINFOTSN, -- TABLESPACE_ID NUMBER KTTEFINFOFNO, -- RFNO NUMBER KTTEFINFOUSP, -- ALLOCATED_SPACE NUMBER KTTEFINFOSIZE, -- FILE_SIZE NUMBER KTTEFINFOMSIZE, -- FILE_MAXSIZE NUMBER KTTEFINFOSCNB, -- CHANGESCN_BASE NUMBER KTTEFINFOSCNW, -- CHANGESCN_WRAP NUMBER KTTEFINFOFLAG -- FLAG NUMBER FROM X$KTTEFINFO
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 0, -- X_2_NULL NUMBER 0, -- X_2_NULL_FORCED_WRITE NUMBER 0, -- X_2_NULL_FORCED_STALE NUMBER 0, -- X_2_S NUMBER 0, -- X_2_S_FORCED_WRITE NUMBER 0, -- S_2_NULL NUMBER 0, -- S_2_NULL_FORCED_STALE NUMBER 0, -- RBR NUMBER 0, -- RBR_FORCED_WRITE NUMBER 0, -- RBR_FORCED_STALE NUMBER 0, -- NULL_2_X NUMBER 0, -- S_2_X NUMBER 0, -- NULL_2_S NUMBER 0, -- CR_TRANSFERS NUMBER 0 -- CUR_TRANSFERS NUMBER from x$kcfio x, x$kccfe fe where x.kcfiofno = fe.fenum
GV$FILE_HISTOGRAM
select k.inst_id, -- INST_ID NUMBER k.kcfiofno, -- FILE# NUMBER k.kcfiomaxdur, -- SINGLEBLKRDTIM_MILLI NUMBER k.kcfioval -- SINGLEBLKRDS NUMBER from x$kcfiohist k, x$kccfe f where f.fedup <> 0 and f.fenum=k.kcfiofno
GV$FILE_PING
select x.inst_id, -- INST_ID NUMBER kcfiofno, -- FILE_NUMBER NUMBER 0, -- FREQUENCY NUMBER 0, -- X_2_NULL NUMBER 0, -- X_2_NULL_FORCED_WRITE NUMBER 0, -- X_2_NULL_FORCED_STALE NUMBER 0, -- X_2_S NUMBER 0, -- X_2_S_FORCED_WRITE NUMBER 0, -- X_2_SSX NUMBER 0, -- X_2_SSX_FORCED_WRITE NUMBER 0, -- S_2_NULL NUMBER 0, -- S_2_NULL_FORCED_STALE NUMBER 0, -- SS_2_NULL NUMBER 0, -- SS_2_RLS NUMBER 0, -- WRB NUMBER 0, -- WRB_FORCED_WRITE NUMBER 0, -- RBR NUMBER 0, -- RBR_FORCED_WRITE NUMBER 0, -- RBR_FORCED_STALE NUMBER 0, -- CBR NUMBER 0, -- CBR_FORCED_WRITE NUMBER 0, -- NULL_2_X NUMBER 0, -- S_2_X NUMBER 0, -- SSX_2_X NUMBER 0, -- 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$FLASHBACK_DATABASE_LOG
select inst_id, -- INST_ID NUMBER to_number (fblogscn), -- OLDEST_FLASHBACK_SCN NUMBER to_date -- OLDEST_FLASHBACK_TIME DATE (fblogtim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), fblogretn, -- RETENTION_TARGET NUMBER totsize, -- FLASHBACK_SIZE NUMBER to_number (fblogesiz) -- ESTIMATED_FLASHBACK_SIZE NUMBER from x$krfblog, ( select sum (flebsz * flenblks) totsize from x$kccfle where fledup != 0)
GV$FLASHBACK_DATABASE_LOGFILE
select fn.inst_id, -- INST_ID NUMBER fn.fnnam, -- NAME VARCHAR2(513) fle.flelno, -- LOG# NUMBER fle.flethr, -- THREAD# NUMBER fle.fleseq, -- SEQUENCE# NUMBER fle.flenblks -- BYTES NUMBER * fle.flebsz, to_number (fle.flelscn), -- FIRST_CHANGE# NUMBER to_date -- FIRST_TIME DATE (fle.fleltim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') from x$kccfn fn, x$kccfle fle where (fn.fntyp = 24) and (fn.fnnum = fle.flefnh) and (fle.fledup != 0)
GV$FLASHBACK_DATABASE_STAT
select inst_id, -- INST_ID NUMBER to_date -- BEGIN_TIME DATE (btime, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), to_date -- END_TIME DATE (etime, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), fbw*512, -- FLASHBACK_DATA NUMBER dbw*512, -- DB_DATA NUMBER redow*512, -- REDO_DATA NUMBER to_number (fbsz) -- ESTIMATED_FLASHBACK_SIZE NUMBER from x$krfgstat
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_CACHE_TRANSFER
select inst_id, -- INST_ID NUMBER instance, -- INSTANCE NUMBER decode -- CLASS VARCHAR2(18) (class, 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, 'undo header', 16, 'undo block'), cr_block, -- CR_BLOCK NUMBER cr_busy, -- CR_BUSY NUMBER cr_congested, -- CR_CONGESTED NUMBER current_block, -- CURRENT_BLOCK NUMBER current_busy, -- CURRENT_BUSY NUMBER current_congested -- CURRENT_CONGESTED NUMBER from x$instance_cache_transfer
GV$INSTANCE_LOG_GROUP
select USERENV ('Instance'), -- INST_ID NUMBER THREAD#, -- THREAD# NUMBER STATUS, -- STATUS VARCHAR2(6) ENABLED, -- ENABLED VARCHAR2(8) GROUPS, -- GROUPS NUMBER INSTANCE, -- INSTANCE VARCHAR2(80) OPEN_TIME, -- OPEN_TIME DATE CURRENT_GROUP#, -- CURRENT_GROUP# NUMBER SEQUENCE#, -- SEQUENCE# NUMBER CHECKPOINT_CHANGE#, -- CHECKPOINT_CHANGE# NUMBER CHECKPOINT_TIME, -- CHECKPOINT_TIME DATE ENABLE_CHANGE#, -- ENABLE_CHANGE# NUMBER ENABLE_TIME, -- ENABLE_TIME DATE DISABLE_CHANGE#, -- DISABLE_CHANGE# NUMBER DISABLE_TIME -- DISABLE_TIME DATE from V$THREAD
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)), to_number -- ACTUAL_REDO_BLKS NUMBER (decode (ACTUAL_REDO_BLKS, -1, NULL, ACTUAL_REDO_BLKS)), 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), ( select logfile_size from x$kctlax where indx=0 and inst_id=t.inst_id), to_number (decode (CUR_EST_MTTCA_SEC, -1, NULL, CUR_EST_MTTCA_SEC)), MTTR_WRITES, LOGFILE_SIZE_WRITES, CKPT_SETTING_WRITES, OTHER_WRITES, AUTO_WRITES, FULL_WRITES from X$TARGETRBA T, X$ESTIMATED_MTTR E, X$KCTICW W where T.INST_ID=E.INST_ID AND T.INST_ID=W.INST_ID
GV$JAVAPOOL
select inst_id, -- INST_ID NUMBER ksmchcom, -- CATEGORY VARCHAR2(16) sum (ksmchsiz) -- MEMUSED NUMBER from x$ksmjch group by inst_id, ksmchcom order by ksmchcom
GV$JAVA_LIBRARY_CACHE_MEMORY
select inst_id, -- INST_ID NUMBER decode -- LC_NAMESPACE VARCHAR2(15) (kgljsim_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', '?'), kgljsim_pincnt, -- LC_INUSE_MEMORY_OBJECTS NUMBER kgljsim_pinmem, -- LC_INUSE_MEMORY_SIZE NUMBER kgljsim_unpincnt, -- LC_FREEABLE_MEMORY_OBJECTS NUMBER kgljsim_unpinmem -- LC_FREEABLE_MEMORY_SIZE NUMBER from x$kgljmem where kgljsim_namespace<8 or kgljsim_namespace=13 or kgljsim_namespace=14 or kgljsim_namespace=32 union select inst_id, -- INST_ID NUMBER 'OTHER/SYSTEM', -- LC_NAMESPACE VARCHAR2(15) sum -- LC_INUSE_MEMORY_OBJECTS NUMBER (kgljsim_pincnt) sum_pincnt, sum -- LC_INUSE_MEMORY_SIZE NUMBER (kgljsim_pinmem) sum_pinmem, sum -- LC_FREEABLE_MEMORY_OBJECTS NUMBER (kgljsim_unpincnt) sum_unpincnt, sum -- LC_FREEABLE_MEMORY_SIZE NUMBER (kgljsim_unpinmem) sum_unpinmem from x$kgljmem where not (kgljsim_namespace<8 or kgljsim_namespace=13 or kgljsim_namespace=14 or kgljsim_namespace=32) group by inst_id
GV$JAVA_POOL_ADVICE
select inst_id, -- INST_ID NUMBER java_size, -- JAVA_POOL_SIZE_FOR_ESTIMATE NUMBER round -- JAVA_POOL_SIZE_FACTOR NUMBER (java_size / basejava_size, 4), kgljsim_size, -- ESTD_LC_SIZE NUMBER kgljsim_objs, -- ESTD_LC_MEMORY_OBJECTS NUMBER kgljsim_timesave, -- ESTD_LC_TIME_SAVED NUMBER decode -- ESTD_LC_TIME_SAVED_FACTOR NUMBER (kgljsim_basetimesave, 0, to_number (null), round (kgljsim_timesave / kgljsim_basetimesave, 4)), kgljsim_parsetime, -- ESTD_LC_LOAD_TIME NUMBER decode -- ESTD_LC_LOAD_TIME_FACTOR NUMBER (kgljsim_baseparsetime, 0, to_number (null), round (kgljsim_parsetime / kgljsim_baseparsetime, 4)), kgljsim_hits -- ESTD_LC_MEMORY_OBJECT_HITS NUMBER from x$kgljsim
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(50) d.kslldhsh, -- HASH NUMBER 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(50) kslldhsh -- HASH NUMBER 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(50) n.kslldhsh, -- HASH NUMBER 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(50) n.kslldhsh, -- HASH NUMBER 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 cpu_count, -- CPU_COUNT_CURRENT NUMBER cpu_count_hwm -- CPU_COUNT_HIGHWATER 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) klciemno, -- MESSAGE_NUM NUMBER 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 0, -- INST_ID NUMBER 'NULL', -- FROM_VAL CHAR(4) 'S', -- TO_VAL CHAR(1) 'Lock buffers for read', -- ACTION_VAL CHAR(21) 0 -- COUNTER NUMBER from dual
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$LOCK_TYPE
select rest.inst_id, -- INST_ID NUMBER rest.resname, -- TYPE VARCHAR2(64) rest.name, -- NAME VARCHAR2(64) rest.id1, -- ID1_TAG VARCHAR2(64) rest.id2, -- ID2_TAG VARCHAR2(64) decode -- IS_USER VARCHAR2(3) (bitand (eqt.flags, 1), 1, 'YES', 'NO'), rest.expl -- DESCRIPTION VARCHAR2(4000) from X$KSIRESTYP rest, X$KSQEQTYP eqt where (rest.inst_id = eqt.inst_id) and (rest.indx = eqt.indx) and (rest.indx > 0)
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+32), 8, '', 32, '', 40, '', 'UNKNOWN'))))), decode -- TYPE VARCHAR2(7) (bitand (fnflg, 8), 0, 'ONLINE', 'STANDBY'), fnnam, -- MEMBER VARCHAR2(513) decode -- IS_RECOVERY_DEST_FILE VARCHAR2(3) (bitand (fnflg, 32), 0, 'NO', 'YES') 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) TABLE_NAME, -- TABLE_NAME VARCHAR2(32) SEG_TYPE, -- SEG_TYPE NUMBER SEG_TYPE_NAME, -- SEG_TYPE_NAME VARCHAR2(32) TABLE_SPACE, -- TABLE_SPACE VARCHAR2(32) ROW_ID, -- ROW_ID VARCHAR2(18) 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 NUMBER UNDO_VALUE, -- UNDO_VALUE NUMBER SQL_COLUMN_TYPE, -- SQL_COLUMN_TYPE VARCHAR2(30) SQL_COLUMN_NAME, -- SQL_COLUMN_NAME VARCHAR2(30) REDO_LENGTH, -- REDO_LENGTH NUMBER REDO_OFFSET, -- REDO_OFFSET NUMBER UNDO_LENGTH, -- UNDO_LENGTH NUMBER UNDO_OFFSET, -- UNDO_OFFSET NUMBER DATA_OBJV#, -- DATA_OBJV# NUMBER SAFE_RESUME_SCN, -- SAFE_RESUME_SCN NUMBER XID, -- XID RAW(8) PXID, -- PXID RAW(8) AUDIT_SESSIONIDfrom -- AUDIT_SESSIONID NUMBER x$logmnr_contents where ROW_TYPE = 0
GV$LOGMNR_DICTIONARY
select INST_ID, -- INST_ID NUMBER DB_NAME, -- DB_NAME VARCHAR2(9) DB_ID, -- DB_ID NUMBER DB_CREATED, -- DB_CREATED DATE TIMESTAMP, -- TIMESTAMP DATE RESET_SCN, -- RESET_SCN NUMBER RESET_SCN_TIME, -- RESET_SCN_TIME DATE DB_VERSION_TIME, -- DB_VERSION_TIME DATE DB_CHARACTER_SET, -- DB_CHARACTER_SET VARCHAR2(30) DB_VERSION, -- DB_VERSION VARCHAR2(64) DB_STATUS, -- DB_STATUS VARCHAR2(64) DICTIONARY_SCN, -- DICTIONARY_SCN NUMBER ENABLED_THREAD_MAP, -- ENABLED_THREAD_MAP RAW(16) DB_TXN_SCN, -- DB_TXN_SCN NUMBER FILENAME, -- FILENAME VARCHAR2(512) INFO, -- INFO VARCHAR2(32) STATUS -- STATUS NUMBER from x$logmnr_dictionary
GV$LOGMNR_LATCH
select inst_id, -- INST_ID NUMBER session_id, -- SESSION_ID NUMBER name, -- NAME VARCHAR2(32) child_addr, -- CHILD_ADDR RAW(4) decode -- STATE VARCHAR2(6) (state, 0, 'UNINIT', 1, 'READY') state from x$logmnr_latch
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(512) 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) TYPE, -- TYPE VARCHAR2(7) BLOCKSIZE, -- BLOCKSIZE NUMBER FILESIZE, -- FILESIZE NUMBER INFO, -- INFO VARCHAR2(32) STATUS -- STATUS NUMBER from x$logmnr_logs
GV$LOGMNR_PARAMETERS
select INST_ID, -- INST_ID NUMBER START_DATE, -- START_DATE DATE REQUIRED_START_DATE, -- REQUIRED_START_DATE DATE END_DATE, -- END_DATE DATE START_SCN, -- START_SCN NUMBER REQUIRED_START_SCN, -- REQUIRED_START_SCN NUMBER END_SCN, -- END_SCN NUMBER OPTIONS, -- OPTIONS NUMBER INFO, -- INFO VARCHAR2(32) STATUS -- STATUS NUMBER from x$logmnr_parameters
GV$LOGMNR_PROCESS
select a.inst_id, -- INST_ID NUMBER a.session_id, -- SESSION_ID NUMBER a.pid, -- PID NUMBER a.role, -- ROLE VARCHAR2(32) a.work_microsec, -- WORK_MICROSEC VARCHAR2(22) a.overhead_microsec, -- OVERHEAD_MICROSEC VARCHAR2(22) b.spid, -- SPID VARCHAR2(12) b.username, -- USERNAME VARCHAR2(15) b.latchwait, -- LATCHWAIT VARCHAR2(8) b.latchspin, -- LATCHSPIN VARCHAR2(8) c.sid, -- SID NUMBER c.serial# -- SERIAL# NUMBER from x$logmnr_process a, v$process b, v$session c where a.pid = b.pid and b.spid = c.process
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'), db_name, -- DB_NAME VARCHAR2(128) db_id, -- DB_ID NUMBER reset_scn, -- RESET_SCN NUMBER reset_timestamp, -- RESET_TIMESTAMP NUMBER num_process, -- NUM_PROCESS NUMBER chunk_size, -- CHUNK_SIZE NUMBER start_scn, -- START_SCN NUMBER end_scn, -- END_SCN NUMBER spill_scn, -- SPILL_SCN NUMBER processed_scn, -- PROCESSED_SCN NUMBER prepared_scn, -- PREPARED_SCN NUMBER read_scn, -- READ_SCN NUMBER low_mark_scn, -- LOW_MARK_SCN NUMBER consumed_scn, -- CONSUMED_SCN NUMBER max_memory_size, -- MAX_MEMORY_SIZE NUMBER used_memory_size, -- USED_MEMORY_SIZE NUMBER prepared_work_size, -- PREPARED_WORK_SIZE NUMBER available_work_size, -- AVAILABLE_WORK_SIZE NUMBER available_txn, -- AVAILABLE_TXN NUMBER available_committed_txn, -- AVAILABLE_COMMITTED_TXN NUMBER delivered_txn, -- DELIVERED_TXN NUMBER delivered_committed_txn, -- DELIVERED_COMMITTED_TXN NUMBER pinned_txn, -- PINNED_TXN NUMBER pinned_committed_txn -- PINNED_COMMITTED_TXN 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 VARCHAR2(22) from x$krvxsv where flags = 0
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 where name != 'LMNR_SID' union all select inst_id, name, to_char (value) from ( select inst_id, session_id, name, value from x$krvxsv where flags = 0) where session_id = ( select value from x$krvslvs 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 x$kcclh.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 to_number (lhrls), -- RESETLOGS_CHANGE# NUMBER to_date -- RESETLOGS_TIME DATE (lhrlc, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') from x$kcclh
GV$MANAGED_STANDBY
select inst_id, -- INST_ID NUMBER decode -- PROCESS VARCHAR2(9) (MSTYP, 1, 'RFS', 2, 'MRP0', 3, 'MR (fg) ', 4, 'ARCH', 5, 'FGRD', 6, 'LGWR', 7, 'RFS (FAL) ', 8, 'RFS (NEXP) ', '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 (MSRLC), -- RESETLOG_ID NUMBER 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$METRIC
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER groupid, -- GROUP_ID NUMBER eid, -- ENTITY_ID NUMBER eidsq, -- ENTITY_SEQUENCE NUMBER metricid, -- METRIC_ID NUMBER name, -- METRIC_NAME VARCHAR2(64) value, -- VALUE NUMBER unit -- METRIC_UNIT VARCHAR2(64) FROM x$kewmdrmv WHERE flag1 = 1
GV$METRICGROUP
SELECT inst_id, -- INST_ID NUMBER groupid, -- GROUP_ID NUMBER name, -- NAME VARCHAR2(64) intsize, -- INTERVAL_SIZE NUMBER maxintv -- MAX_INTERVAL NUMBER FROM x$kewmgsm
GV$METRICNAME
SELECT d.inst_id, -- INST_ID NUMBER d.groupid, -- GROUP_ID NUMBER g.name, -- GROUP_NAME VARCHAR2(64) d.metricid, -- METRIC_ID NUMBER d.name, -- METRIC_NAME VARCHAR2(64) d.unit -- METRIC_UNIT VARCHAR2(64) FROM x$kewmdsm d, x$kewmgsm g where d.groupid = g.groupid
GV$METRIC_HISTORY
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER groupid, -- GROUP_ID NUMBER eid, -- ENTITY_ID NUMBER eidsq, -- ENTITY_SEQUENCE NUMBER metricid, -- METRIC_ID NUMBER name, -- METRIC_NAME VARCHAR2(64) value, -- VALUE NUMBER unit -- METRIC_UNIT VARCHAR2(64) FROM x$kewmdrmv
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 x$kccor.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'), to_number (orrls), -- RESETLOGS_CHANGE# NUMBER to_date -- RESETLOGS_TIME DATE (orrlc, '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 kgllksqlid, -- SQL_ID VARCHAR2(13) 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$OSSTAT
select INST_ID, -- INST_ID NUMBER KSUCPUSTATNAME, -- STAT_NAME VARCHAR2(64) KSUCPUSTATVALUE, -- VALUE NUMBER KSUCPUSTATID -- OSSTAT_ID NUMBER from X$KSUCPUSTAT union all select INST_ID, -- INST_ID NUMBER KSUVMSTATNAME, -- STAT_NAME VARCHAR2(64) KSUVMSTATVALUE, -- VALUE NUMBER KSUVMSTATID -- OSSTAT_ID NUMBER from X$KSUVMSTAT
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(80) ksppity, -- TYPE NUMBER ksppstvl, -- VALUE VARCHAR2(512) ksppstdvl, -- DISPLAY_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 -- ISINSTANCE_MODIFIABLE VARCHAR2(5) (bitand (ksppiflg, 4), 4, 'FALSE', decode (bitand (ksppiflg/65536, 3), 0, 'FALSE', 'TRUE')), decode -- ISMODIFIED VARCHAR2(10) (bitand (ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'), decode -- ISADJUSTED VARCHAR2(5) (bitand (ksppstvf, 2), 2, 'TRUE', 'FALSE'), decode -- ISDEPRECATED VARCHAR2(5) (bitand (ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc, -- DESCRIPTION VARCHAR2(255) ksppstcmnt, -- UPDATE_COMMENT VARCHAR2(255) ksppihash -- HASH NUMBER 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(80) ksppity, -- TYPE NUMBER kspftctxvl, -- VALUE VARCHAR2(512) kspftctxdvl, -- DISPLAY_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 -- ISINSTANCE_MODIFIABLE VARCHAR2(5) (bitand (ksppiflg, 4), 4, 'FALSE', decode (bitand (ksppiflg/65536, 3), 0, 'FALSE', 'TRUE')), decode -- ISMODIFIED VARCHAR2(10) (bitand (kspftctxvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'), decode -- ISADJUSTED VARCHAR2(5) (bitand (kspftctxvf, 2), 2, 'TRUE', 'FALSE'), decode -- ISDEPRECATED VARCHAR2(5) (bitand (ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc, -- DESCRIPTION VARCHAR2(255) 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$PROPAGATION_RECEIVER
select inst_id, -- INST_ID NUMBER kwqpdsqn, -- SRC_QUEUE_NAME VARCHAR2(66) kwqpddbn, -- SRC_DBNAME VARCHAR2(128) min (kwqpdtim), -- STARTUP_TIME DATE max (kwqpdhwm), -- HIGH_WATER_MARK NUMBER min (kwqpdack), -- ACKNOWLEDGEMENT NUMBER min (kwqpdupc), -- ELAPSED_UNPICKLE_TIME NUMBER min (kwqpdrul), -- ELAPSED_RULE_TIME NUMBER min -- ELAPSED_ENQUEUE_TIME NUMBER (kwqpdenq) - min (kwqpdupc) - min (kwqpdrul) from x$kwqpd group by inst_id, kwqpdsqn, kwqpddbn
GV$PROPAGATION_SENDER
select s.inst_id, -- INST_ID NUMBER s.kwqpsqid, -- QUEUE_ID NUMBER q.queue_schema, -- QUEUE_SCHEMA VARCHAR2(30) q.queue_name, -- QUEUE_NAME VARCHAR2(30) s.kwqpsdbn, -- DBLINK VARCHAR2(128) s.kwqpshwm, -- HIGH_WATER_MARK NUMBER s.kwqpsack, -- ACKNOWLEDGEMENT NUMBER s.kwqpsstt, -- SCHEDULE_STATUS VARCHAR2(30) s.kwqpsmsg, -- TOTAL_MSGS NUMBER s.kwqpsbyt, -- TOTAL_BYTES NUMBER s.kwqpsdeq, -- ELAPSED_DEQUEUE_TIME NUMBER s.kwqpspic, -- ELAPSED_PICKLE_TIME NUMBER s.kwqpsprp -- ELAPSED_PROPAGATION_TIME NUMBER - s.kwqpsdeq - s.kwqpspic from x$kwqps s, x$buffered_queues q where s.inst_id = q.inst_id and s.kwqpsqid = q.queue_id
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 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'), 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'), abs -- ELAPSED_SECONDS NUMBER ((to_date (pctim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') - to_date (pctsm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')) *86400), pcrsi, -- RMAN_STATUS_RECID NUMBER pcrst -- RMAN_STATUS_STAMP NUMBER 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'), abs -- ELAPSED_SECONDS NUMBER ((to_date (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), pcrsi, -- RMAN_STATUS_RECID NUMBER pcrst -- RMAN_STATUS_STAMP NUMBER 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_BUFFER_ADVICE
select inst_id, -- INST_ID NUMBER rpad (kxfpnsnam, 30), -- STATISTIC VARCHAR2(30) kxfpnsval -- VALUE NUMBER from x$kxfpns where indx = 4 or indx >= 14
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 where indx < 15
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.ksusenum 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(30) 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$RMAN_OUTPUT
select userenv ('Instance'), -- INST_ID NUMBER SID_KRBMROT, -- SID NUMBER ID_KRBMROT, -- RECID NUMBER STAMP_KRBMROT, -- STAMP NUMBER L0ID_KRBMROT, -- SESSION_RECID NUMBER L0TS_KRBMROT, -- SESSION_STAMP NUMBER TXT_KRBMROT -- OUTPUT VARCHAR2(129) from x$krbmrot order by MTS_KRBMROT, ROWNO_KRBMROT
GV$RMAN_STATUS_CURRENT
select userenv ('Instance'), -- INST_ID NUMBER SID_KRBMRST, -- SID NUMBER ID_KRBMRST, -- RECID NUMBER STAMP_KRBMRST, -- STAMP NUMBER decode -- PARENT_RECID VARCHAR2(40) (LEVEL_KRBMRST, 0, NULL, PID_KRBMRST), decode -- PARENT_STAMP VARCHAR2(40) (LEVEL_KRBMRST, 0, NULL, PTS_KRBMRST), L0ID_KRBMRST, -- SESSION_RECID NUMBER L0TS_KRBMRST, -- SESSION_STAMP NUMBER LEVEL_KRBMRST, -- ROW_LEVEL NUMBER decode -- ROW_TYPE VARCHAR2(19) (LEVEL_KRBMRST, 0, 'SESSION', 1, 'COMMAND', 'RECURSIVE OPERATION'), CMDID_KRBMRST, -- COMMAND_ID VARCHAR2(33) UPPER (OPER_KRBMRST), -- OPERATION VARCHAR2(33) decode -- STATUS VARCHAR2(23) (STATUS_KRBMRST, 1, 'RUNNING', 1+8, 'RUNNING WITH WARNINGS', 1+16, 'RUNNING WITH ERRORS', 1+8+16, 'RUNNING WITH ERRORS', 2, 'COMPLETED', 2+8, 'COMPLETED WITH WARNINGS', 2+16, 'COMPLETED WITH ERRORS', 2+8+16, 'COMPLETED WITH ERRORS', 'FAILED'), 0, -- MBYTES_PROCESSED NUMBER to_date -- START_TIME DATE (START_KRBMRST, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), to_date -- END_TIME DATE (END_KRBMRST, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian') from x$krbmrst
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) is_top_plan_kgskpft -- IS_TOP_PLAN VARCHAR2(5) from x$kgskpft
GV$RSRC_PLAN_CPU_MTH
select inst_id, -- INST_ID NUMBER policy_name_kgskpp -- NAME VARCHAR2(40) from x$kgskpp
GV$RULE
select inst_id, -- INST_ID NUMBER kqrfpt07, -- RULE_SET_OBJECT_ID NUMBER kqrfpt08, -- EVALUATION_CONTEXT_OBJECT_ID NUMBER kqrfpts0, -- RULE_OWNER VARCHAR2(64) kqrfpts1, -- RULE_NAME VARCHAR2(64) kqrfpts2, -- RULE_CONDITION VARCHAR2(64) kqrfpt09, -- TRUE_HITS NUMBER kqrfpt10, -- MAYBE_HITS NUMBER kqrfpt11 -- SQL_EVALUATIONS NUMBER from x$kqrfp where kqrfpcid=22
GV$RULE_SET
select inst_id, -- INST_ID NUMBER kglnaown, -- OWNER VARCHAR2(64) kglnaobj, -- NAME VARCHAR2(1000) kglobt08, -- CPU_TIME NUMBER kglobt09, -- ELAPSED_TIME NUMBER kglnatim, -- FIRST_LOAD_TIME DATE kglobtt0, -- LAST_LOAD_TIME DATE kglobt10, -- LAST_LOADING_TIME NUMBER kglobhs0+kglobhs6, -- SHARABLE_MEM NUMBER kglhdldc, -- RELOADS NUMBER kglhdivc, -- INVALIDATIONS NUMBER kglobt11, -- EVALUATIONS NUMBER kglobt12, -- FIRST_HIT_EVALUATIONS NUMBER kglobt13, -- SIMPLE_RULES_ONLY_EVALUATIONS NUMBER kglobt14, -- SQL_FREE_EVALUATIONS NUMBER kglobt15, -- SQL_EXECUTIONS NUMBER kglobt16, -- CONDITIONS_PROCESSED NUMBER kglobt17, -- TRUE_RULES NUMBER kglobt18, -- MAYBE_RULES NUMBER kglobt19, -- VARIABLE_VALUE_FUNCTION_CALLS NUMBER kglobt20, -- VARIABLE_METHOD_FUNCTION_CALLS NUMBER kglobt21 -- EVALUATION_FUNCTION_CALLS NUMBER from x$kglob where (kglhdnsp = 23) and (kglobtyp = 46)
GV$RULE_SET_AGGREGATE_STATS
select inst_id, -- INST_ID NUMBER name, -- NAME VARCHAR2(80) value -- VALUE NUMBER from x$kwrsnv
GV$SCHEDULER_RUNNING_JOBS
select inst_id, -- INST_ID NUMBER kglobt18, -- SESSION_ID NUMBER kglobt19, -- SESSION_SERIAL_NUM NUMBER kglobt17, -- JOB_ID NUMBER kglobt20, -- PROCESS_ID NUMBER kglobt21 -- SESSION_STAT_CPU NUMBER from x$jskslv where kglobt32 != 0
GV$SEGMENT_STATISTICS
select s.inst_id, -- INST_ID NUMBER u.name, -- OWNER NOT o.name, -- OBJECT_NAME NOT o.subname, -- SUBOBJECT_NAME VARCHAR2(30) ts.name, -- TABLESPACE_NAME NOT s.fts_tsn, -- TS# NUMBER o.obj#, -- OBJ# NOT 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_name, -- NAME VARCHAR2(64) decode -- SAMPLED VARCHAR2(3) (bitand (st_flag, 1), 0, 'NO', 1, 'YES') from x$ksolsstat where bitand (st_flag, 2) <> 2
GV$SERVICEMETRIC
SELECT sm.inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER groupid, -- GROUP_ID NUMBER sv.kswsastabnmh, -- SERVICE_NAME_HASH NUMBER sv.kswsastabnm, -- SERVICE_NAME VARCHAR2(64) ctmhash, -- CTMHASH NUMBER elapsedpercall, -- ELAPSEDPERCALL NUMBER cpupercall -- CPUPERCALL NUMBER FROM x$kewmsvcmv sm, x$kswsastab sv WHERE flag1 = 1 AND sm.svcid = sv.kswsastabsi
GV$SERVICEMETRIC_HISTORY
SELECT sm.inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER groupid, -- GROUP_ID NUMBER sv.kswsastabnmh, -- SERVICE_NAME_HASH NUMBER sv.kswsastabnm, -- SERVICE_NAME VARCHAR2(64) ctmhash, -- CTMHASH NUMBER elapsedpercall, -- ELAPSEDPERCALL NUMBER cpupercall -- CPUPERCALL NUMBER FROM x$kewmsvcmv sm, x$kswsastab sv WHERE sm.svcid = sv.kswsastabsi
GV$SERVICES
select inst_id, -- INST_ID NUMBER kswsastabsi, -- SERVICE_ID NUMBER kswsastabnm, -- NAME VARCHAR2(64) kswsastabnmh, -- NAME_HASH NUMBER kswsastabnn, -- NETWORK_NAME VARCHAR2(512) kswsastabcd, -- CREATION_DATE DATE kswsastabcdh -- CREATION_DATE_HASH NUMBER from x$kswsastab
GV$SERVICE_EVENT
select s.inst_id, -- INST_ID NUMBER s.kswsevtabnm, -- SERVICE_NAME VARCHAR2(64) s.kswsevtabnmh, -- SERVICE_NAME_HASH NUMBER d.kslednam, -- EVENT VARCHAR2(64) d.ksledhash, -- EVENT_ID NUMBER s.kswsevtabwts, -- TOTAL_WAITS NUMBER s.kswsevtabtmo, -- TOTAL_TIMEOUTS NUMBER round -- TIME_WAITED NUMBER (s.kswsevtabtim / 10000), round -- AVERAGE_WAIT NUMBER (s.kswsevtabtim / (10000 * s.kswsevtabwts)), round -- MAX_WAIT NUMBER (s.kswsevtabmxt / 10000), s.kswsevtabtim -- TIME_WAITED_MICRO NUMBER from x$kswsevtab s, x$ksled d where s.kswsevtabwts != 0 and s.kswsevtabnum = d.indx
GV$SERVICE_STATS
select s.inst_id, -- INST_ID NUMBER s.svchsh, -- SERVICE_NAME_HASH NUMBER s.svcnam, -- SERVICE_NAME VARCHAR2(64) m.extid, -- STAT_ID NUMBER m.sname, -- STAT_NAME VARCHAR2(64) s.kewsval -- VALUE NUMBER from x$kewssvcv s, x$kewssmap m where s.kewsoff = m.offst and m.aggid = 2
GV$SERVICE_WAIT_CLASS
select s.inst_id, -- INST_ID NUMBER s.kswsclstabnm, -- SERVICE_NAME VARCHAR2(64) s.kswsclstabnmh, -- SERVICE_NAME_HASH NUMBER s.kswsclsid, -- WAIT_CLASS_ID NUMBER s.kswsclsnum, -- WAIT_CLASS# NUMBER s.kswsclsname, -- WAIT_CLASS VARCHAR2(64) s.kswsclswts, -- TOTAL_WAITS NUMBER round -- TIME_WAITED NUMBER (s.kswsclstim / 10000) from x$kswsclstab s where s.kswsclswts != 0
GV$SERV_MOD_ACT_STATS
select sma.inst_id, -- INST_ID NUMBER 'SERVICE_MODULE_ACTION', -- AGGREGATION_TYPE VARCHAR2(21) sma.srvnam, -- SERVICE_NAME VARCHAR2(64) sma.modnam, -- MODULE VARCHAR2(49) sma.actnam, -- ACTION VARCHAR2(33) m.extid, -- STAT_ID NUMBER m.sname, -- STAT_NAME VARCHAR2(64) sma.statval -- VALUE NUMBER from x$kewesmas sma, x$kewssmap m where sma.statpos = m.offst and m.aggid = 3 union all select sm.inst_id, -- INST_ID NUMBER 'SERVICE_MODULE', -- AGGREGATION_TYPE VARCHAR2(21) sm.srvnam, -- SERVICE_NAME VARCHAR2(64) sm.modnam, -- MODULE VARCHAR2(49) NULL, -- ACTION VARCHAR2(33) m.extid, -- STAT_ID NUMBER m.sname, -- STAT_NAME VARCHAR2(64) sm.statval -- VALUE NUMBER from x$kewesms sm, x$kewssmap m where sm.statpos = m.offst and m.aggid = 5
GV$SESSION
select s.inst_id, -- INST_ID NUMBER s.addr, -- SADDR RAW(4) s.indx, -- SID NUMBER s.ksuseser, -- SERIAL# NUMBER s.ksuudses, -- AUDSID NUMBER s.ksusepro, -- PADDR RAW(4) s.ksuudlui, -- USER# NUMBER s.ksuudlna, -- USERNAME VARCHAR2(30) s.ksuudoct, -- COMMAND NUMBER s.ksusesow, -- OWNERID NUMBER decode -- TADDR VARCHAR2(8) (s.ksusetrn, hextoraw ('00'), null, s.ksusetrn), decode -- LOCKWAIT VARCHAR2(8) (s.ksqpswat, hextoraw ('00'), null, s.ksqpswat), decode -- STATUS VARCHAR2(8) (bitand (s.ksuseidl, 11), 1, 'ACTIVE', 0, decode (bitand (s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 2, 'SNIPED', 3, 'SNIPED', 'KILLED'), decode -- SERVER VARCHAR2(9) (s.ksspatyp, 1, 'DEDICATED', 2, 'SHARED', 3, 'PSEUDO', 'NONE'), s.ksuudsid, -- SCHEMA# NUMBER s.ksuudsna, -- SCHEMANAME VARCHAR2(30) s.ksuseunm, -- OSUSER VARCHAR2(30) s.ksusepid, -- PROCESS VARCHAR2(12) s.ksusemnm, -- MACHINE VARCHAR2(64) s.ksusetid, -- TERMINAL VARCHAR2(30) s.ksusepnm, -- PROGRAM VARCHAR2(48) decode -- TYPE VARCHAR2(10) (bitand (s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?'), s.ksusesql, -- SQL_ADDRESS RAW(4) s.ksusesqh, -- SQL_HASH_VALUE NUMBER s.ksusesqi, -- SQL_ID VARCHAR2(13) decode -- SQL_CHILD_NUMBER NUMBER (s.ksusesch, 65535, to_number (null), s.ksusesch), s.ksusepsq, -- PREV_SQL_ADDR RAW(4) s.ksusepha, -- PREV_HASH_VALUE NUMBER s.ksusepsi, -- PREV_SQL_ID VARCHAR2(13) decode -- PREV_CHILD_NUMBER NUMBER (s.ksusepch, 65535, to_number (null), s.ksusepch), s.ksuseapp, -- MODULE VARCHAR2(48) s.ksuseaph, -- MODULE_HASH NUMBER s.ksuseact, -- ACTION VARCHAR2(32) s.ksuseach, -- ACTION_HASH NUMBER s.ksusecli, -- CLIENT_INFO VARCHAR2(64) s.ksusefix, -- FIXED_TABLE_SEQUENCE NUMBER s.ksuseobj, -- ROW_WAIT_OBJ# NUMBER s.ksusefil, -- ROW_WAIT_FILE# NUMBER s.ksuseblk, -- ROW_WAIT_BLOCK# NUMBER s.ksuseslt, -- ROW_WAIT_ROW# NUMBER s.ksuseltm, -- LOGON_TIME DATE s.ksusectm, -- LAST_CALL_ET NUMBER decode -- PDML_ENABLED VARCHAR2(3) (bitand (s.ksusepxopt, 12), 0, 'NO', 'YES'), decode -- FAILOVER_TYPE VARCHAR2(13) (s.ksuseft, 2, 'SESSION', 4, 'SELECT', 8, 'TRANSACTIONAL', 'NONE'), decode -- FAILOVER_METHOD VARCHAR2(10) (s.ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'), decode -- FAILED_OVER VARCHAR2(3) (s.ksusefs, 1, 'YES', 'NO'), s.ksusegrp, -- RESOURCE_CONSUMER_GROUP VARCHAR2(32) decode -- PDML_STATUS VARCHAR2(8) (bitand (s.ksusepxopt, 4), 4, 'ENABLED', decode (bitand (s.ksusepxopt, 8), 8, 'FORCED', 'DISABLED')), decode -- PDDL_STATUS VARCHAR2(8) (bitand (s.ksusepxopt, 2), 2, 'FORCED', decode (bitand (s.ksusepxopt, 1), 1, 'DISABLED', 'ENABLED')), decode -- PQ_STATUS VARCHAR2(8) (bitand (s.ksusepxopt, 32), 32, 'FORCED', decode (bitand (s.ksusepxopt, 16), 16, 'DISABLED', 'ENABLED')), s.ksusecqd, -- CURRENT_QUEUE_DURATION NUMBER s.ksuseclid, -- CLIENT_IDENTIFIER VARCHAR2(64) decode -- BLOCKING_SESSION_STATUS VARCHAR2(11) (s.ksuseblocker, 4294967295, 'UNKNOWN', 4294967294, 'GLOBAL', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT', 'VALID'), decode -- BLOCKING_SESSION NUMBER (s.ksuseblocker, 4294967295, to_number (null), 4294967294, to_number (null), 4294967293, to_number (null), 4294967292, to_number (null), 4294967291, to_number (null), s.ksuseblocker), s.ksuseseq, -- SEQ# NUMBER s.ksuseopc, -- EVENT# NUMBER e.kslednam, -- EVENT VARCHAR2(64) e.ksledp1, -- P1TEXT VARCHAR2(64) s.ksusep1, -- P1 NUMBER s.ksusep1r, -- P1RAW RAW(4) e.ksledp2, -- P2TEXT VARCHAR2(64) s.ksusep2, -- P2 NUMBER s.ksusep2r, -- P2RAW RAW(4) e.ksledp3, -- P3TEXT VARCHAR2(64) s.ksusep3, -- P3 NUMBER s.ksusep3r, -- P3RAW RAW(4) e.ksledclassid, -- WAIT_CLASS_ID NUMBER e.ksledclass#, -- WAIT_CLASS# NUMBER e.ksledclass, -- WAIT_CLASS VARCHAR2(64) decode -- WAIT_TIME NUMBER (s.ksusetim, 0, 0, -1, -1, -2, -2, decode (round (s.ksusetim/10000), 0, -1, round (s.ksusetim/10000))), s.ksusewtm, -- SECONDS_IN_WAIT NUMBER decode -- STATE VARCHAR2(19) (s.ksusetim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, 'WAITED SHORT TIME', decode (round (s.ksusetim/10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME')), s.ksusesvc -- SERVICE_NAME VARCHAR2(64) from x$ksuse s, x$ksled e where bitand (s.ksspaflg, 1) !=0 and bitand (s.ksuseflg, 1) !=0 and s.ksuseopc=e.indx
GV$SESSION_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 d.ksledhash -- EVENT_ID 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 ksulosqi, -- SQL_ID VARCHAR2(13) 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) e.ksledclassid, -- WAIT_CLASS_ID NUMBER e.ksledclass#, -- WAIT_CLASS# NUMBER e.ksledclass, -- WAIT_CLASS VARCHAR2(64) 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', decode (round (s.ksusstim/10000), 0, '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$SESSION_WAIT_CLASS
select s.inst_id, -- INST_ID NUMBER s.kslcssid, -- SID NUMBER s.kslcsser, -- SERIAL# NUMBER s.kslcsclsid, -- WAIT_CLASS_ID NUMBER s.kslcscls, -- WAIT_CLASS# NUMBER s.kslcsclsname, -- WAIT_CLASS VARCHAR2(64) s.kslcswts, -- TOTAL_WAITS NUMBER round -- TIME_WAITED NUMBER (s.kslcstim / 10000) from x$kslcs s where s.kslcswts != 0
GV$SESSION_WAIT_HISTORY
select s.inst_id, -- INST_ID NUMBER s.ksusehsnum, -- SID NUMBER s.ksusehwnum, -- SEQ# NUMBER s.ksusehopc, -- EVENT# NUMBER s.ksusehname, -- EVENT VARCHAR2(64) s.ksusehp1text, -- P1TEXT VARCHAR2(64) s.ksusehp1, -- P1 NUMBER s.ksusehp2text, -- P2TEXT VARCHAR2(64) s.ksusehp2, -- P2 NUMBER s.ksusehp3text, -- P3TEXT VARCHAR2(64) s.ksusehp3, -- P3 NUMBER round -- WAIT_TIME NUMBER (s.ksusehwtm/10000), s.ksusehcnt -- WAIT_COUNT NUMBER from x$ksuseh s
GV$SESSMETRIC
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER sessid, -- SESSION_ID NUMBER sernum, -- SERIAL_NUM NUMBER cpu, -- CPU NUMBER phyrds, -- PHYSICAL_READS NUMBER pga_memory, -- PGA_MEMORY NUMBER hard_parses, -- HARD_PARSES NUMBER soft_parses, -- SOFT_PARSES NUMBER phyrds_pct, -- PHYSICAL_READ_PCT NUMBER logrds_pct -- LOGICAL_READ_PCT NUMBER FROM x$kewmsemv WHERE flag1 = 1
GV$SESSTAT
select inst_id, -- INST_ID NUMBER ksusenum, -- 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$SESS_TIME_MODEL
select map.inst_id, -- INST_ID NUMBER sesv.ksusenum, -- SID NUMBER map.extid, -- STAT_ID NUMBER map.sname, -- STAT_NAME VARCHAR2(64) sesv.kewsval -- VALUE NUMBER from x$kewssmap map, x$kewssesv sesv where map.soffst = sesv.kewsnum and map.aggid = 1 and bitand (sesv.ksspaflg, 1) !=0 and bitand (sesv.ksuseflg, 1) !=0 and (map.stype = 2 or map.stype = 3)
GV$SES_OPTIMIZER_ENV
select INST_ID, -- INST_ID NUMBER SID_QKSCESEROW, -- SID NUMBER PNUM_QKSCESEROW, -- ID NUMBER PNAME_QKSCESEROW, -- NAME VARCHAR2(40) decode -- ISDEFAULT VARCHAR2(3) (bitand (FLAGS_QKSCESEROW, 2), 0, 'NO', 'YES'), PVALUE_QKSCESEROW -- VALUE VARCHAR2(25) from X$QKSCESES where bitand (FLAGS_QKSCESEROW, 8) = 0 and (bitand (FLAGS_QKSCESEROW, 4) = 0 or bitand (FLAGS_QKSCESEROW, 2) = 0)
GV$SGA
select inst_id, -- INST_ID NUMBER ksmsdnam, -- NAME VARCHAR2(20) ksmsdval -- VALUE NUMBER from x$ksmsd
GV$SGAINFO
select b, -- INST_ID NUMBER c, -- NAME VARCHAR2(32) d, -- BYTES NUMBER e -- RESIZEABLE VARCHAR2(3) from ( select ksmsgmemidx a, inst_id b, ksmsgmemnam c, ksmsgmemval d, decode (ksmsgmemrez, 0, 'No', 1, 'Yes', NULL) e from x$ksmsgmem union select 32 a, USERENV ('Instance') b, 'Free SGA Memory Available' c, current_size d, NULL e from v$sga_dynamic_free_memory)
GV$SGASTAT
select inst_id, -- INST_ID NUMBER '', -- POOL VARCHAR2(12) 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(12) 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(12) 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(12) ksmssnam, -- NAME VARCHAR2(26) sum (ksmsslen) -- BYTES NUMBER from x$ksmjs where ksmsslen>1 group by inst_id, 'java pool', ksmssnam union all select inst_id, -- INST_ID NUMBER 'streams pool', -- POOL VARCHAR2(12) ksmssnam, -- NAME VARCHAR2(26) sum (ksmsslen) -- BYTES NUMBER from x$ksmstrs where ksmsslen>1 group by inst_id, 'streams pool', ksmssnam
GV$SGA_CURRENT_RESIZE_OPS
select sc.inst_id, -- INST_ID NUMBER sc.component, -- COMPONENT VARCHAR2(64) decode -- OPER_TYPE VARCHAR2(13) (sc.opcode, 0, 'STATIC', 1, 'INITIALIZING', 2, 'DISABLED', 3, 'GROW', 4, 'SHRINK', 5, 'SHRINK_CANCEL', NULL), decode -- OPER_MODE VARCHAR2(9) (sc.opmode, 1, 'MANUAL', 2, 'DEFERRED', 3, 'IMMEDIATE', NULL), pn.name, -- PARAMETER VARCHAR2(80) sc.initsize -- INITIAL_SIZE NUMBER * sc.gransize, sc.targsize -- TARGET_SIZE NUMBER * sc.gransize, sc.cursize -- CURRENT_SIZE NUMBER * sc.gransize, sc.starttime, -- START_TIME DATE sc.lasttime -- LAST_UPDATE_TIME DATE from x$kmgsct sc, v$parameter pn where (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 st.component, -- COMPONENT VARCHAR2(64) st.cursize -- CURRENT_SIZE NUMBER * st.gransize, st.minsize -- MIN_SIZE NUMBER * st.gransize, st.maxsize -- MAX_SIZE NUMBER * st.gransize, st.usersize -- USER_SPECIFIED_SIZE NUMBER * st.gransize, st.opercnt, -- OPER_COUNT NUMBER decode -- LAST_OPER_TYPE VARCHAR2(13) (st.lastoper, 0, 'STATIC', 1, 'INITIALIZING', 2, 'DISABLED', 3, 'GROW', 4, 'SHRINK', 5, 'SHRINK_CANCEL', NULL), decode -- LAST_OPER_MODE VARCHAR2(9) (st.lastmode, 1, 'MANUAL', 2, 'DEFERRED', 3, 'IMMEDIATE', NULL), st.lasttime, -- LAST_OPER_TIME DATE st.gransize -- GRANULE_SIZE NUMBER from x$kmgsct st
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$kmgsct gv where rownum=1
GV$SGA_RESIZE_OPS
select op.inst_id, -- INST_ID NUMBER gv.component, -- COMPONENT VARCHAR2(64) decode -- OPER_TYPE VARCHAR2(13) (op.opcode, 0, 'STATIC', 1, 'INITIALIZING', 2, 'DISABLED', 3, 'GROW', 4, 'SHRINK', 5, 'SHRINK_CANCEL', NULL), decode -- OPER_MODE VARCHAR2(9) (op.opmode, 1, 'MANUAL', 2, 'DEFERRED', 3, 'IMMEDIATE', NULL), pn.name, -- PARAMETER VARCHAR2(80) op.initsize -- INITIAL_SIZE NUMBER * gv.gransize, op.targsize -- TARGET_SIZE NUMBER * gv.gransize, op.realsize -- FINAL_SIZE NUMBER * gv.gransize, decode -- STATUS VARCHAR2(9) (op.status, 0, 'INACTIVE', 1, 'PENDING', 2, 'COMPLETE', 3, 'CANCELLED', 4, 'ERROR', 5, 'ERROR', 6, 'CANCELLED', 7, 'CANCELLED', NULL), op.starttime, -- START_TIME DATE op.endtime -- END_TIME DATE from x$kmgsop op, x$kmgsct 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_parsetime, -- ESTD_LC_LOAD_TIME NUMBER decode -- ESTD_LC_LOAD_TIME_FACTOR NUMBER (kglsim_baseparsetime, 0, to_number (null), round (kglsim_parsetime / kglsim_baseparsetime, 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)), sum (requests) requests, -- REQUESTS NUMBER sum -- REQUEST_MISSES NUMBER (request_misses) request_misses, sum -- LAST_MISS_SIZE NUMBER (last_miss_size) last_miss_size, sum -- MAX_MISS_SIZE NUMBER (max_miss_size) max_miss_size, sum -- REQUEST_FAILURES NUMBER (request_failures) request_failures, sum -- LAST_FAILURE_SIZE NUMBER (last_failure_size) last_failure_size, sum -- ABORTED_REQUEST_THRESHOLD NUMBER (aborted_request_threshold) aborted_request_threshold, sum -- ABORTED_REQUESTS NUMBER (aborted_requests) aborted_requests, sum -- LAST_ABORTED_SIZE NUMBER (last_aborted_size) last_aborted_size from x$ksmspr, ( select sum (kghlurcn) requests, sum (kghlurmi) request_misses, max (kghlurmz) last_miss_size, max (kghlurmx) max_miss_size, sum (kghlunfu) request_failures, max (kghlunfs) last_failure_size, max (kghlumxa) aborted_request_threshold, sum (kghlumer) aborted_requests, max (kghlumes) last_aborted_size from 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 prev_sql_id, -- SQL_ID VARCHAR2(13) 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) KSPSPFFTCTXSPDVALUE, -- DISPLAY_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) kglfnobj, -- SQL_FULLTEXT CLOB kglobt03, -- SQL_ID VARCHAR2(13) 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 kglobt35, -- END_OF_FETCH_COUNT 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 kglobwdw, -- DIRECT_WRITES NUMBER kglobt14, -- BUFFER_GETS NUMBER kglobwap, -- APPLICATION_WAIT_TIME NUMBER kglobwcc, -- CONCURRENCY_WAIT_TIME NUMBER kglobwcl, -- CLUSTER_WAIT_TIME NUMBER kglobwui, -- USER_IO_WAIT_TIME NUMBER kglobt42, -- PLSQL_EXEC_TIME NUMBER kglobt43, -- JAVA_EXEC_TIME 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 kglobcce, -- OPTIMIZER_ENV RAW(652) kglobcceh, -- OPTIMIZER_ENV_HASH_VALUE 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 kglobt46, -- OLD_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 kglobts3, -- SQL_PROFILE VARCHAR2(64) kglobt44, -- PROGRAM_ID NUMBER kglobt45 -- PROGRAM_LINE# NUMBER from x$kglcursor where kglhdadr != kglhdpar and kglobt02 != 0
GV$SQLAREA
select inst_id, -- INST_ID NUMBER kglnaobj, -- SQL_TEXT VARCHAR2(1000) kglobt03, -- SQL_ID VARCHAR2(13) 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 (kglobt35), -- END_OF_FETCH_COUNT 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 (kglobwdw), -- DIRECT_WRITES NUMBER sum (kglobt14), -- BUFFER_GETS NUMBER sum (kglobwap), -- APPLICATION_WAIT_TIME NUMBER sum (kglobwcc), -- CONCURRENCY_WAIT_TIME NUMBER sum (kglobwcl), -- CLUSTER_WAIT_TIME NUMBER sum (kglobwui), -- USER_IO_WAIT_TIME NUMBER sum (kglobt42), -- PLSQL_EXEC_TIME NUMBER sum (kglobt43), -- JAVA_EXEC_TIME 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 kglobt46, -- OLD_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 kglobt44 -- PROGRAM_ID NUMBER from x$kglcursor group by inst_id, kglnaobj, kglobt03, kglhdpar, kglnahsh, kglnatim, kglobts0, kglobt19, kglobts1, kglobt20, decode (kglobt33, 1, 'Y', 'N'), kglhdclt, kglobt44, kglobt46 having sum (decode (kglobt09, 0, kglobt02, 0)) != 0
GV$SQLTEXT
select inst_id, -- INST_ID NUMBER kglhdadr, -- ADDRESS RAW(4) kglnahsh, -- HASH_VALUE NUMBER kglnasqlid, -- SQL_ID VARCHAR2(13) 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 kglnasqlid, -- SQL_ID VARCHAR2(13) 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_OPTIMIZER_ENV
select INST_ID, -- INST_ID NUMBER KQLFSQCE_PHAD, -- ADDRESS RAW(4) KQLFSQCE_HASH, -- HASH_VALUE NUMBER KQLFSQCE_SQLID, -- SQL_ID VARCHAR2(13) KQLFSQCE_HADD, -- CHILD_ADDRESS RAW(4) KQLFSQCE_CHNO, -- CHILD_NUMBER NUMBER KQLFSQCE_PNUM, -- ID NUMBER KQLFSQCE_PNAME, -- NAME VARCHAR2(40) decode -- ISDEFAULT VARCHAR2(3) (bitand (KQLFSQCE_FLAGS, 2), 0, 'NO', 'YES'), KQLFSQCE_PVALUE -- VALUE VARCHAR2(25) from X$KQLFSQCE where bitand (KQLFSQCE_FLAGS, 8) = 0 and (bitand (KQLFSQCE_FLAGS, 4) = 0 or bitand (KQLFSQCE_FLAGS, 2) = 0)
GV$SQL_PLAN
select inst_id, -- INST_ID NUMBER kqlfxpl_phad, -- ADDRESS RAW(4) kqlfxpl_hash, -- HASH_VALUE NUMBER kqlfxpl_sqlid, -- SQL_ID VARCHAR2(13) kqlfxpl_plhash, -- PLAN_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(40) (kqlfxpl_tqid, 1, 40), 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(31) (p.kqlfxpl_objname, o.name), kqlfxpl_alias, -- OBJECT_ALIAS VARCHAR2(65) substr -- OBJECT_TYPE VARCHAR2(20) (kqlfxpl_objtype, 1, 20), 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 to_number -- POSITION NUMBER (decode (kqlfxpl_pos, 0, decode (kqlfxpl_cost, 4294967295, NULL, kqlfxpl_cost), kqlfxpl_pos)), kqlfxpl_scols, -- SEARCH_COLUMNS NUMBER to_number -- COST NUMBER (decode (kqlfxpl_cost, 4294967295, 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, 4294967295, NULL, kqlfxpl_cpuc)), to_number -- IO_COST NUMBER (decode (kqlfxpl_ioct, 4294967295, 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) kqlfxpl_proj, -- PROJECTION VARCHAR2(4000) to_number -- TIME NUMBER (decode (kqlfxpl_time, 0, NULL, kqlfxpl_time)), kqlfxpl_qblock, -- QBLOCK_NAME VARCHAR2(31) kqlfxpl_remark -- REMARKS 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 SQLID_QESRS, -- SQL_ID VARCHAR2(13) PLHASH_QESRS, -- PLAN_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 SQLID_QESRS, -- SQL_ID VARCHAR2(13) PLHASH_QESRS, -- PLAN_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(40) (tqid_qesrs, 1, 40), 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(31) (p.objname_qesrs, o.name), alias_qesrs, -- OBJECT_ALIAS VARCHAR2(65) substr -- OBJECT_TYPE VARCHAR2(20) (objtype_qesrs, 1, 20), 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 to_number -- POSITION NUMBER (decode (pos_qesrs, 0, decode (cost_qesrs, 4294967295, NULL, cost_qesrs), pos_qesrs)), scols_qesrs, -- SEARCH_COLUMNS NUMBER to_number -- COST NUMBER (decode (cost_qesrs, 4294967295, 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, 4294967295, NULL, cpuc_qesrs)), to_number -- IO_COST NUMBER (decode (ioct_qesrs, 4294967295, 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) PROJ_QESRS, -- PROJECTION VARCHAR2(4000) to_number -- TIME NUMBER (decode (time_qesrs, 0, NULL, time_qesrs)), QBLOCK_QESRS, -- QBLOCK_NAME VARCHAR2(31) REMARK_QESRS, -- REMARKS 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.kglobt03, -- SQL_ID VARCHAR2(13) 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 sql_id, -- SQL_ID VARCHAR2(13) kglhdpar, -- ADDRESS RAW(4) kglhdadr, -- CHILD_ADDRESS RAW(4) childno, -- CHILD_NUMBER NUMBER 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'), decode -- ANYDATA_TRANSFORMATION VARCHAR2(1) (bitand (bitvector, POWER (2, 31)), POWER (2, 31), 'Y', 'N'), decode -- INCOMPLETE_CURSOR VARCHAR2(1) (bitand (bitvector, POWER (2, 32)), POWER (2, 32), 'Y', 'N'), decode -- TOP_LEVEL_RPI_CURSOR VARCHAR2(1) (bitand (bitvector, POWER (2, 33)), POWER (2, 33), 'Y', 'N'), decode -- DIFFERENT_LONG_LENGTH VARCHAR2(1) (bitand (bitvector, POWER (2, 34)), POWER (2, 34), 'Y', 'N'), decode -- LOGICAL_STANDBY_APPLY VARCHAR2(1) (bitand (bitvector, POWER (2, 35)), POWER (2, 35), 'Y', 'N'), decode -- DIFF_CALL_DURN VARCHAR2(1) (bitand (bitvector, POWER (2, 36)), POWER (2, 36), 'Y', 'N'), decode -- BIND_UACS_DIFF VARCHAR2(1) (bitand (bitvector, POWER (2, 37)), POWER (2, 37), 'Y', 'N'), decode -- PLSQL_CMP_SWITCHS_DIFF VARCHAR2(1) (bitand (bitvector, POWER (2, 38)), POWER (2, 38), '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) kglfnobj, -- SQL_FULLTEXT CLOB kglnahsh, -- HASH_VALUE NUMBER kglobt03, -- SQL_ID VARCHAR2(13) 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 SQLID_QKSMM, -- SQL_ID VARCHAR2(13) 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 SQLHASHV, -- SQL_HASH_VALUE NUMBER SQLID, -- SQL_ID VARCHAR2(13) 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 decode -- DBID VARCHAR2(40) (slpdb, 0, 'UNASSIGNED', to_number (slpdb)), slthr, -- THREAD# NUMBER slseq, -- SEQUENCE# NUMBER slsiz*slbsz, -- BYTES NUMBER decode -- USED NUMBER (slnab, 0, 0, (slnab-1) *slbsz), 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 ksusdhsh -- STAT_ID 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_ignored_knstacr, -- TOTAL_IGNORED NUMBER total_rollbacks_knstacr, -- TOTAL_ROLLBACKS 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 last_browse_num_knstasl, -- LAST_BROWSE_NUM NUMBER oldest_scn_num_knstasl, -- OLDEST_SCN_NUM NUMBER last_browse_seq_knstasl, -- LAST_BROWSE_SEQ NUMBER last_deq_seq_knstasl -- LAST_DEQ_SEQ 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', 12, 'ROLLBACK TRANSACTION', 13, 'TRANSACTION CLEANUP'), 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_rollbacks_knstasl, -- TOTAL_ROLLBACKS 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 x.inst_id, -- INST_ID NUMBER x.sid_knst, -- SID NUMBER x.serial_knst, -- SERIAL# NUMBER x.capnum_knstcap, -- CAPTURE# NUMBER x.capname_knstcap, -- CAPTURE_NAME VARCHAR2(30) x.logminer_id_knstcap, -- LOGMINER_ID NUMBER x.startup_time_knstcap, -- STARTUP_TIME DATE case -- STATE VARCHAR2(161) when (x.state_knstcap = 10 and d.loaded = 'ACTIVE') then d.current_state else DECODE (x.state_knstcap, 0, 'INITIALIZING', 1, 'CAPTURING CHANGES', 2, 'EVALUATING RULE', 3, 'ENQUEUING MESSAGE', 4, 'SHUTTING DOWN', 5, 'ABORTING', 6, 'CREATING LCR', 7, 'WAITING FOR DICTIONARY REDO', 8, 'WAITING FOR REDO', 9, 'PAUSED FOR FLOW CONTROL', 10, 'DICTIONARY INITIALIZATION') end, m.msgs_filtered, -- TOTAL_PREFILTER_DISCARDED NUMBER m.msgs_kept, -- TOTAL_PREFILTER_KEPT NUMBER m.msgs_total, -- TOTAL_PREFILTER_EVALUATIONS NUMBER x.total_captured_knstcap, -- TOTAL_MESSAGES_CAPTURED NUMBER x.recent_time_knstcap, -- CAPTURE_TIME DATE x.recent_msg_num_knstcap, -- CAPTURE_MESSAGE_NUMBER NUMBER x.recent_msg_time_knstcap, -- CAPTURE_MESSAGE_CREATE_TIME DATE x.total_messages_created_knstcap, -- TOTAL_MESSAGES_CREATED NUMBER x.total_full_evaluations_knstcap, -- TOTAL_FULL_EVALUATIONS NUMBER x.total_msg_enq_knstcap, -- TOTAL_MESSAGES_ENQUEUED NUMBER x.enqueue_time_knstcap, -- ENQUEUE_TIME DATE x.enqueue_msg_num_knstcap, -- ENQUEUE_MESSAGE_NUMBER NUMBER x.enqueue_msg_time_knstcap, -- ENQUEUE_MESSAGE_CREATE_TIME DATE DECODE -- AVAILABLE_MESSAGE_NUMBER NUMBER (bitand (x.flags_knstcap, 1), 0, upstream.next_scn, 1, downstream.next_scn), DECODE -- AVAILABLE_MESSAGE_CREATE_TIME DATE (bitand (x.flags_knstcap, 1), 0, upstream.next_time, 1, downstream.next_time), x.elapsed_capture_time_knstcap, -- ELAPSED_CAPTURE_TIME NUMBER x.elapsed_rule_time_knstcap, -- ELAPSED_RULE_TIME NUMBER x.elapsed_enqueue_time_knstcap, -- ELAPSED_ENQUEUE_TIME NUMBER x.elapsed_lcr_time_knstcap, -- ELAPSED_LCR_TIME NUMBER x.elapsed_wait_time_knstcap, -- ELAPSED_REDO_WAIT_TIME NUMBER x.elapsed_pause_time_knstcap -- ELAPSED_PAUSE_TIME NUMBER FROM x$knstcap x, ( SELECT session_id, sum (skipped_filter_calls) msgs_filtered, sum (kept_filter_calls) msgs_kept, sum (total_filter_calls) msgs_total FROM x$logmnr_process GROUP BY session_id) m, ( SELECT last_write_scn next_scn, last_write_scn_time next_time FROM x$kcrfws) upstream, ( SELECT session#, max (next_change#) next_scn, max (next_time) next_time FROM system.logmnr_log$ GROUP BY session#) downstream, x$logmnr_dictionary_load d WHERE type_knst=8 AND x.logminer_id_knstcap = m.session_id (+) AND x.logminer_id_knstcap = d.session_id (+) AND x.logminer_id_knstcap = downstream.session# (+) 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$SYSAUX_OCCUPANTS
SELECT inst_id, -- INST_ID NUMBER occ_name_kewxocf, -- OCCUPANT_NAME VARCHAR2(64) occ_desc_kewxocf, -- OCCUPANT_DESC VARCHAR2(64) sch_name_kewxocf, -- SCHEMA_NAME VARCHAR2(64) move_proc_kewxocf, -- MOVE_PROCEDURE VARCHAR2(64) move_desc_kewxocf, -- MOVE_PROCEDURE_DESC VARCHAR2(64) space_usage_kewxocf -- SPACE_USAGE_KBYTES NUMBER FROM x$kewxocf
GV$SYSMETRIC
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER groupid, -- GROUP_ID NUMBER metricid, -- METRIC_ID NUMBER name, -- METRIC_NAME VARCHAR2(64) value, -- VALUE NUMBER unit -- METRIC_UNIT VARCHAR2(64) FROM x$kewmdrmv WHERE flag1 = 1 AND groupid in (2, 3)
GV$SYSMETRIC_HISTORY
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER groupid, -- GROUP_ID NUMBER metricid, -- METRIC_ID NUMBER name, -- METRIC_NAME VARCHAR2(64) value, -- VALUE NUMBER unit -- METRIC_UNIT VARCHAR2(64) FROM x$kewmdrmv WHERE groupid in (2, 3)
GV$SYSMETRIC_SUMMARY
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER groupid, -- GROUP_ID NUMBER metricid, -- METRIC_ID NUMBER name, -- METRIC_NAME VARCHAR2(64) numintv, -- NUM_INTERVAL NUMBER max, -- MAXVAL NUMBER min, -- MINVAL NUMBER avg, -- AVERAGE NUMBER std, -- STANDARD_DEVIATION NUMBER unit -- METRIC_UNIT VARCHAR2(64) FROM x$kewmsmdv WHERE groupid = 2
GV$SYSSTAT
select inst_id, -- INST_ID NUMBER indx, -- STATISTIC# NUMBER ksusdnam, -- NAME VARCHAR2(64) ksusdcls, -- CLASS NUMBER ksusgstv, -- VALUE NUMBER ksusdhsh -- STAT_ID 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 d.ksledhash -- EVENT_ID 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(80) ksppity, -- TYPE NUMBER ksppstvl, -- VALUE VARCHAR2(512) ksppstdvl, -- DISPLAY_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 -- ISINSTANCE_MODIFIABLE VARCHAR2(5) (bitand (ksppiflg, 4), 4, 'FALSE', decode (bitand (ksppiflg/65536, 3), 0, 'FALSE', 'TRUE')), decode -- ISMODIFIED VARCHAR2(8) (bitand (ksppstvf, 7), 1, 'MODIFIED', 'FALSE'), decode -- ISADJUSTED VARCHAR2(5) (bitand (ksppstvf, 2), 2, 'TRUE', 'FALSE'), decode -- ISDEPRECATED VARCHAR2(5) (bitand (ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc, -- DESCRIPTION VARCHAR2(255) ksppstcmnt, -- UPDATE_COMMENT VARCHAR2(255) ksppihash -- HASH NUMBER 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(80) ksppity, -- TYPE NUMBER kspftctxvl, -- VALUE VARCHAR2(512) kspftctxdvl, -- DISPLAY_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 -- ISINSTANCE_MODIFIABLE VARCHAR2(5) (bitand (ksppiflg, 4), 4, 'FALSE', decode (bitand (ksppiflg/65536, 3), 0, 'FALSE', 'TRUE')), decode -- ISMODIFIED VARCHAR2(8) (bitand (kspftctxvf, 7), 1, 'MODIFIED', 'FALSE'), decode -- ISADJUSTED VARCHAR2(5) (bitand (kspftctxvf, 2), 2, 'TRUE', 'FALSE'), decode -- ISDEPRECATED VARCHAR2(5) (bitand (ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc, -- DESCRIPTION VARCHAR2(255) 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$SYSTEM_WAIT_CLASS
select s.inst_id, -- INST_ID NUMBER s.kslscsclsid, -- WAIT_CLASS_ID NUMBER s.kslscscls, -- WAIT_CLASS# NUMBER s.kslscsclsname, -- WAIT_CLASS VARCHAR2(64) s.kslscswts, -- TOTAL_WAITS NUMBER round -- TIME_WAITED NUMBER (s.kslscstim / 10000) from x$kslscs s where s.kslscswts != 0
GV$SYS_OPTIMIZER_ENV
select INST_ID, -- INST_ID NUMBER PNUM_QKSCESYROW, -- ID NUMBER PNAME_QKSCESYROW, -- NAME VARCHAR2(40) decode -- ISDEFAULT VARCHAR2(3) (bitand (FLAGS_QKSCESYROW, 2), 0, 'NO', 'YES'), PVALUE_QKSCESYROW, -- VALUE VARCHAR2(25) DEFPVALUE_QKSCESYROW -- DEFAULT_VALUE VARCHAR2(25) from X$QKSCESYS where bitand (FLAGS_QKSCESYROW, 8) = 0 and (bitand (FLAGS_QKSCESYROW, 4) = 0 or bitand (FLAGS_QKSCESYROW, 2) = 0)
GV$SYS_TIME_MODEL
select map.inst_id, -- INST_ID NUMBER map.extid, -- STAT_ID NUMBER map.sname, -- STAT_NAME VARCHAR2(64) sysv.kewsval -- VALUE NUMBER from x$kewssmap map, x$kewssysv sysv where map.offst = sysv.indx and map.aggid = 1 and (map.stype = 2 or map.stype = 3)
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'), decode -- BIGFILE VARCHAR2(3) (bitand (tsflg, 4), 4, 'YES', 'NO'), decode -- FLASHBACK_ON VARCHAR2(3) (bitand (tsflg, 8), 8, '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 kdlt.inst_id, -- INST_ID NUMBER kdlt.kdltsno, -- SID NUMBER sum (kdlt.kdltctmp), -- CACHE_LOBS NUMBER sum (kdlt.kdltnctmp), -- NOCACHE_LOBS NUMBER abs.count -- ABSTRACT_LOBS NUMBER from X$KDLT kdlt, X$ABSTRACT_LOB abs group by kdlt.inst_id, kdlt.kdltsno, abs.count 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 0, -- X_2_NULL NUMBER 0, -- X_2_NULL_FORCED_WRITE NUMBER 0, -- X_2_NULL_FORCED_STALE NUMBER 0, -- X_2_S NUMBER 0, -- X_2_S_FORCED_WRITE NUMBER 0, -- S_2_NULL NUMBER 0, -- S_2_NULL_FORCED_STALE NUMBER 0, -- RBR NUMBER 0, -- RBR_FORCED_WRITE NUMBER 0, -- NULL_2_X NUMBER 0, -- S_2_X NUMBER 0 -- 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 NOT 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 NOT 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_HISTOGRAM
select k.inst_id, -- INST_ID NUMBER k.kcftiofno, -- FILE# NUMBER k.kcftiomaxdur, -- SINGLEBLKRDTIM_MILLI NUMBER k.kcftioval -- SINGLEBLKRDS NUMBER from x$kcftiohist k, x$kcctf f where f.tfdup <> 0 and f.tfnum=k.kcftiofno
GV$TEMP_PING
select x.inst_id, -- INST_ID NUMBER kcftiofno, -- FILE_NUMBER NUMBER 0, -- FREQUENCY NUMBER 0, -- X_2_NULL NUMBER 0, -- X_2_NULL_FORCED_WRITE NUMBER 0, -- X_2_NULL_FORCED_STALE NUMBER 0, -- X_2_S NUMBER 0, -- X_2_S_FORCED_WRITE NUMBER 0, -- X_2_SSX NUMBER 0, -- X_2_SSX_FORCED_WRITE NUMBER 0, -- S_2_NULL NUMBER 0, -- S_2_NULL_FORCED_STALE NUMBER 0, -- SS_2_NULL NUMBER 0, -- SS_2_RLS NUMBER 0, -- WRB NUMBER 0, -- WRB_FORCED_WRITE NUMBER 0, -- RBR NUMBER 0, -- RBR_FORCED_WRITE NUMBER 0, -- RBR_FORCED_STALE NUMBER 0, -- CBR NUMBER 0, -- CBR_FORCED_WRITE NUMBER 0, -- NULL_2_X NUMBER 0, -- S_2_X NUMBER 0, -- SSX_2_X NUMBER 0, -- 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 NOT 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 x$kccrt.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 tirsid, -- INSTANCE VARCHAR2(80) 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, x$kcctir where rtnlf!=0 and x$kcctir.inst_id=x$kccrt.inst_id and tirnum=rtnum
GV$THRESHOLD_TYPES
SELECT t.inst_id, -- INST_ID NUMBER mid_kelrtd, -- METRICS_ID NUMBER gid_kelrtd, -- METRICS_GROUP_ID NUMBER opmask_kelrtd, -- OPERATOR_MASK NUMBER typnam_keltosd, -- OBJECT_TYPE VARCHAR2(64) alrtid_kelrtd -- ALERT_REASON_ID NUMBER FROM x$kelrtd t, x$keltosd o WHERE typid_keltosd = objtype_kelrtd
GV$TIMER
select inst_id, -- INST_ID NUMBER ksutmtim -- HSECS NUMBER from x$ksutm
GV$TIMEZONE_FILE
select FILENAME, -- FILENAME VARCHAR2(12) VERSION -- VERSION NUMBER from X$TIMEZONE_FILE
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 to_date -- START_DATE DATE (ktcxbstm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'), ktcxbdsb, -- DSCN_BASE NUMBER ktcxbdsw, -- DSCN_WRAP NUMBER ktcxbssc, -- START_SCN NUMBER ktcxbdsc, -- DEPENDENT_SCN NUMBER ktcxbxid, -- XID RAW(8) ktcxbpid, -- PRV_XID RAW(8) ktcxbpxi -- PTX_XID RAW(8) 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$TRANSPORTABLE_PLATFORM
SELECT INST_ID, -- INST_ID NUMBER PLATFORM_ID, -- PLATFORM_ID NUMBER PLATFORM_NAME, -- PLATFORM_NAME VARCHAR2(101) decode -- ENDIAN_FORMAT VARCHAR2(14) (endian_format, 1, 'Big', 0, 'Little', 'UNKNOWN FORMAT') FROM x$kcpxpl
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 KTUSMSTRMQI, -- MAXQUERYID VARCHAR2(13) 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 KTUSMSTABK, -- ACTIVEBLKS NUMBER KTUSMSTUBK, -- UNEXPIREDBLKS NUMBER KTUSMSTEBK, -- EXPIREDBLKS NUMBER KTUSMSTTUR -- TUNED_UNDORETENTION 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.kglobt03, -- SQL_ID VARCHAR2(13) 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(4000) 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$WAITCLASSMETRIC
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER wait#, -- WAIT_CLASS# NUMBER wait_id, -- WAIT_CLASS_ID NUMBER average_waiter_count, -- AVERAGE_WAITER_COUNT NUMBER dbtime_in_wait, -- DBTIME_IN_WAIT NUMBER time_waited, -- TIME_WAITED NUMBER wait_count -- WAIT_COUNT NUMBER FROM x$kewmevmv WHERE flag1 = 1 AND GROUPID = 1
GV$WAITCLASSMETRIC_HISTORY
SELECT inst_id, -- INST_ID NUMBER begtime, -- BEGIN_TIME DATE endtime, -- END_TIME DATE intsize_csec, -- INTSIZE_CSEC NUMBER wait#, -- WAIT_CLASS# NUMBER wait_id, -- WAIT_CLASS_ID NUMBER average_waiter_count, -- AVERAGE_WAITER_COUNT NUMBER dbtime_in_wait, -- DBTIME_IN_WAIT NUMBER time_waited, -- TIME_WAITED NUMBER wait_count -- WAIT_COUNT NUMBER FROM x$kewmevmv WHERE GROUPID = 1
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$_RESUMABLE2
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) ktrsfobj, -- OBJID NUMBER ktrsftyp -- TYPE NUMBER from x$ktrso
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