Oracle | X$ Structures

Oracle X$ Structures Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This page is a work in progress. As I find demos and descriptions of X$ structures this page will continue to be enhanced.

Assignments on this page are based on research, white papers, and wild guesses based on naming conventions: Some may well be incorrect.

If you have any information that might further expand these contents please forward them to me. Thank you. Morgan

X$ Linked List Growth

Version	Number Views
6	~35
7.1	126
8.1	271
9.2	394
10.1	543
10.2	613
11.1	798
11.2	945
12.1	1062
12.2	1312
18.3	1347
19.3	1379
21.3	1422

Dependencies

V$FIXED_TABLE

V$INDEXED_FIXED_COLUMN

List X$ Arrays and related information conn / as sysdba desc v$fixed_table desc v$fixed_view_definition desc v$indexed_fixed_column SELECT kqftanam FROM x$kqfta ORDER BY 1; -- returns 1,377 objects SELECT t.kqftanam "Table Name" FROM x$kqfta t, x$kqfco c WHERE t.indx = c.kqfcotab AND c.kqfconam like '%SID%' INTERSECT SELECT DISTINCT t.kqftanam "Table Name" FROM x$kqfta t, x$kqfco c WHERE t.indx = c.kqfcotab AND c.kqfconam like '%NAM%'; -- returns 45 objects

Morgan's brute-force code for collecting the names of X$ objects. Not elegant but it gets the job done ... eventually.

Adding additional loops allows for brute force construction of arbitrarily sized X$ objects. Of course there is no need to do this given the answer can be obtained sub-second from v$fixed_table but it was an interesting academic exercise so it will remain here until I tire of it. CREATE TABLE xdollar ( xname VARCHAR2(30), numrows NUMBER); CREATE OR REPLACE PROCEDURE xdollarproc AUTHID DEFINER IS rindex BINARY_INTEGER; slno BINARY_INTEGER; sofar NUMBER(12,2); twork NUMBER; pfix CONSTANT VARCHAR2(23) := 'SELECT COUNT(*) FROM X$'; str1 VARCHAR2(128); str2 VARCHAR2(128); str3 VARCHAR2(128); str4 VARCHAR2(128); str5 VARCHAR2(128); xstr VARCHAR2(128); n xdollar.numrows%TYPE; BEGIN rindex := dbms_application_info.set_session_longops_nohint; -- one twork := ((95-65)+1)**1; FOR a IN 65 .. 95 LOOP sofar := (a-65); dbms_application_info.set_session_longops(rindex,slno,'1',0,0,sofar,twork,''); str1 := pfix || CHR(a); BEGIN xstr := str1; EXECUTE IMMEDIATE xstr INTO n; INSERT INTO xdollar (xname,numrows) VALUES ('X$' || CHR(a),n); EXCEPTION WHEN others THEN NULL; END; END LOOP; COMMIT; -- two twork := ((95-65)+1)**2; FOR a IN 65 .. 95 LOOP str1 := pfix || CHR(a); FOR b IN 65 .. 95 LOOP sofar := (a-65)*(b-65); dbms_application_info.set_session_longops(rindex,slno,'2',0,0,sofar,twork,''); str2 := str1 || CHR(b); BEGIN xstr := str2; EXECUTE IMMEDIATE xstr INTO n; INSERT INTO xdollar (xname, numrows) VALUES ('X$' || CHR(a) || CHR(b),n); EXCEPTION WHEN others THEN NULL; END; END LOOP; END LOOP; COMMIT; -- three twork := ((95-65)+1)**3; FOR a IN 65 .. 95 LOOP str1 := pfix || CHR(a); FOR b IN 65 .. 95 LOOP str2 := str1 || CHR(b); FOR c IN 65 .. 95 LOOP sofar := (a-65)*(b-65)*(c-65); dbms_application_info.set_session_longops(rindex,slno,'3',0,0,sofar,twork,''); str3 := str2 || CHR(c); BEGIN xstr := str3; EXECUTE IMMEDIATE xstr INTO n; INSERT INTO xdollar (xname,numrows) VALUES ('X$'||CHR(a)||CHR(b)||CHR(c),n); EXCEPTION WHEN others THEN NULL; END; END LOOP; END LOOP; END LOOP; COMMIT; -- four twork := ((95-65)+1)**4; FOR a IN 65 .. 95 LOOP str1 := pfix || CHR(a); FOR b IN 65 .. 95 LOOP str2 := str1 || CHR(b); FOR c IN 65 .. 95 LOOP str3 := str2 || CHR(c); FOR d IN 65 .. 95 LOOP sofar := (a-65)*(b-65)*(c-65)*(d-65); dbms_application_info.set_session_longops(rindex,slno,'4',0,0,sofar,twork,''); str4 := str3 || CHR(d); BEGIN xstr := str4; EXECUTE IMMEDIATE xstr INTO n; INSERT INTO xdollar (xname,numrows) VALUES ('X$' || CHR(a) || CHR(b) || CHR(c) || CHR(d),n); EXCEPTION WHEN others THEN NULL; END; END LOOP; END LOOP; END LOOP; END LOOP; COMMIT; -- five FOR a IN 65 .. 95 LOOP str1 := pfix || CHR(a); FOR b IN 65 .. 95 LOOP str2 := str1 || CHR(b); FOR c IN 65 .. 95 LOOP str3 := str2 || CHR(c); FOR d IN 65 .. 95 LOOP str4 := str3 || CHR(d); FOR e IN 65 .. 95 LOOP str5 := str4 || CHR(e); BEGIN xstr := str5; EXECUTE IMMEDIATE xstr INTO n; INSERT INTO xdollar (xname,numrows) VALUES ('X$' || CHR(a) || CHR(b) || CHR(c) || CHR(d) || CHR(e),n); EXCEPTION WHEN others THEN NULL; END; END LOOP; END LOOP; END LOOP; END LOOP; END LOOP; COMMIT; END xdollarproc; / exec xdollarproc;

Morgan's less brutal, but less satisfying, solution to obtaining the number of rows in each X$ object.

CREATE TABLE xdollar2 (

      xname   VARCHAR2(30),

      numrows NUMBER);

      

      CREATE OR REPLACE PROCEDURE xdollarproc AUTHID DEFINER IS

       pfix   CONSTANT VARCHAR2(21) := 'SELECT COUNT(*) FROM ';

       sqlStr          VARCHAR2(128);

       n               INTEGER;

      BEGIN

        FOR i IN (SELECT name FROM v$fixed_table WHERE name LIKE 'X$%') LOOP

          sqlStr := pfix || i.name;

          BEGIN

            execute immediate sqlStr INTO n;

          EXCEPTION

            WHEN OTHERS THEN

              n := -99;

          END;

          INSERT INTO xdollar2 (xname,numrows)

          VALUES (i.name,n);

        END LOOP;

        COMMIT;

      END xdollarproc;

      /

      

      set timing on

      exec xdollarproc

      Elapsed: 00:03:01.90

      

      SQL> SELECT xname FROM xdollar2 where NUMROWS = -99 ORDER BY 1;

      

      XNAME

      ------------------------------

      X$CDBVW$

      X$COMVW$

      X$KSIPC_INFO

      X$KSIPC_PROC_STATS

      X$KSXP_STATS

      X$LOGMNR_CONTENTS

      X$OBLNK$

      X$SKGXP_CONNECTION

      X$SKGXP_MISC

      X$SKGXP_PORT

      
          
        
          XNAME
      Reason
        
        
          X$CDBVW$
          ORA-65318: query to cross-container fixed tables not allowed
        
        
          X$COMVW$
          ORA-65318: query to cross-container fixed tables not allowed
        
        
          X$KSIPC_INFO
          ORA-00942: table or view does not exist
        
        
          X$KSIPC_PROC_STATS
          ORA-00942: table or view does not exist
        
        
          X$KSXP_STATS
          ORA-00942: table or view does not exist
        
        
          X$LOGMNR_CONTENTS
          ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents
        
        
          X$OBLNK$
          ORA-65318: query to cross-container fixed tables not allowed
        
        
          X$SKGXP_CONNECTION
          ORA-00942: table or view does not exist
        
        
          X$SKGXP_MISC
          ORA-00942: table or view does not exist
        
        
          X$SKGXP_PORT
          ORA-00942: table or view does not exist

V$ Reverse Engineering -- the following demo shows how to find the X$ source for GV$ objects from which V$ objects are derived SELECT view_name, view_definition FROM v$fixed_view_definition WHERE UPPER(view_definition) LIKE '%X$KKSBV%'; VIEW_NAME VIEW_DEFINITION -------------------- ------------------------------------------------------------------- GV$SQL_BIND_METADATA select inst_id,kglhdadr, position, kkscbndt, kkscbndl, kkscbnda, kksbvnnam, con_id from x$kksbv

X$ Naming Notes -- see the kernel subsystems link at page bottom for more hints on X$ naming

ALO = allocate
RLs = release

X$ Objects By Category

Active Session History

Object Name	Notes
X$ALL_ASH	17895 rows
X$ASH	Maps to V$ACTIVE_SESSION_HISTORY, 17843 rows

Advanced Queuing

Object Name	Notes
X$AQ_MESSAGE_CACHE_ADVICE	0 rows
X$AQ_OPT_CACHED_SUBSHARD	0 rows
X$AQ_OPT_INACTIVE_SUBSHARD	0 rows
X$AQ_OPT_STATS	1 row
X$AQ_OPT_UNCACHED_SUBSHARD	0 rows
X$AQ_REMOTE_DEQAFF	0 rows
X$AQ_SUBSCRIBER_LOAD	0 rows
X$AQ_SUBTRANS	0 rows
X$BUFFERED_PUBLISHERS	0 rows
X$BUFFERED_QUEUES	0 rows
X$BUFFERED_SUBSCRIBERS	0 rows
X$MESSAGE_CACHE	`SELECT queue_name, schema_name, enqueued_msgs, used_memory_size, state FROM x$message_cache;`
X$PERSISTENT_PUBLISHERS	0 rows
X$PERSISTENT_QUEUES	0 rows
X$PERSISTENT_SUBSCRIBERS	10 rows
X$UNFLUSHED_DEQUEUES	0 rows

Alert Log

Object Name	Notes
X$DBGALERTEXT	In memory copy of ADR alert log. Effectively indexed by date. Source for v$diag_alert_ext 6364 rows

Archived Redo Logs

Object Name	Notes
X$KCCAL	`ALTER SYSTEM SWITCH LOGFILE; SELECT alnam FROM x$kccal WHERE alnam IS NOT NULL; ALNAM ----------------------------------------------------------- /u21/app/oracle/orabase/recovery_area/orabase/archivelog/2021_11_21/ o1_mf_1_5_jsowcr1r_.arc`

Audit

Object Name	Notes
X$AUD_DPAPI_ACTIONS	3 rows
X$AUD_DP_ACTIONS	4 rows
X$AUD_DV_OBJ_EVENTS	Database Vault 14 rows
X$AUD_OBJ_ACTIONS	20 rows
X$AUD_OLS_ACTIONS	Oracle Label Security 19 rows
X$AUD_PROTOCOL_ACTIONS	SELECT action_name FROM x$aud_protocol_actions; ACTION_NAME ---------------------------------------------------------------- HTTP FTP AUTHENTICATION
X$AUD_XS_ACTIONS	Oracle Advanced Security 51 rows
X$DIR	col os_path format a60 SELECT os_path FROM x$dir ORDER BY 1; OS_PATH ------------------------------------------------------------ u21/app/oracle/product/dbhome_1/javavm/admin/ u21/app/oracle/orabase u21/app/oracle/orabase/admin/oraase\dpdump/ u21/app/oracle/orabase/homes/OraDB21Home1/ccr/state u21/app/oracle/orabase/homes/OraDB21Home1/ccr/state u21/app/oracle/orabase/homes/OraDB21Home1/rdbms/log u21/app/oracle/orabase/dbhome1 u21/app/oracle/orabase/dbhome1/cfgtoollogs u21/app/oracle/orabase/dbhome1/md/admin u21/app/oracle/orabase/dbhome1rdbms/admin u21/app/oracle/orabase/dbhome1/OPatch u21/app/oracle/orabase/dbhome1/QOpatch u21/app/oracle/orabase/dbhome1/bin/clr u21/app/oracle/orabase/dbhome1/rdbms/xml u21/app/oracle/orabase/dbhome1/rdbms/xml/schema SELECT con_id, obj#, audit$, os_path FROM x$dir ORDER BY 1,2;
X$UNIFIED_AUDIT_RECORD_FORMAT	103 rows SELECT component, audit_type, COUNT() FROM x$unified_audit_record_format GROUP BY component, audit_type ORDER BY 1; COMPONENT AUDIT_TYPE COUNT() -------------------- ---------- -------- AppContext 3 1 Basic 2 14 Database Vault 7 10 Datapump 10 2 Direct path API 11 1 FineGrainedAudit 5 1 KACL_AUDIT 12 3 Label Security 8 14 Protocol 13 5 RMAN_AUDIT 9 5 Session 1 16 Standard 4 16 XS 6 15
X$UNIFIED_AUDIT_TRAIL	0 rows
X$XML_AUDIT_TRAIL	0 rows

Automatic Diagnostic Repository (ADR)

Object Name	Notes
X$DBKRUN	Health management runs col cname format a30 col report format a10 SELECT name, cname, report FROM x$dbkrun; NAME CNAME REPORT ----------- ---------------------------- ------- HM_RUN_101 DB Structure Integrity Check HM_RUN_61 DB Structure Integrity Check
X$DIAG_ADR_CONTROL	ADR homes, paths, purge times, and similar administrative data 1 rows
X$DIAG_ADR_CONTROL_AUX	1 rows
X$DIAG_ADR_INVALIDATION	no rows when checked 1 rows
X$DIAG_ALERT_EXT	Text portions of the XML version of the Alert Log 6364 rows
X$DIAG_AMS_XACTION	0 rows
X$DIAG_DDE_USER_ACTION	contains incident_id 0 rows
X$DIAG_DDE_USER_ACTION_DEF	1 rows
X$DIAG_DDE_USR_ACT_PARAM	0 rows
X$DIAG_DDE_USR_ACT_PARAM_DEF	8 rows
X$DIAG_DDE_USR_INC_ACT_MAP	2 rows
X$DIAG_DDE_USR_INC_TYPE	6 rows
X$DIAG_DFW_CONFIG_CAPTURE	14 rows
X$DIAG_DFW_CONFIG_ITEM	5048 rows
X$DIAG_DFW_PATCH_CAPTURE	0 rows
X$DIAG_DFW_PATCH_ITEM	0 rows
X$DIAG_DFW_PURGE	0 rows
X$DIAG_DFW_PURGE_ITEM	0 rows
X$DIAG_DIAGV_INCIDENT	1 rows
X$DIAG_DIR_EXT	714 rows
X$DIAG_EM_DIAG_JOB	0 rows
X$DIAG_EM_TARGET_INFO	0 rows
X$DIAG_EM_USER_ACTIVITY	0 rows
X$DIAG_HM_FDG_SET	0 rows
X$DIAG_HM_FINDING	0 rows
X$DIAG_HM_INFO	0 rows
X$DIAG_HM_MESSAGE	0 rows
X$DIAG_HM_RECOMMENDATION	0 rows
X$DIAG_HM_RUN	2 rows
X$DIAG_INCCKEY	8 rows
X$DIAG_INCIDENT	1 rows
X$DIAG_INCIDENT_FILE	Lists incident files 2 rows
X$DIAG_INC_METER_CONFIG	1 rows
X$DIAG_INC_METER_IMPT_DEF	28 rows
X$DIAG_INC_METER_INFO	0 rows
X$DIAG_INC_METER_PK_IMPTS	229 rows
X$DIAG_INC_METER_SUMMARY	1 rows
X$DIAG_INFO	ADR equivalent to the database instance's V$PARAMETER 12 rows
X$DIAG_IPS_CONFIGURATION	24 rows
X$DIAG_IPS_FILE_COPY_LOG	0 rows
X$DIAG_IPS_FILE_METADATA	0 rows
X$DIAG_IPS_PACKAGE	0 rows
X$DIAG_IPS_PACKAGE_FILE	0 rows
X$DIAG_IPS_PACKAGE_HISTORY	0 rows
X$DIAG_IPS_PACKAGE_INCIDENT	0 rows
X$DIAG_IPS_PKG_UNPACK_HIST	0 rows
X$DIAG_IPS_PROGRESS_LOG	0 rows
X$DIAG_IPS_REMOTE_PACKAGE	0 rows
X$DIAG_LOG_EXT	2 rows
X$DIAG_PDB_PROBLEM	0 rows
X$DIAG_PDB_SPACE_MGMT	0 rows
X$DIAG_PICKLEERR	0 rows
X$DIAG_PROBLEM	1 rows
X$DIAG_RELMD_EXT	119 rows
X$DIAG_SWEEPERR	0 rows
X$DIAG_VEM_USER_ACTLOG	0 rows
X$DIAG_VEM_USER_ACTLOG1	0 rows
X$DIAG_VHM_RUN	2 rows
X$DIAG_VIEW	0 rows
X$DIAG_VIEWCOL	0 rows
X$DIAG_VINCIDENT	1 rows
X$DIAG_VINCIDENT_FILE	2 rows
X$DIAG_VINC_METER_INFO	0 rows
X$DIAG_VIPS_FILE_COPY_LOG	0 rows
X$DIAG_VIPS_FILE_METADATA	0 rows
X$DIAG_VIPS_PACKAGE_FILE	0 rows
X$DIAG_VIPS_PACKAGE_HISTORY	0 rows
X$DIAG_VIPS_PACKAGE_MAIN_INT	0 rows
X$DIAG_VIPS_PACKAGE_SIZE	0 rows
X$DIAG_VIPS_PKG_FILE	0 rows
X$DIAG_VIPS_PKG_INC_CAND	0 rows
X$DIAG_VIPS_PKG_INC_DTL	0 rows
X$DIAG_VIPS_PKG_INC_DTL1	0 rows
X$DIAG_VIPS_PKG_MAIN_PROBLEM	0 rows
X$DIAG_VNOT_EXIST_INCIDENT	1 rows
X$DIAG_VPDB_PROBLEM	0 rows
X$DIAG_VPROBLEM	1 rows
X$DIAG_VPROBLEM1	1 rows
X$DIAG_VPROBLEM2	1 rows
X$DIAG_VPROBLEM_BUCKET	1 rows
X$DIAG_VPROBLEM_BUCKET1	1 rows
X$DIAG_VPROBLEM_BUCKET_COUNT	1 rows
X$DIAG_VPROBLEM_INT	1 rows
X$DIAG_VPROBLEM_LASTINC	1 rows
X$DIAG_VSHOWCATVIEW	16 rows
X$DIAG_VSHOWINCB	1 rows
X$DIAG_VSHOWINCB_I	1 rows
X$DIAG_VTEST_EXISTS	1 rows
X$DIAG_V_ACTINC	1 rows
X$DIAG_V_ACTPROB	1 rows
X$DIAG_V_INCCOUNT	1 rows
X$DIAG_V_INCFCOUNT	1 rows
X$DIAG_V_INC_METER_INFO_PROB	0 rows
X$DIAG_V_IPSPRBCNT	0 rows
X$DIAG_V_IPSPRBCNT1	0 rows
X$DIAG_V_NFCINC	1 rows
X$DIAG_V_SWPERRCOUNT	0 rows

Backup & Recovery

Object Name	Notes
X$ESTIMATED_MTTR	Estimated Mean Time To Recovery SELECT fopen_time_avg, stat_based_on FROM x$estiamted_mttr; FOPEN_TIME_AVG STAT_BASED_ON -------------- -------------- 10977 RECOVERY
X$KCCBI	SELECT bitsm, bimdt, bidun FROM x$kccbi; BITSM BIMDT BIDUN -------------------- -------------------- ----------- 06/25/2021 14:54:31 06/25/2021 14:44:57 ORABASEXIX 06/25/2021 21:11:50 06/25/2021 14:44:57 ORABASEXIX 07/07/2021 08:51:22 07/02/2021 14:08:19 ORABASEXIX 07/15/2021 23:04:17 07/15/2021 18:01:01 ORABASEXIX 07/17/2021 19:20:29 07/16/2021 14:59:58 ORABASEXIX 07/17/2021 20:10:32 07/16/2021 14:59:58 ORABASEXIX SELECT start_time FROM v$rman_backup_job_details ORDER BY 1;
X$KCVFHONL	-- from a private communication with Riyaj Shamsudeen: v$backup is based upon x$kcvfhonl. KCV tables are mapping from file headers.Read from v$backup will show lots of db file sequential reads with block_id=1 and blocks=1, i.e. reading file headers. WAIT #1: nam='db file sequential read' ela= 942 file#=2 block#=1 blocks=1 obj#=-1 tim=497122949693 WAIT #1: nam='unspecified wait event' ela= 34380 p1=0 p2=0 p3=0 obj#=-1 tim=497122949979 WAIT #1: nam='db file sequential read' ela= 1188 file#=3 block#=1 blocks=1 obj#=-1 tim=497123009270 WAIT #1: nam='unspecified wait event' ela= 58053 p1=0 p2=0 p3=0 obj#=-1 tim=497123009523 WAIT #1: nam='db file sequential read' ela= 736 file#=4 block#=1 blocks=1 obj#=-1 tim=497123052425 WAIT #1: nam='unspecified wait event' ela= 42143 p1=0 p2=0 p3=0 obj#=-1 tim=497123052678 WAIT #1: nam='db file sequential read' ela= 2022 file#=5 block#=1 blocks=1 obj#=-1 tim=497123093842 WAIT #1: nam='unspecified wait event' ela= 39161 p1=0 p2=0 p3=0 obj#=-1 tim=497123094115 Here is the file header update, I was talking about. you can see that backup checkpointed at SCN from thread 6. This is not a problem per se, but other sessions in the remote nodes are not reading the file headers. `Backup Checkpointed at scn: 0x0b26.2cc0c5c8 04/12/2011 18:25:34 thread:6 rba:(0x69fb.8fa5.10) enabled threads: 00000111 11100000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000`

Buffer Headers

Object Name Notes

X$BH --commonly used to locate file# and block# when diagnosing cache buffers chains latch contention.

SELECT bh.obj, bh.dbarfil, bh.dbablk FROM x$bh bh, v$latch_children lc WHERE bh.hladdr = lc.addr;

-- for the said latch (whose sleeps you think are too high).

-- To determine if a specific buffer has too many clones:

SELECT dbarfil, dbablk, COUNT(*) FROM x$bh GROUP BY dbarfil, dbablk HAVING COUNT(*) > 9; DBARFIL DBABLK COUNT(*) -------- ------ -------- 3 8915 10

obj column matches dba_objects.data_object_id
The tch column, touch count, records how many times a particular buffer has been accessed
Tim is time the buffer touch happened
Lru_flag relates to a buffer's hot/cold feature and is often used to find hot blocks

Buffer Pools

Object Name	Notes
X$KCBWBPD	SELECT inst_id, con_id, bp_name FROM x$kcbwbpd; INST_ID CON_ID BP_NAME ---------- ---------- -------- 1 0 1 0 KEEP 1 0 RECYCLE 1 0 DEFAULT 1 0 DEFAULT 1 0 DEFAULT 1 0 DEFAULT 1 0 DEFAULT 1 0 DEFAULT 1 0 DEFAULT

Buffer Working Data Sets

Object Name	Notes
X$KCBWDS	36 rows

Checkpoint Process

Object Name	Notes
X$ACTIVECKPT	9 rows
X$CKPTBUF	3208 rows

Child Cursors

Object Name Notes

X$KGLCURSOR_CHILD col kglfnobj format a60 SELECT kglfnobj, DECODE(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN') FROM x$kglcursor_child WHERE rownum < 6; KGLFNOBJ DECODE(KGL ------------------------------------------------------------ ---------- SELECT COUNT(*) FROM X$LOGMNR_KOPM$ ALL_ROWS SELECT COUNT(*) FROM X$KQFSZ ALL_ROWS SELECT COUNT(*) FROM V$PROXY_COPY_SUMMARY ALL_ROWS SELECT NVL(TO_NUMBER(EXTRACT(XMLTYPE(:B2 ), :B1 )), 0) FROM ALL_ROWS DUAL select inst_id,hxfil, decode(hxerr, 0,decode(bitand(fhsta, 1 CHOOSE ), 0,'NOT ACTIVE','ACTIVE'), 1,'FILE MISSING', 2,'OFFLINE NO RMAL', 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), to_date(fhbti,'MM/DD /RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), con_id from x$kcv fhonl

Compilation Layer

Object Name	Notes
X$KKAEET	11 rows
X$KKCNEREG	0 rows
X$KKCNEREGSTAT	0 rows
X$KKCNRSTAT	all raw/number/timestamp - 4 rows
X$KKKICR	1 row
X$KKOAR_HINT	2 rows
X$KKOCS_HISTOGRAM	252 rows
X$KKOCS_SELECTIVITY	0 rows
X$KKOCS_STATISTICS	0 rows
X$KKSAI	0 rows
X$KKSBV	Cursor Cache Bind Variables - 4031 rows
X$KKSCS	4425 rows
X$KKSPCIB	6313 rows
X$KKSSQLSTAT	SQL plan information- 5789 rows
X$KKSSQLSTAT_PLAN_HASH	SQL plan information- 5780 rows
X$KKSSRD	4441 rows
X$KKSXSQLSTAT	0 rows

Control Files

Object Name	Notes
X$KCCCF	`SELECT cfnam FROM x$kcccf; CFNAM ------------------------------------------------------------ u21/app/oracle/orabase/oradata/orabase/control01.ctl u21/app/oracle/orabase/recovery_area/orabase/control02.ctl`

Database Version

Object Name Notes

X$VERSION col banner format a20 col banner_full format a20 col banner_legacy format a20 SELECT banner, banner_full, banner_legacy FROM x$version; BANNER BANNER_FULL BANNER_LEGACY -------------------- -------------------- -------------------- Oracle Database 21c Oracle Database 21c Oracle Database 21c Enterprise Edition R Enterprise Edition R Enterprise Edition R elease 21.0.0.0.0 - elease 21.0.0.0.0 - elease 21.0.0.0.0 - Production Production Production Version 21.3.0.0.0

Data Files

Object Name	Notes
X$KCVDF Datafile header information	SELECT df_fhfno, df_fhtnm, con_id FROM x$kcvdf; DF_FHFNO DF_FHTNM CON_ID ---------- --------- ------ 1 SYSTEM 1 3 SYSAUX 1 4 UNDOTBS1 1 5 SYSTEM 2 6 SYSAUX 2 7 USERS 1 8 UNDOTBS1 2 9 SYSTEM 3 10 SYSAUX 3 11 UNDOTBS1 3 12 USERS 3 13 UWDATA 3 14 UWDATA 1
X$KCVFH	K = kernel, C = cache layer, V = recoVery Component, FH = File Header The following query will tell you if recovery is complete: col file# format 99 col status format 9999 col scn format a8 col sequence format 999 col datafile_name format a62 SELECT hxfil FILE#, fhscn SCN, hxfnm DATAFILE_NAME FROM x$kcvfh ORDER BY 1; FILE# SCN DATAFILE_NAME ----- -------- -------------------------------------------------------- 1 3959104 u21/app/oracle/orabase/oradata/orabase/SYSTEM01.DBF 3 3959104 u21/app/oracle/orabase/oradata/orabase/SYSAUX01.DBF 4 3959104 u21/app/oracle/orabase/oradata/orabase/UNDOTBS01.DBF 5 2796306 u21/app/oracle/orabase/oradata/orabase/PDBSEED\SYSTEM01.DBF 6 2796306 u21/app/oracle/orabase/oradata/orabase/PDBSEED\SYSAUX01.DBF 7 3959104 u21/app/oracle/orabase/oradata/orabase/USERS01.DBF 8 2796306 u21/app/oracle/orabase/oradata/orabase/UNDOTBS01.DBF 9 3959104 u21/app/oracle/orabase/oradata/orabase/PDBDEV\SYSTEM01.DBF 10 3959104 u21/app/oracle/orabase/oradata/orabase/SYSAUX01.DBF 11 3959104 u21/app/oracle/orabase/oradata/orabase/PDBDEV\UNDOTBS01.DBF 12 3959104 u21/app/oracle/orabase/oradata/orabase/PDBDEV\USERS01.DBF 13 3959104 u21/app/oracle/orabase/oradata/orabase/PDBDEV\UWDATA01.DBF
X$KCVFHTMP	3 rows

Data Guard

Object Name	Notes
X$DRC	Data Guard Broker, 0 rows

Distributed Execution Layer - 2PC handling

Object Name	Notes
X$K2GTE	kernel 2-phase commit, global transaction entry. Find sessions coming from or going to a remote database SELECT /+ ordered / SUBSTR(s.ksusemnm,1,10)\|\|'-'\|\| SUBSTR(s.ksusepid,1,10) origin, SUBSTR(g.k2gtitid_ora,1,35) gtxid, SUBSTR(s.indx,1,4)\|\|'.'\|\| SUBSTR(s.ksuseser,1,5) lsession, s.ksuudlna username, SUBSTR( DECODE(BITAND(ksuseidl,11), 1,'ACTIVE', 0, DECODE(BITAND(ksuseflg,4096), 0,'INACTIVE','CACHED'), 2,'SNIPED', 3,'SNIPED', 'KILLED'),1,1) status, e.kslednam waiting FROM x$k2gte g, x$ktcxb t, x$ksuse s, x$ksled e WHERE g.k2gtdxcb=t.ktcxbxba AND g.k2gtdses = t.ktcxbses AND s.addr = g.k2gtdses AND e.indx=s.ksuseopc; x$k2gte.k2gtdses matches v$session.saddr k2gtdxcb matches v$transaction.addr. It's better than checking for DX locks for outgoing sessions (since a DX lock only shows up in v$lock for the current distributed transaction session). x$k2gte2 is the same as x$k2gte except on k2gtetyp which may show 2 for 'TIGHTLY COUPLED' instead of 0 for 'FREE'. One use of x$k2gte[2] is the clearly translated global transaction ID in k2gtitid_ora as opposed to the hex numbers in v$global_transaction.globalid.
X$K2GTE2	kernel 2-phase commit, global transaction entry: See above entry. 0 rows

DNFS

Object Name	Notes
X$DNFS_CHANNELS	0 rows
X$DNFS_FILES	0 rows
X$DNFS_HIST	0 rows
X$DNFS_META	1 rows
X$DNFS_SERVERS	0 rows
X$DNFS_STATS	90 rows

DUAL

Object Name	Notes
X$DUAL	`SELECT * FROM x$dual; ADDR INDX INST_ID CON_ID D ---------------- ---- ------- ------ - 00007FF79B21A43C 0 1 0 X SQL> select * from dual; DUMMY ----- X`

Exadata Cells

Object Name	Notes
X$CELL_NAME	Cell names, 0 rows
X$KCFISOSS	Stat types, 0 rows
X$KCFISOSSN	Waits, 0 rows
X$KCFISOSST	Snapshots, 0 rows

Explain Plan Lookup Table

Object Name	Notes
X$XPLTON	-- explain plan operation types lookup table `SELECT xplton_name FROM x$xplton ORDER BY 1; 149 rows`
X$XPLTOO	-- explain plan lookup table `SELECT xpltoo_name FROM x$xpltoo ORDER BY 1; 259 rows`

Heat Maps

Object Name	Notes
X$HEATMAPSEGMENT	0 rows
X$HEATMAPSEGMENT1	0 rows

Heterogeneous Services

Object Name	Notes
X$HS_SESSION	Heterogeneous services data base link users, hosts, and SIDs, 0 rows

In-Memory

Object Name	Notes
X$IMCSEGEXTMAP	0 rows
X$IMCSEGMENTS	0 rows
X$IMCTBSEXTMAP	0 rows

Incident Repair

Object Name	Notes
X$IR_MANUAL_OPTION	0 rows
X$IR_REPAIR_OPTION	0 rows
X$IR_REPAIR_STEP	0 rows
X$IR_RS_PARAM	0 rows
X$IR_WF_PARAM	0 rows
X$IR_WORKING_FAILURE_SET	0 rows
X$IR_WORKING_REPAIR_SET	0 rows
X$IR_WR_PARAM	0 rows

Kernel Cache

Object Name	Notes
X$KCBBES	19 rows
X$KCBBF	According to Jonathan Lewis the number of available buffer handles set at instance start-up, and likely dependent on the number of processes, apparently 5 * processes, although that value five may be related to the fact that the default value for _db_handles_cached is five. These handles appear as the structure x$kcbbf. `SQL> show parameter processes SELECT COUNT() FROM x$kcbbf; COUNT() --------- 3200`
X$KCBBHS	0 rows
X$KCBDBK	Encrypted key, 3 rows
X$KCBDWOBJ	0 rows
X$KCBDWS	1 row
X$KCBFCIO	0 rows
X$KCBFWAIT	Kernel Cache Buffer File Wait used to break down the contents of v$waitstat into per-datafile statistics SELECT name, count, time FROM v$datafile df, x$kcbfwait fw WHERE fw.indx+1 = df.file#; NAME COUNT TIME ------------------------------------------------------------ ----- ---- u21/app/oracle/orabase/oradata/orabase/SYSTEM01.DBF 156 6 u21/app/oracle/orabase/oradata/orabase/SYSAUX01.DBF 6 0 u21/app/oracle/orabase/oradata/orabase/UNDOTBS01.DBF 36 0 u21/app/oracle/orabase/oradata/orabase/PDBSEED\SYSTEM01.DBF 0 0 u21/app/oracle/orabase/oradata/orabase/PDBSEED\SYSAUX01.DBF 0 0 u21/app/oracle/orabase/oradata/orabase/USERS01.DBF 0 0 u21/app/oracle/orabase/oradata/orabase/PDBSEED\UNDOTBS01.DBF 0 0 u21/app/oracle/orabase/oradata/orabase/PDBDEV\SYSTEM01.DBF 1 0 u21/app/oracle/orabase/oradata/orabase/PDBDEV\SYSAUX01.DBF 1 0 u21/app/oracle/orabase/oradata/orabase/PDBDEV\UNDOTBS01.DBF 2 0 u21/app/oracle/orabase/oradata/orabase/PDBDEV\USERS01.DBF 0 0 u21/app/oracle/orabase/oradata/orabase/PDBDEV\UWDATA01.DBF 0 0 u21/app/oracle/orabase/oradata/orabase/UWDATA01.DBF 0 0
X$KCBINSTVIEW	Kernel Cache Buffer `Instance View col caching_status format a22 SELECT * FROM x$kcbinstview; ADDR INDX INST_ID CON_ID CACHING_STATUS ---------------- ---- ------- ------ --------------------- 0000026C38331A00 0 1 0 FULL CACHING DISABLED`
X$KCBKPFS	Kernel Cache Buffer cKpt PreFetch Statistics. Used by the CKPT process to store its wasted prefetch block statistics history. [Link] 450 rows
X$KCBKWRL	1 row
X$KCBLDRHIST	1000 rows
X$KCBLSC	64 rows
X$KCBMKID	3 rows
X$KCBMMAV	0 rows
X$KCBOBH	Kernel Cache Buffer Object queue Buffer Header. Contains one row for each buffers in the KCB object queue. [Link] 96761 rows
X$KCBOQH	Kernel Cache Buffer Object Queue Header, 4950 rows
X$KCBPDBRM	0 rows
X$KCBPINTIME	2048 rows
X$KCBPRFH	0 rows
X$KCBSDS	21 rows
X$KCBSW	kernel cache waits, 1707 rows
X$KCBTEK	16 rows
X$KCBUWHY	1707 rows
X$KCBWAIT	kernel cache, block wait, 57 rows
X$KCBWH	1707 rows
X$KCCACM	11 rows
X$KCCADFC	0 rows
X$KCCAGF	3 rows
X$KCCBF	6 rows
X$KCCBL	0 rows
X$KCCBLKCOR	0 rows
X$KCCBP	6 rows
X$KCCBS	6 rows
X$KCCCC	0 rows
X$KCCCP	kernel cache, controlfile checkpoint progress, 8 rows
X$KCCDFHIST	0 rows
X$KCCDI	1 row
X$KCCDI2	1 row
X$KCCDL	39 rows
X$KCCFC	0 rows
X$KCCFE	19 rows
X$KCCFLE	0 rows
X$KCCIC	2 rows
X$KCCIRT	1 row
X$KCCLE	3 rows
X$KCCLH	38 rows
X$KCCNRS	0 rows
X$KCCOR	32 rows
X$KCCPA	0 rows
X$KCCPD	0 rows
X$KCCPDB	3 rows
X$KCCPIC	0 rows
X$KCCRDI	1 row
X$KCCRL	0 rows
X$KCCRM	0 rows
X$KCCRS	43 rows
X$KCCRSP	0 rows
X$KCCRSR	SELECT rsrci, rsrop, rsrst, rsret FROM x$kccrsr; RSRCI RSROP RSRST RSRET -------------------- ------- -------------------- -------------------- 2014-12-04T22:10:47 RMAN 12/04/2014 22:10:48 12/04/2014 22:11:15 PDB$SEED restore 12/04/2014 22:10:49 12/04/2014 22:11:15 2014-12-04T22:11:15 RMAN 12/04/2014 22:11:16 12/04/2014 22:11:17 2014-12-04T22:19:36 RMAN 12/04/2014 22:19:38 12/04/2014 22:20:16 pdbdev restore 12/04/2014 22:19:39 12/04/2014 22:20:16 2014-12-04T22:20:16 RMAN 12/04/2014 22:20:17 12/04/2014 22:20:18 2015-01-24T19:24:43 RMAN 01/24/2015 19:24:44 01/24/2015 19:24:44 2015-01-24T19:27:30 RMAN 01/24/2015 19:27:31 01/24/2015 19:27:38 2015-01-24T19:28:00 RMAN 01/24/2015 19:28:00 01/24/2015 19:28:07 2015-01-24T19:36:57 RMAN 01/24/2015 19:36:58 01/24/2015 19:36:58 2015-01-24T19:38:03 RMAN 01/24/2015 19:38:03 01/24/2015 19:42:25 2015-01-24T19:42:51 RMAN 01/24/2015 19:42:52 01/24/2015 19:44:19 2015-01-24T19:45:03 RMAN 01/24/2015 19:45:04 01/24/2015 19:47:34 2015-01-24T19:45:03 report 01/24/2015 19:46:52 01/24/2015 19:46:54 2015-02-11T12:36:30 RMAN 02/11/2015 12:36:31 02/11/2015 12:37:05
X$KCCRT	1 row
X$KCCSL	0 rows
X$KCCTF	3 rows
X$KCCTIR	8 rows
X$KCCTKH	0 rows
X$KCFIO	kernel cache, file I/O, 200 rows
X$KCFIOFCHIST	0 rows
X$KCFIOHIST	12 rows
X$KCFISCAP	15 rows
X$KCFISOSSAWR	0 rows
X$KCFISOSSC	0 rows
X$KCFISOSSL	0 rows
X$KCFISTCAP	0 rows
X$KCFISTSA	6 rows
X$KCFTIO	200 rows
X$KCLCRST	1 row
X$KCLCURST	1 row
X$KCLFX	0 rows
X$KCLLS	0 rows
X$KCLPINGPIN	2048 rows - pings
X$KCLPINGWEV	2048 rows - pings
X$KCLRCVST	1 row
X$KCMSCN	1 row
X$KCNT	0 rows
X$KCPDBINC	6 rows
X$KCPXPL	SELECT platform_id, endian_format, platform_name FROM x$kcpxpl ORDER BY 1; PLATFORM_ID ENDIAN_FORMAT PLATFORM_NAME ----------- ------------- ---------------------------------- 1 1 Solaris[tm] OE (32-bit) 2 1 Solaris[tm] OE (64-bit) 3 1 HP-UX (64-bit) 4 1 HP-UX IA (64-bit) 5 0 HP Tru64 UNIX 6 1 AIX-Based Systems (64-bit) 7 0 Microsoft Windows IA (32-bit) 8 0 Microsoft Windows IA (64-bit) 9 1 IBM zSeries Based Linux 10 0 Linux IA (32-bit) 11 0 Linux IA (64-bit) 12 0 Microsoft Windows x86 64-bit 13 0 Linux x86 64-bit 15 0 HP Open VMS 16 1 Apple Mac OS 17 0 Solaris Operating System (x86) 18 1 IBM Power Based Linux 19 0 HP IA Open VMS 20 0 Solaris Operating System (x86-64) 21 0 Apple Mac OS (x86-64) 22 1 Linux OS (S64)
X$KCRFDEBUG	1 row
X$KCRFWS	1 row
X$KCRFX	0 rows
X$KCRMF	0 rows
X$KCRMT	0 rows
X$KCRMX	0 rows
X$KCRRARCH	30 rows
X$KCRRASTATS	31 rows
X$KCRRDSTAT	31 rows
X$KCRRLNS	0 rows
X$KCRRNHG	3050 rows
X$KCTICW	1 row
X$KCTLAX	0 rows
X$KCVFHALL	13 rows
X$KCVFHMRR	0 rows

Kernel Data Layer

Object Name	Notes
X$KDKVIMHASHINDEX	0 rows
X$KDKVIMHASHINDEX_OBJECTS	0 rows
X$KDLT	1 row
X$KDLU_STAT	273 rows
X$KDMADOELEMENTS	0 rows
X$KDMADOTASKDETAILS	0 rows
X$KDMADOTASKS	0 rows
X$KDMIMCCOL	0 rows
X$KDMIMCHEAD	0 rows
X$KDMIMEUCOL	0 rows
X$KDMIMEUHEAD	0 rows
X$KDMUFASTSTARTAREA	3 rows
X$KDMUSEGDICT	0 rows
X$KDMUSEGDICTPMAP	0 rows
X$KDMUSEGDICTSO	0 rows
X$KDMUSEGDICTVER	0 rows
X$KDNSSF	504 rows
X$KDXHS	Histograms, 16 rows
X$KDXST	Stats collection, 0 rows
X$KDZCOLCL	In-Memory Compression, 0 rows
X$KD_COLUMN_STATISTICS	0 rows

Kernel eXecution Layer

Object Name	Notes
X$KXDBIO_STATS	0 rows
X$KXDCM_DB	0 rows
X$KXDCM_DISK	0 rows
X$KXDCM_DISK_HISTORY	0 rows
X$KXDCM_GLOBAL	0 rows
X$KXDCM_GLOBAL_HISTORY	0 rows
X$KXDCM_IOREASON	0 rows
X$KXDCM_IOREASON_NAME	SELECT reason_name FROM x$kxdcm_ioreason_name ORDER BY 1; REASON_NAME ----------------------------------- AKM AutoLogin KeyStore I/O AKM KeyStore I/O AM Container I/O ASM Block Format ASM CACHE IO ASM Cache Cleanup IO ASM Cache ping ASM Checkpoint IO ASM Disk Header IO ASM Extent Repair I/O ASM Filter Driver I/O ASM Fixed Package IO ASM Heartbeat IO ASM PST IO ASM REDO IO ASM Recovery IO ASM Relocate IO ASM Replacement IO ASM Stale File ASM User File Relocate ASM scrub IO ASM volume file I/O Calibration data read Cell IMC population I/O Cell internal I/O Clear ORL/SRL write Data Guard I/O I/O to Oracle parameter file I/O to Oracle passwd file IDR I/O error check Internal IO NBR recovery write OSD header I/O Other PQ induced IO REQ list writes Voting file I/O aged writes by dbwr archive log read for recovery archive log write audit file I/O backup I/O block dump block media recovery write buffer cache reads change tracking I/O cross platform I/O data file reads to private memory data file writes to private memory data header read data header write data pump I/O database control file read database control file write dbwr instance recovery writes dbwr media recovery writes fast file create file format writes file transfer IO fixedpkg hard I/O flashback log read flashback log write high-priority checkpoint write incremental apply I/O invalid IO limit dirty buffer writes low priority checkpoint writes medium-priority checkpoint writes misc I/O online move datafile I/O redo log HWM update redo log file rdma write redo log read redo log read for archiving redo log seqno read redo log write reread of a block on error resilvering write reuse block range writes by dbwr reuse object writes by dbwr self tune checkpoint writes smart backup smart scan table space checkpoint writes trace file write xrov I/O
X$KXDCM_METRIC_DESC	327 rows
X$KXDCM_OPEN_ALERTS	0 rows
X$KXDRS	0 rows
X$KXDSPPHYSMP	0 rows
X$KXFBBOX	458 rows
X$KXFPBS	5 rows
X$KXFPCDS	Client Dequeue Statistics, 38 rows
X$KXFPCMS	Client Messages Statistics, 71 rows
X$KXFPCST	col value format 99999999999 SELECT statistic, value FROM x$kxfpcst ORDER BY 1; STATISTIC VALUE ------------------------------ ------------ Data Messages Dequeued 10591 Data Messages Sent 10606 Dequeues for Credit (enq) 523 Dequeues for Credit (free) 0 Dequeues for Credit (geb) 2 Dialog Messages Sent 11591 Double Credit Pings 128 Fast Distributed Streams 0 Fast Shared Memory Streams 21 Immediate Dequeues 26400 Implicit Dequeues 26590 Multiple Credit Pings 502 Null Messages Dequeued 86468 Null Messages Sent 113112 Posted Dequeues 67565 Query Sessions 1770 Single Credit Pings 808 Stream Messages Sent 34 Stream Mode Credit Pings 25 Timed-out Dequeues 9441 Total Dequeue Timeouts 691743 Total Dequeue Waits 759035 Total Messages Dequeued 97059 Total Messages Sent 123718 Triple Credit Pings 29 Unknown Credit Pings 113087
X$KXFPDP	8 rows
X$KXFPIG	0 rows
X$KXFPNS	SELECT kxfpnsnam, kxfpnsval FROM x$kxfpns ORDER BY 1; KXFPNSNAM KXFPNSVAL ------------------------- ---------- Buffers Allocated 7307 Buffers Current 0 Buffers Current Free 28 Buffers Current Total 28 Buffers Freed 7307 Buffers HWM 55 Chunk Lists 4 Estimated Buffers HWM 45 Estimated Buffers Max 5040 Memory Chunks Allocated 182 Memory Chunks Current 4 Memory Chunks Freed 178 Memory Chunks HWM 9 Memory Chunks Permanent 4 Server Sessions 1303 Servers Available 8 Servers Cleaned Up 0 Servers Highwater 6 Servers In Use 0 Servers Max 80 Servers Shutdown 22 Servers Started 30
X$KXFPPFT	100 rows
X$KXFPPIG	0 rows
X$KXFPREMINSTLOAD	0 rows
X$KXFPSDS	38 rows
X$KXFPSMS	71 rows
X$KXFPSST	SELECT kxfpssnam FROM x$kxfpsst ORDER BY 1; KXFPSSNAM --------------------- Allocation Height Allocation Width DDL Parallelized DFO Trees DML Parallelized DOP Distr Msgs Recv'd Distr Msgs Sent Local Msgs Recv'd Local Msgs Sent Queries Parallelized Server Threads Slave Sets
X$KXFPYS	SELECT kxfpysnam, kxffpyval FROM x$kxfpys ORDER BY 1; KXFPYSNAM KXFPYSVAL ------------------------ ---------- DDL Initiated 0 DDL Initiated (IPQ) 0 DFO Trees 458 DML Initiated 0 DML Initiated (IPQ) 0 Distr Msgs Recv'd 0 Distr Msgs Sent 0 Local Msgs Recv'd 9045 Local Msgs Sent 9045 Queries Initiated 6 Queries Initiated (IPQ) 0 Queries Queued 0 Server Sessions 1281 Servers Busy 0 Servers Cleaned Up 0 Servers Highwater 6 Servers Idle 8 Servers Shutdown 22 Servers Started 30 Sessions Active 0
X$KXFQSROW	0 rows
X$KXFRSVCHASH	0 rows
X$KXFSOURCE	0 rows
X$KXFTASK	0 rows
X$KXSBD	5 rows
X$KXSCC	64 rows
X$KXSREPLAY	0 rows
X$KXSREPLAYDATE	0 rows
X$KXSREPLAYGUID	0 rows
X$KXSREPLAYLOB	0 rows
X$KXSREPLAYSEQ	0 rows
X$KXSREPLAYTIME	0 rows
X$KXTTSTECS	0 rows
X$KXTTSTEHS	0 rows
X$KXTTSTEIS	0 rows
X$KXTTSTETS	0 rows
X$KXTTSTETS	0 rows

Kernel Generic Heap

Object Name	Notes
X$KGHLU	kernel generic, heap LRUs: A one-row summary of LRU statistics for the shared pool, 1 row
X$KGICS	1 row
X$KGLAU	1539 rows
X$KGLBODY	Derived from X$KGLOB (col kglhdnsp = 2), 208 rows
X$KGLCLUSTER	Derived from X$KGLOB (col kglhdnsp = 5), 27 rows
X$KGLCURSOR	19982 rows
X$KGLCURSOR_CHILD_SQLID	4382 rows
X$KGLCURSOR_CHILD_SQLIDPH	4372 rows
X$KGLCURSOR_PARENT	13904 rows
X$KGLDP	13276 rows
X$KGLINDEX	Derived from X$KGLOB (col kglhdnsp = 4), 156 rows
X$KGLJMEM	0 rows
X$KGLJSIM	0 rows
X$KGLLK	kernel generic, library cache lock, 1403 rows
X$KGLMEM	143 rows
X$KGLNA	30427 rows
X$KGLNA1	30427 rows
X$KGLOB	kernel generic, library cache object. A direct dependency for DBMS_ALERT, 34838 rows
X$KGLOBXML	34838 rows
X$KGLPN	kernel generic, library cache pin, 1 row
X$KGLRD	13251 row
X$KGLSIM	24 rows
X$KGLSN	32 rows
X$KGLSQLTXL	0 rows
X$KGLST	kernel generic, library cache status, 302 rows
X$KGLTABLE	Derived from X$KGLOB (col kglhdnsp = 1), 4457 rows
X$KGLTR	Library Cache Translation Table entry, 4915 rows
X$KGLTRIGGER	Derived from X$KGLOB (col kglhdnsp = 3), 9 rows
X$KGLXS	Library Cache Access, 7419 rows
X$KGSCC	1 row
X$KGSKASP	Active Session Pool, 1 row
X$KGSKCFT	10 rows
X$KGSKCP	2 rows
X$KGSKDOPP	SELECT policy_name_kgskdopp, attributes_kgskdopp FROM x$kgskdopp; POLICY_NAME_KGSKDOPP ATTRIBUTES_KGSKDOPP ------------------------------- ------------------- PARALLEL_DEGREE_LIMIT_ABSOLUTE 0
X$KGSKMEMINFO	3 rows
X$KGSKNCFT	10 rows
X$KGSKPDBFT	3 rows
X$KGSKPDBHISTFT	12 rows
X$KGSKPFT	3 rows
X$KGSKPP	2 rows
X$KGSKQUEP	SELECT policy_name_kgskquep, attributes_kgskquep FROM x$kgskquep; POLICY_NAME_KGSKQUEP ATTRIBUTES_KGSKQUEP --------------------- ------------------- FIFO_TIMEOUT 0
X$KGSKSCS	28 rows
X$KGSKTE	0 rows
X$KGSKTO	0 rows
X$KGSKVFT	51 rows

Kernel Lock Manager

Object Name	Notes
X$KJAC_CONFIG	1 row
X$KJAC_ID	0 rows
X$KJAC_MY_ID	0 rows
X$KJBFPBR	0 rows
X$KJBL	0 rows
X$KJBLFX	0 rows
X$KJBR	0 rows
X$KJBRFX	0 rows
X$KJCISOT	29 rows
X$KJCISPT	5 rows
X$KJCTFR	0 rows
X$KJCTFRI	0 rows
X$KJCTFS	0 rows
X$KJDDDEADLOCKS	0 rows
X$KJDDDEADLOCKSES	0 rows
X$KJDRBH	0 rows
X$KJDRHV	0 rows
X$KJDRMAFNSTATS	1 row
X$KJDRMHVSTATS	1 row
X$KJDRMREADMOSTLYSTATS	1 row
X$KJDRMREQ	0 rows
X$KJDRPCMHV	0 rows
X$KJDRPCMPF	0 rows
X$KJFMHBACL	0 rows
X$KJICVT	0 rows
X$KJIDT	1 row very slow to load
X$KJILFT	0 rows
X$KJILKFT	0 rows
X$KJIRFT	0 rows
X$KJISFT	0 rows
X$KJITRFT	0 rows
X$KJKMKGA	0 rows - very interesting
X$KJKMLOCK	0 rows
X$KJKMREQ	0 rows
X$KJLEQFP	0 rows
X$KJMDDP	0 rows
X$KJMSDP	0 rows
X$KJPMPRC	0 rows
X$KJPNPX	0 rows
X$KJREQFP	0 rows
X$KJRTBCFP	0 rows
X$KJR_CHUNK_STATS	0 rows
X$KJR_FREEABLE_CHUNKS	0 rows
X$KJSCAPKAT	0 rows
X$KJSCASVCAT	0 rows
X$KJXGNA_STATS	0 rows
X$KJXM	0 rows
X$KJZNCBHANGS	0 rows
X$KJZNHANGS	0 rows
X$KJZNHANGSES	0 rows
X$KJZNHMLSI	504 rows
X$KJZNHNGMGRSTS	1 row
X$KJZNHNGSTATS	94 rows
X$KJZNRSLNRC	52 rows
X$KJZNWLMPCRANK	SELECT kjznwlmpcrankdesc, kjznwlmpcrankshrtdesc FROM x$kjznwlmpcrank ORDER BY 1; KJZNWLMPCRAN KJZNWLMP ------------ -------- HIGH HIGH HIGHEST HIGHEST LOW LOW LOWEST LOWEST MEASURE ONLY PCMONLY MEDIUM MEDIUM NOT MANAGED PCNMNGD UNDEFINED UNDEF UNKNOWN UNKNOWN
X$KJZSIWTEVT	1919 rows

Kernel Query

Object Name	Notes
X$KQDPG	kernel query, fixed table columns, 1 row
X$KQFCO	22037 rows
X$KQFDT	kernel query, fixed tables, 43 rows
X$KQFOPT	159 rows
X$KQFP	kernel query, fixed procedures, 41 rows
X$KQFSZ	kernel query, fixed size, 45 rows
X$KQFTA	Kernel Query, Fixed Table: Base table of v$fixed_table, whose object_id (indx of x$kqfta) matches obj# of tab_stats$, the table dbms_stats.gather_fixed_objects_stats inserts stats into 1335 rows
X$KQFTVRTTST0	1 row
X$KQFVI	Kernel Query Fixed View: V$ and GV$ dynamic performance views. 1520 rows
X$KQFVT	For each Fixed View this object holds the DDL metadata that creates it from its corresponding X$ object(s) 1520 rows SELECT kqftpsel FROM x$kqfvt WHERE rownum = 1; KQFTPSEL ----------------------------------------------------------------------------- select inst_id,decode(mod(indx,19),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,time, con_id from x$kcbwait where mod(indx,19)!=0
X$KQLFBC	2889 rows
X$KQLFSQCE	2803126 rows
X$KQLFXPL	22074 rows
X$KQLSET	24381 rows
X$KQPXINV	1 row
X$KQRFP	94663 rows
X$KQRFS	2983 rows
X$KQRPD	Kernel Query, Rowcache Parent Definition: Column kqrpdosz is the size of the parent rowcache object, not exposed in v$rowcache_parent although shown in rowcache dump 65 rows
X$KQRSD	Kernel Query, Rowcache Subordinate Definition: Column kqrsdosz is the size of the subordinate rowcache object, not exposed in v$rowcache_subordinate although shown in rowcache dump. 14 rows
X$KQRST	75 rows

Kernel Security

Object Name	Notes
X$KZAHIST	0 rows
X$KZAJOBS	0 rows
X$KZALOADJOBS	0 rows
X$KZAPARAMS	0 rows
X$KZATS	0 rows
X$KZCKMCS	0 rows
X$KZCKMEK	0 rows
X$KZDOS	Represent an OS role as defined by the operating system, 0 rows
X$KZDPSUPSF	col kzdpsupsfnm format a28 col kzdpsupsffn format a24 col kzdpsupsfcom format a28 SELECT kzdpsupsfnm, kzdpsupsffn, kzdpsupsfcom FROM x$kzdpsupsf ORDER BY 1; KZDPSUPSFNM KZDPSUPSFFN KZDPSUPSFCOM ---------------------------- ------------------------ ---------------------------- DATA REDACTION ALL Supports all data redaction functionality (DBMS_REDACT). FINE GRAINED AUDIT ALL Supports all fine grained au dit functionality (DBMS_FGA) TRANSPARENT DATA ENCRYPTION COLUMN-LEVEL ENCRYPTION Supports TDE Column level en cryption. UNIFIED AUDIT OBJECT-LEVEL POLICY Supports object-level Unifie d Audit policies. VIRTUAL PRIVATE DATABASE OBJECT-LEVEL POLICY Supports object-level VPD po licies. VIRTUAL PRIVATE DATABASE COLUMN-LEVEL POLICY Supports column-level VPD po licies. This corresponds to the SEC_RELEVANT_COL paramet er functionality provided by DBMS_RLS.ADD_POLICY.
X$KZEKMENCWAL	`Wallets, their location, type and status col wrl_type format a8 col wrl_parameter format a32 col wallet_type format a11 SELECT wrl_type, wrl_parameter, status, wallet_type, wallet_order FROM x$kzekmencwal; WRL_TYPE WRL_PARAMETER WALLET_TYPE -------- ---------------------------------------- ----------- FILE C:\U01\ORABASE19\ADMIN\ORABASEXIX\WALLET UNKNOWN FILE 64 UNKNOWN FILE 64 UNKNOWN`
X$KZEKMFVW	0 rows
X$KZPOPR	-- appears to be an intersection between object privileges and object_types 112 rows `SELECT privilege_name, object_type_name FROM x$kzpopr;`
X$KZRTPD	0 rows
X$KZSPR	409 rows
X$KZSRO	Kernel Security System ROle `SELECT * FROM x$kzsro; ADDR INDX INST_ID CON_ID KZSROROL ---------------- ---------- ---------- ---------- ---------- 0000000000000000 0 1 0 1 0000000000000008 1 1 0 0`
X$KZSRPWFILE	1 row
X$KZSRT	4 rows
X$KZVDVCLAUSE	82 rows

Kernel Service

Object Name	Notes
X$KSACLTAB	0 rows
X$KSAST	320 rows
X$KSBDD	358 rows
X$KSBDP	389 rows
X$KSBDPNEEDED	1 row
X$KSBFT	72 rows
X$KSBSRVDT	86 rows col ksbsrvdfile format a16 col ksbsrvddesc format a47 SELECT ksbsrvdfile, ksbsrvddesc FROM x$ksbsrvdt ORDER BY 2,1; KSBSRVDFILE KSBSRVDDESC ---------------- ----------------------------------------------- kfn2.h ASM CF Connection Pool kfn2.h ASM CF Connection Pool 2 kfn2.h ASM CF Connection Pool 3 kfn2.h ASM CF Connection Pool 4 kfn2.h ASM Connection Pool kfn2.h ASM Connection Pool 2 kfn2.h ASM Connection Pool 3 kfn2.h ASM Connection Pool 4 kfrc2.h ASM Recovery Slave kffscrb2.h ASM Scrubbing Check Slave kffscrb2.h ASM Scrubbing Repair Slave kffscrb2.h ASM Scrubbing Verify Slave kfg2.h ASM disk expel slave kfdp2.h ASM kfdp Slave Blocking kfdp2.h ASM kfdp Slave Error Emulation kff2.h ASM kff Slave kfk2.h ASM kfk FD Cleanup Slave krbabr2.h Auto BMR Slave Pool kjxgr2.h CGS IMR Slave kecpp.h Capture Preprocessing Slave Process kmp.h Connection Broker kskslv.h DBRM Scheduler Slave Class kjsc2.h DLM Statistics Collection and Management Slave kjsc2.h DLM Statistics Collection and Management Slave ksddnfs2.h DNFS Dispatchers kupp.h Data Pump master process kupp.h Data Pump slave class kupp.h Data Pump worker process kmmcts.h Dispatchers kfn2.h FENC Connection Pool 5 kjgcr.h GCR Slaves (LMHB) kjbl.h GCS CR Slave kjm.h GCS RM Slave kjka.h GES LMA Slave kjm.h GES LMD Slave kcf.h I/O Calibration ksfv2.h I/O Slave kfias2.h IOServer IO worker kfias2.h IOServer global file identifier kfias2.h IOServer network process ksws2.h Java patching slave kkj.h Job queue slaves kcrf.h KCRF Slave Pool krdbc.h KRDBC Slave Pool ksfs2.h KSFS Worker Slave Class ksmh.h KSM SGA allocator class ksusehst.h Ksu session HiSTory slave ksu2.h Ksupetrm_uT calloc spin slave kcrfh.h Log Writer Slave krvx.h LogMiner Parallel Reader krvx.h LogMiner Parallel Reader krvx.h LogMiner Worker Process knah.h Logical Standby Apply knah.h Logical Standby Apply kebm2.h MMON slave class 1 kebm2.h MMON slave class 2 krp.h Media Recovery krpm.h Media Recovery knvmd2.h NVM Dispatcher kr_lost_write.h New Lost Write Slave Pool kxfp.h Parallel query slave kxfp.h Parallel query slave kmp.h Pooled server kwsbg.h QMON MS kwsbg.h QMON MS kesms.h RAT Masking Slave Process krso.h Redo Transport keswts.h SPA Exec Slaves kmmcts.h Shared servers knl.h Streams Apply knl.h Streams Apply knl.h Streams Capture knl.h Streams Capture ksb2.h Testing SRV process ksb2.h Testing SRV process2 ksb2.h Testing SRV process3 kfv2.h Volume IO kjm.h global cache service process ksv2.h ksv test class 3 ksv2.h ksv test osp slave class ksv2.h ksv test osp slave class ksv2.h ksv test slave class ksv2.h ksv test slave class ksucln0.h process cleanup slave ktsjcts.h space management slave pool
X$KSBTABACT	4460 rows
X$KSBUCPUWTM	0 rows
X$KSDAF	0 rows
X$KSDAFT	0 rows
X$KSDHNG_CACHE_HISTORY	20 rows
X$KSDHNG_CHAINS	1 row
X$KSDHNG_SESSION_BLOCKERS	0 rows
X$KSFDFTYP	37 rows
X$KSFDKLL	0 rows
X$KSFDSSCLONEINFO	0 rows
X$KSFDSTBLK	Block level stats, 0 rows
X$KSFDSTCG	84 rows
X$KSFDSTCMP	630 rows
X$KSFDSTFILE	58 rows
X$KSFDSTHIST	1 row
X$KSFDSTLL	12 rows
X$KSFDSTTHIST	0 rows
X$KSFMCOMPL	0 rows
X$KSFMELEM	0 rows
X$KSFMEXTELEM	0 rows
X$KSFMFILE	0 rows
X$KSFMFILEEXT	0 rows
X$KSFMIOST	0 rows
X$KSFMLIB	0 rows
X$KSFMSUBELEM	0 rows
X$KSFQDVNT	1 row
X$KSFQP	8 rows
X$KSFVQST	192 rows
X$KSFVSL	0 rows
X$KSFVSTA	32 rows
X$KSIMAT	6 rows
X$KSIMAV	0 rows
X$KSIMSI	0 rows
X$KSIMTSR	0 rows
X$KSIPCIP	0 rows
X$KSIPCIP_CI	0 rows
X$KSIPCIP_KGGPNP	0 rows
X$KSIPCIP_OSD	0 rows
X$KSIPC_INFO	Not queryable
X$KSIPC_PROC_STATS	Not queryable
X$KSIPC_SKGSNDOMAIN	0 rows
X$KSIRESTYP	292 rows
X$KSIRGD	0 rows
X$KSIRPINFO	0 rows
X$KSI_REUSE_STATS	466 rows
X$KSKCDBPLW	4 rows
X$KSKCGMETRIC	10 rows
X$KSKPDBMETRIC	3 rows
X$KSKPLW	8 rows
X$KSKQDFT	13 rows
X$KSKQVFT	0 rows
X$KSLCS	6552 rows
X$KSLECLASS	167 rows
X$KSLED	1918 rows
X$KSLEI	1918 rows
X$KSLEMAP	Kernel Service Latch Event Map: Maps events to a small number of useful classes like I/O waits, 1924 rows
X$KSLEPX	13 rows
X$KSLES	26612 rows
X$KSLHOT	10 rows
X$KSLLCLASS	8 rows
X$KSLLD	993 rows
X$KSLLTR	1031 rows
X$KSLLTR_CHILDREN	23160 rows
X$KSLLTR_OSP	10 rows
X$KSLLTR_PARENT	1031 rows
X$KSLLW	9736 rows
X$KSLPO	591 rows
X$KSLSCS	13 rows
X$KSLSESHIST	1211 rows
X$KSLSESHIST_MICRO	2565 rows
X$KSLSESOUT	0 rows
X$KSLWH	504 rows
X$KSLWSC	11152 rows
X$KSLWSC_OSP	252 rows
X$KSLWT	52 rows
X$KSMDD	448 rows
X$KSMDUT1	320 rows
X$KSMFS	Kernel Services: memory fixed SGA, 5 rows
X$KSMFSV	Kernel Services: memory fixed SGA vectors, 23075 rows
X$KSMGE	304 rows
X$KSMHP	0 rows
X$KSMJCH	0 rows
X$KSMJS	Kernel Services: memory java_pool summary, 4 rows
X$KSMLRU	Kernel Services: memory LRU (Least Recently Used) column definitions: ksmlrsiz - amount of contiguous memory allocated ksmlrnum - number of objects being displaced ksmlrhon - name of inbound object if pl/sql or cursor ksmlrohv - hash value of object loaded ksmlrses - saddr of the loading session SELECT ksmlrses, ksmlrsiz, ksmlrnum, ksmlrhon FROM x$ksmlru;KSMLRSES KSMLRSIZ KSMLRNUM KSMLRHON ---------------- -------- -------- ------------------------------- 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00 0 0 00007FFDFC731C48 143560 48 DBMS_STATS 00007FFDFC731C48 138288 8 SELECT /+ OPT_PARAM('_fix_c... 00007FFDFC8942E8 62256 560 DBMS_SQLTUNE_INTERNAL 00007FFDFC871438 32848 456 DBMS_HEAT_MAP_INTERNAL 00007FFDFC7204F0 32832 464 MERGE /+ dynamic_sampling(S... 00007FFDFC9C4B68 4272 8 create table "WRI$_RCS_278_1... 00007FFDFC7204F0 4224 8 DECLARE job BINARY_INTEGER :... 00007FFDFC871438 4216 16 CREATE GLOBAL TEMPORARY TABL...
X$KSMLS	Kernel Services: memory large_pool summary, 5 rows
X$KSMMEM	Kernel Services: memory, 1159657 rows
X$KSMMGAPID	0 rows
X$KSMMGASEG	0 rows
X$KSMNIM	0 rows
X$KSMNS	0 rows
X$KSMPDBCSD	0 rows
X$KSMPGDP	0 rows
X$KSMPGDST	0 rows
X$KSMPGDSTA	0 rows
X$KSMPGST	1920 rows
X$KSMPP	Kernel Services: memory process pool, 342 rows
X$KSMSD	Kernel Services: memory SGA definition, 4 rows
X$KSMSGMAP	0 rows
X$KSMSGMEM	13 rows
X$KSMSP	Kernel Services: memory shared pool, 302041 rows
X$KSMSPR	Kernel Services: memory shared pool reserved, 350 rows
X$KSMSP_DSNEW	1 row
X$KSMSP_NWEX	54 rows
X$KSMSS	Kernel Services: memory shared_pool summary, 1859 rows
X$KSMSSINFO	3 rows
X$KSMSST	0 rows
X$KSMSTRS	4 rows
X$KSMUP	Kernel Services: memory user pool, 2558 rows
X$KSMXMINFO	0 rows
X$KSNSOBJ	1 row
X$KSNSRESINFO	0 rows
X$KSOLSFTS	122370 rows
X$KSOLSSTAT	30 rows
X$KSOLTD	0 rows
X$KSO_PRESPAWN_POOL	0 rows
X$KSO_SCHED_DELAY_HISTORY	902 rows
X$KSPCS_CPU_STAT	0 rows
X$KSPPCV	5397 rows
X$KSPPCV2	5410 rows
X$KSPPI	5397 rows
X$KSPPO	172 rows
X$KSPPSCV	5454 rows
X$KSPPSV	5454 rows
X$KSPPSV2	5467 rows
X$KSPRSTV	5454 rows
X$KSPRSTV2	5454 rows
X$KSPSPFH	1 row
X$KSPSPFILE	`col kspspfftctxspname format a34 col kspspfftctxspvalue format a44 col kspspfftctxspdvalue format a44 SELECT kspspfftctxspname, kspspfftctxspvalue, kspspfftctxspdvalue FROM x$kspspfile WHERE kspspfftctxspvalue IS NOT NULL ORDER BY 1;` 5398 rows
X$KSPTCH	0 rows
X$KSPVLD_VALUES	1041 rows
X$KSQDN	Global database name - 1 row `SELECT inst_id, con_id, ksqdngdn, ksqdngln, ksqdngun, ksqdngunid FROM x$ksqdn; INST_ID CON_ID KSQDNGDN KSQDNGLN KSQDN -------- ------- --------- --------- ----- 1 0 ORABASE2 ORABASE2 oraba col ksqdngln format a10 col ksqdngun format a10 INST_ID CON_ID KSQDNGDN KSQDNGLN KSQDNGUN KSQDNGUNID ------- ------ --------- --------- --------- ---------- 1 0 ORABASE2 ORABASE2 orabase2 3232374927`
X$KSQEQ	6064 rows
X$KSQEQTYP	292 rows
X$KSQRS	2448 rows
X$KSQST	Kernel Services: enqueue status, Gets and waits for different types of enqueues, 561 rows
X$KSRCCTX	173 rows
X$KSRCDES	173 rows
X$KSRCHDL	178 rows
X$KSRMPCTX	101 rows
X$KSRMSGDES	101 rows
X$KSRMSGO	8 rows
X$KSRPCIOS	3 rows
X$KSSQUAR	0 rows
X$KSSQUAR_REG	0 rows
X$KSSQUAR_SUM	3 rows
X$KSTEX	0 rows
X$KSUCF	Cost function for each Kernel Profile, 30 rows
X$KSUCLNDPCC	0 rows
X$KSUCLNPROC	2 rows
X$KSUCPUSTAT	col ksucpustatname format a23 SELECT ksucpustatname, ksucpustatvalue FROM x$ksucpustat ORDER BY 1; KSUCPUSTATNAME KSUCPUSTATVALUE ----------------------- --------------- AVG_BUSY_TIME 1978308 AVG_IDLE_TIME 14784579 AVG_SYS_TIME 993286 AVG_USER_TIME 964401 BUSY_TIME 8007198 IDLE_TIME 59234673 NUM_CPUS 4 NUM_CPU_CORES 2 NUM_CPU_SOCKETS 1 RSRC_MGR_CPU_WAIT_TIME 29 SYS_TIME 4066147 USER_TIME 3944700
X$KSUGBLNETSTAT	0 rows
X$KSUGBLNETSTATAWR	0 rows
X$KSUINSTSTAT	0 rows
X$KSULL	1 rows
X$KSULOP	Kernel Services: user long operation 115 rows
X$KSULV	Kernel Services: user locale value, 620 rows
X$KSUMYSTA	1974 rows
X$KSUNETSTAT	0 rows
X$KSUPDBSES	3 rows
X$KSUPGP	62 rows
X$KSUPGS	0 rows
X$KSUPL	Resource Limit for each Kernel Profile, 10 rows
X$KSUPR	Kernel Services: user process, 320 rows
X$KSUPRLAT	0 rows
X$KSURLMT	27 rows
X$KSURU	Resource Usage for each Kernel Profile, 5040 rows
X$KSUSD	2000 rows
X$KSUSE	See Demo At Page Bottom, 504 rows
X$KSUSECON	532 rows
X$KSUSECST	504 rows
X$KSUSESTA	994896 rows
X$KSUSEX	504 rows
X$KSUSGIF	1 row
X$KSUSGSTA	2000 rows
X$KSUSIO	504 rows
X$KSUSM	504 rows
X$KSUTM	1 row
X$KSUVMSTAT	4 rows
X$KSUXSINST	1 row
X$KSWSAFTAB	0 rows
X$KSWSASTAB	5 rows
X$KSWSCLSTAB	65 rows
X$KSWSCRSSVCTAB	0 rows
X$KSWSCRSTAB	0 rows
X$KSWSEVTAB	2640 rows
X$KSWSINTTAB	1 rows
X$KSWSJPOPERTAB	0 rows
X$KSWSJPTAB	1 rows
X$KSXAFA	16 rows
X$KSXMCOLST	0 rows
X$KSXMME	194 rows
X$KSXM_DFT	0 rows
X$KSXM_EXP_STATS	15 rows
X$KSXM_ZUT	0 rows
X$KSXPCLIENT	0 rows
X$KSXPIA	0 rows
X$KSXPIF	0 rows
X$KSXPPING	0 rows
X$KSXPTESTTBL	5 rows
X$KSXP_STATS	Not queryable
X$KSXRCH	0 rows
X$KSXRCONQ	0 rows
X$KSXRMSG	0 rows
X$KSXRREPQ	0 rows
X$KSXRSG	1 row

Kernel Transaction Layer

Object Name	Notes
X$KTADM	2211 rows
X$KTATL	6 rows
X$KTATRFIL	2 rows
X$KTATRFSL	2 rows
X$KTCNCLAUSES	0 rows
X$KTCNINBAND	0 rows
X$KTCNQROW	0 rows
X$KTCNQUERY	0 rows
X$KTCNREG	0 rows
X$KTCNREGQUERY	0 rows
X$KTCSP	0 rows
X$KTCXB	36 rows
X$KTFBFE	132 rows
X$KTFBHC	5 rows
X$KTFBNSTAT	1 row
X$KTFBUE	10150 rows
X$KTFSAN	3 rows
X$KTFSBI	0 rows
X$KTFSIMSTAT	0 rows
X$KTFSRI	0 rows
X$KTFSTAT	0 rows
X$KTFTBTXNGRAPH	0 rows
X$KTFTBTXNMODS	0 rows
X$KTFTHC	3 rows
X$KTFTME	0 rows
X$KTIFB	15 rows
X$KTIFF	21 rows
X$KTIFV	0 rows
X$KTMTXNCHUNK	0 rows
X$KTMTXNCRCLONE	0 rows
X$KTMTXNDELTA	0 rows
X$KTMTXNHEAD	0 rows
X$KTPRHIST	0 rows
X$KTPRXRS	0 rows
X$KTPRXRT	0 rows
X$KTRSO	0 rows
X$KTSIMAPOOL	0 rows
X$KTSIMAU	6 rows
X$KTSIMXMEMAREA	9 rows
X$KTSJPROC	9 rows
X$KTSJTASK	5 rows
X$KTSJTASKCLASS	41 rows
X$KTSKSTAT	0 rows
X$KTSLCHUNK	0 rows
X$KTSPGS_STAT	6 rows
X$KTSPSTAT	1 row
X$KTSP_REPAIR_LIST	0 rows
X$KTSSO	1 row
X$KTSSPU	0 rows
X$KTSTFC	0 rows
X$KTSTSSD	2 rows
X$KTSTUSC	8 rows
X$KTSTUSG	8 rows
X$KTSTUSS	8 rows
X$KTTEFINFO	12 rows
X$KTTETS	12 rows
X$KTTVS	Possibly relates to tablespaces that have valid save undo segments, 14 rows
X$KTUCUS	0 rows
X$KTUGD	1 row
X$KTUMASCN	1 row
X$KTUQQRY	122024 rows
X$KTURD	11 rows
X$KTURHIST	0 rows
X$KTUSMST	133 rows
X$KTUSMST2	1 row
X$KTUSUS	13 rows
X$KTUTST	0 rows
X$KTUXCHE	0 rows
X$KTUXE	The most recent 438 terminated sessions (appears to always be 438)

Kernel Variables

Object Name	Notes
X$KVII	Internal instance parameters set at instance initialization col kviitag format a24 col kviidsc format a54 SELECT kviival, kviitag, kviidsc FROM x$kvii; KVIIVAL KVIITAG KVIIDSC -------- ------------------------ ----------------------------------------------------- 1566 kslnbe # of base events 447 kslnbesess # of base events in session 703 kslltl number of latches 39952 kslerb event range base 4 ksbcpurawthrcnt_startup initial number of raw CPU threads in the system 4 ksbcpueffthrcnt_startup initial number of effective CPU threads in the system 4096 kcbswc DBWR max outstanding writes 1 kcbnwp number of DBWR processes 204 kcbscw DBWR write chunk 1 kctsat true if Statically Allocated Thread 1 kctthr THRead mounted by this instance - zero if none 1 ktsinm sga shadow value of instance_number
X$KVIS	Oracle Data Block (size_t type) variables, 0 rows
X$KVIT	Instance internal flags, variables and parameters that can change during the life of an instance col kvittag format a17 col kvitdsc format a40 SELECT kvitval, kvittag, kvitdsc FROM x$kvit; KVITVAL KVITTAG KVITDSC ------- ---------------- ------------------------------------------------ 4 ksbcpurawthrcnt number of raw CPU threads in the system used by Oracle 4 ksbcpueffthrcnt number of effective CPU threads in the system used by Oracle 2 ksbcpucore number of physical CPU cores in the system used by Oracle 1 ksbcpusocket number of physical CPU sockets in the system used by Oracle 4 ksbcpu_hwm high water mark of number of CPUs used by Oracle 2 ksbcpucore_hwm high water mark of number of CPU cores on system 1 ksbcpusocket_hwm high water mark of number of CPU sockets on system 4 ksbcpu_actual number of available CPUs in the system 1 ksbcpu_dr CPU dynamic reconfiguration supported 469920 kcbncbh number of buffers in cdb 469920 kcbnbh number of buffers 25 kcbldq large dirty queue if kcbclw reaches this 40 kcbfsp Max percentage of LRU list foreground can scan for free 2 kcbcln Initial percentage of LRU list to keep clean 3200 kcbnbf number buffer objects 0 kcbwst Flag that indicates recovery or db suspension 0 kcteln Error Log Number for thread open 0 kcvgcw SGA: opcode for checkpoint cross-instance call 0 kcvgcw SGA:opcode for pq checkpoint cross-instance call

Large Objects (LOB)

Object Name	Notes
X$ABSTRACT_LOB	52 rows
X$KEWX_LOBS	0 rows
X$LOBSEGSTAT	LOB Segment Stats, 2 rows
X$LOBSTAT	LOB Stats, 5 rows
X$LOBSTATHIST	LOB Stat History, 30 rows
X$TEMPORARY_LOB_REFCNT	4 rows

Locks

Object Name	Notes
X$LE	Lock Element : each PCM lock that is used by the buffer cache (gc_db_locks) If a buffer is locked maps to v$lock_element - 0 rows

Log Buffers

Object Name	Notes
X$LOGBUF_READHIST	Log buffer read history, 16 rows

Log Miner

Object Name	Notes
X$LOGMNR_ATTRCOL$	0 rows
X$LOGMNR_ATTRIBUTE$	0 rows
X$LOGMNR_CALLBACK	0 rows
X$LOGMNR_CDEF$	Log Miner Column Definitions, 0 rows
X$LOGMNR_CLU$	Log Miner Clusters, 0 rows
X$LOGMNR_COL$	Log Miner Columns, 0 rows
X$LOGMNR_COLTYPE$	Log Miner Column Types, 0 rows
X$LOGMNR_CONTENTS	Used by DBMS_LOGMNR to hold redo log transactions while mining
X$LOGMNR_DICTIONARY	0 rows
X$LOGMNR_DICTIONARY_LOAD	0 rows
X$LOGMNR_ENC$	0 rows
X$LOGMNR_ENCRYPTED_OBJ$	Log Miner Encrypted Objects, 0 rows
X$LOGMNR_ENCRYPTION_PROFILE$	0 rows
X$LOGMNR_FILE$	Log Miner Data and Temp Files, 0 rows
X$LOGMNR_IND$	Log Miner Indexes, 0 rows
X$LOGMNR_INDCOMPART$	0 rows
X$LOGMNR_INDPART$	Log Miner Index Partitions, 0 rows
X$LOGMNR_INDSUBPART$	Log Miner Index Subpartitions, 0 rows
X$LOGMNR_KOPM$	0 rows
X$LOGMNR_KTFBUE	0 rows
X$LOGMNR_LATCH	Log Miner Latches, 0 rows
X$LOGMNR_LOB$	Log Miner Large Objects, 0 rows
X$LOGMNR_LOBFRAG$	0 rows
X$LOGMNR_LOG	0 rows
X$LOGMNR_LOGFILE	Log Miner Log Files, 0 rows
X$LOGMNR_LOGS	0 rows
X$LOGMNR_NTAB$	0 rows
X$LOGMNR_OBJ$	Log Miner Objects, 0 rows
X$LOGMNR_OPQTYPE$	Log Miner Opaque Types, 0 rows
X$LOGMNR_PARAMETERS	Log Miner Parameters, 0 rows
X$LOGMNR_PARTOBJ$	0 rows
X$LOGMNR_PROCESS	0 rows
X$LOGMNR_PROPS$	0 rows
X$LOGMNR_REFCON$	0 rows
X$LOGMNR_REGION	0 rows
X$LOGMNR_ROOT$	0 rows
X$LOGMNR_SEG$	Log Miner Segments, 0 rows
X$LOGMNR_SESSION	Log Miner Sessions, 0 rows
X$LOGMNR_SUBCOLTYPE$	0 rows
X$LOGMNR_TAB$	Logminer Tables, 0 rows
X$LOGMNR_TABCOMPART$	0 rows
X$LOGMNR_TABPART$	Log Miner Table Partitions, 0 rows
X$LOGMNR_TABSUBPART$	Log Miner Table Subpartitions, 0 rows
X$LOGMNR_TS$	Log Miner Tablespaces, 0 rows
X$LOGMNR_TYPE$	Log Miner Data Types, 0 rows
X$LOGMNR_UET$	0 rows
X$LOGMNR_UNDO$	Log Miner Undo, 0 rows
X$LOGMNR_USER$	Log Miner Users, 0 rows

Long Operations

Object Name	Notes
X$KSULOP	Kernel Services: user long operation, 115 rows
X$XSLONGOPS	Maps to v$session_longops, 0 rows

Memoptimize Pool

Object Name	Notes
X$MEMOPTIMIZE_WRITE_AREA	3 rows

MMON Process

Object Name	Notes
X$MMON_ACTION	224 rows
X$MMON_AUTOTASK	7 rows
X$MMON_TRACKING_DEBUG	0 rows

Mutexes

Object Name	Notes
X$MUTEX_SLEEP	24 rows
X$MUTEX_SLEEP_HISTORY	183 rows

NLS Parameters

Object Name Notes

X$NLS_PARAMETERS set NLS database parameters, 20 rows

col parameter format a26

SELECT parameter, value FROM x$nls_parameters ORDER BY 1; PARAMETER VALUE -------------------------- ------------------------------ NLS_CALENDAR GREGORIAN NLS_CHARACTERSET AL32UTF8 NLS_COMP BINARY NLS_CURRENCY $ NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS NLS_DATE_LANGUAGE AMERICAN NLS_DUAL_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_LANGUAGE AMERICAN NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_NCHAR_CONV_EXCP FALSE NLS_NUMERIC_CHARACTERS ., NLS_SORT BINARY NLS_SPECIAL_CHARS |@ NLS_TERRITORY AMERICA NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR SELECT parameter, value FROM x$nls_parameters MINUS SELECT parameter, value FROM v$nls_parameters PARAMETER VALUE -------------------------- ------ NLS_SPECIAL_CHARS |@

NFS Mount

Object Name	Notes
X$NFSCLIENTS	NFS Clients, 0 rows
X$NFSLOCKS	NFS Locks, 0 rows
X$NFSOPENS	0 rows

Physical Files

Object Name Notes

X$KCCFN col fnnam format a75 SELECT fnnam FROM x$kccfn ORDER BY 1; FNNAM -------------------------------------------------------------- C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\ONLINELOG\REDO01B.LOG C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\ONLINELOG\REDO02B.LOG C:\U01\ORABASE19\FAST_RECOVERY_AREA\ORABASEXIX\ONLINELOG\REDO03B.LOG C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBDEV\SYSAUX01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBDEV\SYSTEM01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBDEV\TEMP01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBDEV\UNDOTBS01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBDEV\USERS01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBDEV\UWDATA01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBSEED\SYSAUX01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBSEED\SYSTEM01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBSEED\TEMP01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\PDBSEED\UNDOTBS01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\REDO01.LOG C:\U01\ORABASE19\ORADATA\ORABASEXIX\REDO02.LOG C:\U01\ORABASE19\ORADATA\ORABASEXIX\REDO03.LOG C:\U01\ORABASE19\ORADATA\ORABASEXIX\SYSAUX01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\SYSTEM01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\TEMP01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\UNDOTBS01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\USERS01.DBF C:\U01\ORABASE19\ORADATA\ORABASEXIX\UWDATA01.DBF C:\TEMP\PART01.DBF C:\TEMP\PART02.DBF C:\TEMP\PART03.DBF C:\TEMP\PART04.DBF

Processes and Process Messages

Object Name	Notes
X$MESSAGES	-- background process messages, 530 rows `col description format a70 col dest format a10 SELECT dest, description FROM x$messages ORDER BY 1,2;`

Quality of Service

Object Name	Notes
X$QOSADVACTIONDEF	24 rows
X$QOSADVFINDINGDEF	51 rows
X$QOSADVRATIONALEDEF	38 rows
X$QOSADVRECDEF	35 rows
X$QOSADVRULEDEF	24 rows

RDA

Object Name	Notes
X$KCCDC	3 rows

Real Application Clusters

Object Name	Notes
X$INSTANCE_CACHE_TRANSFER	`0 rows SELECT inst_id, lost, lost_time, cr_2hop_time, cr_3hop_time, cr_busy_time, cr_congested_time FROM x$instance_cache_transfer;`
X$KCBSC	I/O, 20 rows

Redo and Undo

Object Name	Notes
X$KCRFSTRAND	Redo pool descriptor, 57 rows
X$KTIFP	In-Memory Undo, 55 rows

Rules and Rule Sets

Object Name	Notes
X$RULE	rule WHERE clause & metrics - 1 row `col rule_owner format a10 col rule_condition format a44 SELECT rule_owner OWNER, rule_condition, true_hits, maybe_hits FROM x$rule; OWNER RULE_CONDITION TRUE_HITS MAYBE_HITS ----- --------------------------------------------- --------- ---------- SYS tab.user_data.MESSAGE_LEVEL <> 32 AND tab. 2 0 user_data.MESSAGE_GROUP = 'High Availability'`

Tablespaces

Object Name Notes

X$KCCTS

Note, as highlighted, TSNAM includes tablespaces that do not show in the data dictionary (highlighted) not just those in PDB$SEED. A TSTSN value of -1 indicates the tablespace has been dropped. SELECT con_id, tablespace_name FROM cdb_tablespaces ORDER BY 1,2; CON_ID TABLESPACE_NAME ------- ---------------- 1 SYSAUX 1 SYSTEM 1 TEMP 1 UNDOTBS1 1 USERS 1 UWDATA 3 SYSAUX 3 SYSTEM 3 TEMP 3 UNDOTBS1 3 USERS 3 UWDATA col tsnam format a12 SELECT con_id, tsnam, tstsn FROM x$kccts ORDER BY 1,2; CON_ID TSNAM TSTSN ------- ------------ ----- 1 COMPALL -1 1 COMPDIR -1 1 COMPIDXADV -1 1 SYSAUX 1 1 SYSTEM 0 1 TEMP 3 1 UNDOTBS1 2 1 USERS 4 1 UWDATA 6 2 SYSAUX 1 2 SYSTEM 0 2 TEMP 3 2 UNDOTBS1 2 3 SYSAUX 1 3 SYSTEM 0 3 TEMP 3 3 UNDOTBS1 2 3 USERS 5 3 UWDATA 6

Time Zone

Object Name	Notes
X$TIMEZONE_FILE	timezone file current in use `SELECT filename, version FROM x$timezone_file; FILENAME VERSION -------------------- -------- timezlrg_32.dat 32`
X$TIMEZONE_NAMES	Timezones, 2341 rows `col tzname format a30 SELECT tzname, tzabbrev FROM x$timezone_names;`

Wait Interface

Object Name	Notes
X$KCBSH	58560 rows
X$KSDHNG_CHAINS	Blockers and waits- 0 rows

X$AL Objects

Object Name	Notes
X$ALL_KESWXMON	68 rows
X$ALL_KESWXMON_PLAN	542 rows
X$ALL_KQLFXPL	22163 rows

X$BU Objects

Object Name	Notes
X$BUFFER	0 rows
X$BUFFER2	0 rows

X$CON Objects

Object Name	Notes
X$CON	3 rows
X$CONTEXT	0 rows
X$CON_KEWMDRMV	65435 rows
X$CON_KEWMSMDV	263 rows
X$CON_KEWSSYSV	72 rows
X$CON_KSLEI	5754 rows
X$CON_KSLSCS	39 rows
X$CON_KSLSESHIST_MICRO	3314 rows
X$CON_KSUSGSTA	6000 rows

X$DB Objects

Object Name	Notes
X$DBGATFLIST	1 row
X$DBGDIREXT	714 rows
X$DBGLOGEXT	2 rows
X$DBGRICX	1 row
X$DBGRIFX	2 rows
X$DBGRIKX	8 rows
X$DBGRIPX	1 row
X$DBGTFLIST	231 rows
X$DBGTFOPTT	0 rows
X$DBGTFSOPTT	0 rows
X$DBGTFSQLT	0 rows
X$DBGTFSSQLT	0 rows
X$DBGTFVIEW	15760 rows
X$DBKECE	42 rows
X$DBKEFAFC	`SELECT "ACTION ID", "ACTION NAME" FROM x$dbkefafc ORDER BY 2; ACTION ID ACTION NAME ---------- -------------------------------- 34340926 ASM_ALLOC_FAIL_CHECK 34340905 DB_STRUCTURE_INTEGRITY_CHECK 34340906 REDO_INTEGRITY_CHECK 17563684 act1 17563685 action1 17563686 action2 34340884 dbkeTestAction2`
X$DBKEFDEAFC	`SELECT "ACTION ID", "PROBLEM KEY" FROM x$dbkefdeafc; no rows selected`
X$DBKEFEFC	18 rows
X$DBKEFIEFC	512 rows
X$DBKFDG	0 rows
X$DBKFSET	0 rows
X$DBKH_CHECK	38 rows
X$DBKH_CHECK_PARAM	63 rows
X$DBKINCMETCFG	1 row
X$DBKINCMETINFO	0 rows
X$DBKINCMETSUMMARY	1 row
X$DBKINFO	0 rows
X$DBKRECO	0 rows
X$DBREPLAY_PATCH_INFO	0 rows

X$DG Objects

Object Name	Notes
X$DGLPARAM	37 rows
X$DGLXDAT	0 rows

X$DRA Objects

Object Name	Notes
X$DRA_FAILURE	75 rows
X$DRA_FAILURE_CHECK	53 rows
X$DRA_FAILURE_CHECK_MAP	107 rows
X$DRA_FAILURE_PARAM	190 rows
X$DRA_FAILURE_PARENT_MAP	11 rows
X$DRA_FAILURE_REPAIR	264 rows
X$DRA_FAILURE_REPAIR_MAP	116 rows
X$DRA_REPAIR	112 rows
X$DRA_REPAIR_PARAM	73 rows

X$DRM Objects

Object Name	Notes
X$DRM_HISTORY	0 rows
X$DRM_HISTORY_STATS	0 rows
X$DRM_WAIT_STATS	0 rows

X$GC Objects

Object Name	Notes
X$GCRACTIONS	0 rows
X$GCRLOG	0 rows
X$GCRMETRICS	0 rows
X$GCRSTATUS	0 rows

X$IE Objects

Object Name	Notes
X$IEE	0 rows
X$IEE_CONDITION	0 rows
X$IEE_ORPIECE	0 rows

X$IM Objects

Object Name	Notes
X$IMHMSEG	0 rows
X$IMHMSEG1	0 rows

X$IP Objects

Object Name	Notes
X$IPCOR_TOPO_DOMAIN	0 rows
X$IPCOR_TOPO_NDEV	0 rows

X$JO Objects

Object Name	Notes
X$JOXDRC	2 rows
X$JOXDRR	2 rows
X$JOXFC	37366 rows
X$JOXFD	1224 rows
X$JOXFM	37366 rows
X$JOXFR	1693 rows
X$JOXFS	2 rows
X$JOXFT	40285 rows
X$JOXMAG	442157 rows
X$JOXMEX	108095 rows
X$JOXMFD	174493 rows
X$JOXMIC	59944 rows
X$JOXMIF	39952 rows
X$JOXMMD	353537 rows
X$JOXMOB	37366 rows
X$JOXOBJ	40285 rows
X$JOXREF	498332 rows
X$JOXRSV	42594 rows
X$JOXSCD	68 rows

X$JS Objects

Object Name	Notes
X$JSKJOBQ	3 rows
X$JSKMIMMD	0 rows
X$JSKMIMRT	0 rows
X$JSKSLV	0 rows

X$KE Objects

Object Name	Notes
X$KEACMDN	64 rows
X$KEAFDGN	88 rows
X$KEAOBJT	32 rows
X$KECPDENTRY	0 rows
X$KECPRT	0 rows
X$KECPTC	0 rows
X$KECUC_CACHE	0 rows
X$KEC_COMPONENT_TIMING	6 rows
X$KEC_PROGRESS	8 rows
X$KEHECLMAP	12 rows
X$KEHETSX	9 rows
X$KEHEVTMAP	113 rows
X$KEHF	124 rows
X$KEHOSMAP	9 rows
X$KEHPRMMAP	25 rows
X$KEHR	-- lookup table possibly ADDM internal rules col description format a64 SELECT rule_id RID, description FROM x$kehr ORDER BY 1; RID DESCRIPTION --- ---------------------------------------------------------------- 0 1 Root node for the Wait-Model tree 2 Root node for the Time-Model tree 3 Root node for the top special rules like Top SQL 4 Rule node for Admin wait-class 5 Rule node for Application wait-class 6 To analyze waits on row locks 7 To analyze waits on table locks 8 To analyze waits on user-lock enqueues 9 Rule node for cluster wait-class 10 Rule node for cluster messaging 11 Rule node for cluster messaging network latency 12 Rule node for cluster messaging congestion 13 Rule node for cluster messaging contention 14 Rule node for cluster messaging lost blocks 15 Rule node for commit wait-class 16 Analyze high number of log file syncs 17 Rule node for Concurrency wait-class 18 Detect problems due to high number of buffer-busy waits 19 Detect problems due to hot blocks in buffer-busy 20 Detect problems due to hot objects in buffer-busy 21 Analyze latch-waits related to hard parsing 22 Detect too many buffer cache latch waits 23 To analyze waits on pipes 24 Rule node for Configuration wait-class 25 Detect too many free-buffer waits 26 To Investigate log file switch waits on archiving needed 27 To investigate log file switch waits on checkpoint incomplete 28 To detect contention on log buffer waits 29 To investigate on HW enqueue waits 30 To investigate on ST enqueue waits 31 To detect too many ITL waits 32 Detects too much time spent in cross instance tmp segment cleanu 33 Detect blocked enqueues in Streams AQ due to flow control waits 34 Detect blocked enqueues in Streams AQ due to low memory 35 Wait model rule node to detect CPU bottleneck 36 Rule to detect OS VM paging 37 Rule node for network wait-class 38 Rule node for queueing wait-class 39 Rule node for scheduler wait-class 40 Rule node for session slot wait event 41 Rule node for cpu quantum wait event 42 Rule node for rman pq queued wait event 43 Rule node for rman pga limit wait event 44 Rule node for rman I/O queue wait events 45 Rule node for Others wait-class 46 Rule node for latch free waits 47 Dummy rule node for all Other PQ waits 48 Rule node for IO wait-class 49 Detect DB IO problems due to application SQL and Objects waiting 50 Detect DB IO problems due to undersized buffer cache 51 Detect DB IO problems due to undersized streams pool 52 Detect DB IO problems due to excessive temp writes 53 Detect DB IO problems due to IO capacity limit 54 Detect DB IO problems due to excessive checkpoint writes 55 Detect DB IO problems due to excessive undo writes 56 Detect DB IO problems due to excessive pq checkpoint writes 57 Detect DB IO problems due to excessive truncate writes 58 Detect DB IO problems due to excessive tablespace DDL checkpoint 59 Detect writers contention on index block splits (enqueue TX) 60 Detect connection management issues 61 Detect SQL hard parsing issues 62 Detect SQL soft parsing issues 63 Detect SQL hard parsing issues due to cursor sharing criteria fa 64 Detect SQL hard parsing issues due to cursor aging 65 Detect SQL hard parsing issues due to too many non-memory failed 66 Detect SQL hard parsing issues due to out-of-memory failed parse 67 Detect SQL hard parsing issues due to cursor invalidation or lit 68 Investigate excessive rebinds 69 Investigate issues in PL/SQL execution 70 Investigate issues in PL/SQL compilation 71 Investigate issues in Java execution 72 Investigate issues in sequences 73 Admin unhandled wait events 74 Application unhandled wait events 75 Cluster unhandled wait events 76 Commit unhandled wait events 77 Concurrency unhandled wait events 78 Configuration unhandled wait events 79 Network unhandled wait events 80 Queueing unhandled wait events 81 Scheduler unhandled wait events 82 Other unhandled wait events 83 User IO unhandled wait events 84 SGA target size analysis 85 Top SQL statements by various criteria
X$KEHRP	lookup table col description format a65 SELECT parameter_id PID, description FROM x$kehrp ORDER BY 1; PID DESCRIPTION ---- ----------------------------------------------------------------- 0 1 # of terminal findings to be reported to the user 2 DB Elapsed Time threshold for reporting terminal fdgs 3 Minimum database activity in seconds of database elapsed time be 4 max # of sql stmt to be examined by some criteria 5 # of top (hot) objects to look at 6 # of top (hot) blocks to look at 7 percent of sql elapsed time to total db elapsed time below which 8 average inter-instance network latency in microseconds 9 Wait-class % threshold on DB elpased time 10 % threshold to find unhandled top events within a wait-class 11 # of top events inside wait-classes to find unhandled ones 12 min% threshold for wait ev elapsed time. Used in rule eval 13 relative % for a leftover symptom to become a terminal finding 14 % threshold on DB elapsed for CPU to be a bottleneck 15 Threshold % to decide on system cpu bottleneck 16 Min % occurence for a hot blocks 17 % above which a SQL Type should occur on a hot object to suggest 18 Ideally, how many seconds of redo should the redo log buffer be 19 Ideally, how many minutes of redo should the redo log file be a 20 Avg prcessing time (in microsec) for the LMS process 21 Avg block serve time for an instance. This includes the defer t 22 Expected (average) time in microseconds to wait for one read I/O 23 Min % of total IO for an object or a file to be selected 24 Max % for files with high average io time to indicate a file acc 25 minimum percent of a set of writes to total writes for it tocons 26 % of undo io from total io to indicate no problem 27 max number of rows to consult from shared pool advice 28 ratio of shared pool impact to total system time (in percent) to 29 total database elapsed time for the period being analyzed. This 30 Min value for (cursor invalidations/hardparse count) to consider 31 Min value for (distinct fms&plan/distinct hash) to consider lite 32 Min value (in bytes) for transaction size, to prevent generation 33 Min # of fetches per execution to investigate single row fetches 34 Max # of rows per fetch to investigate single row fetches 35 Maximal ratio (in percents) of AWR flush time to wall clock for 36 Min # of rows per exec to recommend array interface for INSs 37 % used to resize shared_pool_size for out-of-memory failed parse
X$KEHR_CHILD	raw/numeric - 90 rows
X$KEHSQT	65 rows
X$KEHSYSMAP	11 rows
X$KEHTIMMAP	19 rows
X$KEIUT	0 rows
X$KEIUT_INFO	0 rows
X$KELRSGA	1 row
X$KELRTD	130 rows
X$KELRXMR	3 rows
X$KELTGSD	8 rows
X$KELTOSD	29 rows
X$KELTSD	186 rows
X$KEOMM_DBOP_LIST	1 row
X$KEOMNMON_SESSTAT	0 rows
X$KERPIREPREQ	0 rows
X$KERPISTATS	1 row
X$KESPLAN	0 rows
X$KESSPAMET	9 rows
X$KESWXMON	68 rows
X$KESWXMON_PLAN	542 rows
X$KESWXMON_STATNAME	128 rows
X$KETCL	7 rows
X$KETOP	15 rows
X$KETTG	15 rows
X$KEUMLINKTB	0 rows
X$KEUMREGTB	0 rows
X$KEUMSVCTB	0 rows
X$KEUMTB	4 rows
X$KEUMTOPTB	0 rows
X$KEXSVFU	0 rows
X$KEXSVUS	0 rows

KEW Objects

Object Name	Notes
X$KEWAM	1 row
X$KEWASH	15729 rows
X$KEWCATPDBINSNAP	0 rows
X$KEWECLS	0 rows
X$KEWEFXT	0 rows
X$KEWEPCS	0 rows
X$KEWESMAS	0 rows
X$KEWESMS	0 rows
X$KEWFLINFO	6 rows
X$KEWMAFMV	0 rows
X$KEWMCHKMV	0 rows
X$KEWMDRMV	59013 rows
X$KEWMDSM	359 rows
X$KEWMEVMV	4629 rows
X$KEWMFLMV	91 rows
X$KEWMGSM	19 rows
X$KEWMIOFMV	915 rows
X$KEWMRMGMV	427 rows
X$KEWMRMPDBMV	122 rows
X$KEWMRSM	279 rows
X$KEWMRWMV	52984 rows
X$KEWMSEMV	106 rows
X$KEWMSMDV	212 rows
X$KEWMSRGMV	0 rows
X$KEWMSVCMV	430 rows
X$KEWMWCRMV	0 rows
X$KEWMWPCMV	0 rows
X$KEWPDBINSNAP	0 rows
X$KEWRATTRNEW	0 rows
X$KEWRATTRSTALE	0 rows
X$KEWRCACHE	0 rows
X$KEWRCONIDLOOKUP	4 rows
X$KEWRIMSEGSTAT	0 rows
X$KEWRIPSL	0 rows
X$KEWRSQLCRIT	0 rows
X$KEWRSQLIDTAB	0 rows
X$KEWRSQLSUM	0 rows
X$KEWRSTGTB	25 rows
X$KEWRTB	161 rows
X$KEWRTBGM	21 rows
X$KEWRTB_ALL	161 rows
X$KEWRTOPTENV	0 rows
X$KEWRTSEGSTAT	0 rows
X$KEWRTSQLPLAN	0 rows
X$KEWRTSQLTEXT	0 rows
X$KEWSSESV	49392 rows
X$KEWSSMAP	330 rows
X$KEWSSVCV	145 rows
X$KEWSSYSV	45 rows
X$KEWXOCF	32 rows
X$KEWX_INDEXES	0 rows
X$KEWX_SEGMENTS	0 rows

X$KF Objects

Object Name	Notes
X$KFALS	0 rows
X$KFBF	0 rows
X$KFBH	0 rows
X$KFBTYP	39 rows
X$KFCBH	0 rows
X$KFCCE	0 rows
X$KFCLLE	0 rows
X$KFCSGA	0 rows
X$KFCSTAT	0 rows
X$KFCTE	8 rows
X$KFCTET	0 rows
X$KFCTET_DELTA	0 rows
X$KFDAP	0 rows
X$KFDAT	0 rows
X$KFDDD	0 rows
X$KFDFS	0 rows
X$KFDPARTNER	0 rows
X$KFDSD	0 rows
X$KFDSK	0 rows
X$KFDSK_SPARSE	0 rows
X$KFDSK_SPARSE_STAT	0 rows
X$KFDSK_STAT	0 rows
X$KFDSR	0 rows
X$KFDXEXT	0 rows
X$KFENV	0 rows
X$KFFG	0 rows
X$KFFGF	0 rows
X$KFFGPT	0 rows
X$KFFIL	0 rows
X$KFFIL_SPARSE	0 rows
X$KFFOF	0 rows
X$KFFSCRUB	0 rows
X$KFIPCOMPAT	0 rows
X$KFFXP	0 rows
X$KFGBRB	0 rows
X$KFGBRC	0 rows
X$KFGBRS	0 rows
X$KFGBRW	0 rows
X$KFGMG	0 rows
X$KFGRP	0 rows
X$KFGRP_SPARSE	0 rows
X$KFGRP_STAT	0 rows
X$KFGXP	0 rows
X$KFIAS_CLNT	0 rows
X$KFIAS_FILE	0 rows
X$KFIAS_PROC	0 rows
X$KFIPCOMPAT	1 row
X$KFKID	0 rows
X$KFKLIB	0 rows
X$KFKLSOD	0 rows
X$KFMDGRP	0 rows
X$KFNCL	0 rows
X$KFNCWCL	0 rows
X$KFNDIOSPRS	0 rows
X$KFNRCL	0 rows
X$KFNSDSKIOST	0 rows
X$KFQG	0 rows
X$KFRC	0 rows
X$KFTMTA	0 rows
X$KFVACFS	ACFS 0 rows
X$KFVACFSA	0 rows
X$KFVACFSADMIN	ACFS Administration 0 rows
X$KFVACFSCMDRULE	ACFS 0 rows
X$KFVACFSENCR	ACFS 0 rows
X$KFVACFSREALM	ACFS 0 rows
X$KFVACFSREALMFILTER	ACFS 0 rows
X$KFVACFSREALMGROUP	ACFS 0 rows
X$KFVACFSREALMS	ACFS 0 rows
X$KFVACFSREALMUSER	ACFS 0 rows
X$KFVACFSREPL	ACFS 0 rows
X$KFVACFSREPLTAG	ACFS 0 rows
X$KFVACFSRULE	ACFS 0 rows
X$KFVACFSRULESET	ACFS 0 rows
X$KFVACFSRULESETRULE	ACFS 0 rows
X$KFVACFSS	ACFS 0 rows
X$KFVACFSTAG	ACFS 0 rows
X$KFVACFSV	ACFS 0 rows
X$KFVOL	0 rows
X$KFVOLSTAT	0 rows
X$KFZGDR	0 rows
X$KFZPBLK	0 rows
X$KFZUAGR	0 rows
X$KFZUDR	0 rows

X$KM Objects

Object Name	Notes
X$KMCQS	6 rows
X$KMCVC	0 rows
X$KMGSBSADV	14 rows
X$KMGSBSMEMADV	0 rows
X$KMGSCT	22 rows
X$KMGSOP	22 rows
X$KMGSTFR	800 rows
X$KMMDI	2 rows
X$KMMDP	1 row
X$KMMHST	1 row
X$KMMNV	3 rows
X$KMMRD	96 rows
X$KMMSAS	1 row
X$KMMSG	1 row
X$KMMSI	8 rows
X$KMPCMON	4 rows
X$KMPCP	4 rows
X$KMPCSO	1 row
X$KMPDH	1 row
X$KMPSRV	8 rows

X$KN Objects

Object Name	Notes
X$KNGFL	0 rows
X$KNGFLE	0 rows
X$KNLAROW	0 rows
X$KNLASG	1 row
X$KNLPMSGSTAT	14 rows
X$KNLP_ACTV_MSGS	0 rows
X$KNLP_PEND_MSGS	0 rows
X$KNSTACR	0 rows
X$KNSTANR	0 rows
X$KNSTASL	0 rows
X$KNSTCAP	0 rows
X$KNSTCAPCACHE	0 rows
X$KNSTCAPS	0 rows
X$KNSTMT	0 rows
X$KNSTMVR	Kernel replication, statistics materialized view refresh. Base table of v$mvrefresh. Stores MV refresh history info, such as session SID and serial#. Un-exposed columns reftype_knstmvr, groupstate_knstmvr and total_* are useful; see the query in Note: 258021.1. 0 rows
X$KNSTOGGC	10 rows
X$KNSTORD	0 rows
X$KNSTPSTS	0 rows
X$KNSTRPP	0 rows
X$KNSTRQU	1 row
X$KNSTSESS	3 rows
X$KNSTTXN	0 rows
X$KNSTXSTS	0 rows

X$KP Objects

Object Name	Notes
X$KPDBVPROXY	0 rows
X$KPONDCONSTAT	0 rows
X$KPONDESTAT	0 rows
X$KPONESTAT	0 rows
X$KPONJSTAT	1 row
X$KPOQSTA	0 rows
X$KPOXFT	17 rows
X$KPPLCC_INFO	0 rows
X$KPPLCC_STATS	0 rows
X$KPPLCONN_INFO	0 rows
X$KPPLCP_STATS	0 rows

X$KR Objects

Object Name	Notes
X$KRASGA	1 row
X$KRBABRSTAT	0 rows
X$KRBAFF	13 rows
X$KRBMCA	8 rows
X$KRBMROT	72 rows
X$KRBMRST	0 rows
X$KRBMSFT	0 rows
X$KRBPDATA	0 rows
X$KRBPDIR	0 rows
X$KRBPHEAD	0 rows
X$KRBPPBCTX	1 row
X$KRBPPBTBL	34 rows
X$KRBPSPARSE	0 rows
X$KRBZA	3 rows
X$KRCBIT	Bitmap Block, 0 rows
X$KRCCDE	0 rows
X$KRCCDR	0 rows
X$KRCCDS	0 rows
X$KRCEXT	Allocate Bitmap Extent Map, 0 rows
X$KRCFBH	Bitmap Extent Header, 0 rows
X$KRCFDE	Datafile Descriptor, 0 rows
X$KRCFH	Block Change Tracking File Header, 0 rows
X$KRCGFE	0 rows
X$KRCSTAT	1 row
X$KRDEVTHIST	0 rows
X$KRDMMIRA	0 rows
X$KRDRSBROV	0 rows
X$KRFBLOG	0 rows
X$KRFGSTAT	0 rows
X$KRFSTHRD	0 rows
X$KRSOPROC	9 rows
X$KRSSMS	7 rows
X$KRSSRTT	10 rows
X$KRSTALG	24 rows
X$KRSTAPPSTATS	1055 rows
X$KRSTDEST	31 rows
X$KRSTDGC	0 rows
X$KRSTPVRS	0 rows
X$KRVSLV	0 rows
X$KRVSLVPG	0 rows
X$KRVSLVS	0 rows
X$KRVSLVST	0 rows
X$KRVSLVTHRD	0 rows
X$KRVXDKA	33 rows
X$KRVXDTA	50 rows
X$KRVXISPCHK	0 rows
X$KRVXISPLCR	0 rows
X$KRVXOP	0 rows
X$KRVXSV	0 rows
X$KRVXTHRD	0 rows
X$KRVXTX	0 rows
X$KRVXWARNV	0 rows

X$KU Objects

Object Name	Notes
X$KUPVA	0 rows
X$KUPVJ	0 rows

X$KW Objects

Object Name	Notes
X$KWDDEF	2457 rows
X$KWQBPMT	1 row
X$KWQDLSTAT	90 rows
X$KWQITCX	1 row
X$KWQMNC	1 row
X$KWQMNJIT	31 rows
X$KWQMNSCTX	2 rows
X$KWQMNTASK	2 rows
X$KWQMNTASKSTAT	27 rows
X$KWQPD	0 rows
X$KWQPS	0 rows
X$KWQSI	64 rows
X$KWRSNV	13 rows
X$KWSBGAQPCSTAT	1 row
X$KWSBGQMNSTAT	1 row
X$KWSBJCSQJIT	0 rows
X$KWSBSMSLVSTAT	2 rows
X$KWSCPJOBSTAT	0 rows
X$KWSSUBUSTATS	0 rows

X$KY Objects

Object Name	Notes
X$KYWMCLTAB	0 rows
X$KYWMNF	0 rows
X$KYWMPCMN	0 rows
X$KYWMPCS	0 rows
X$KYWMPCTAB	0 rows
X$KYWMWRCTAB	0 rows
X$KYWMWRCTAB	0 rows

X$OFS Objects

Object Name	Notes
X$OFSMOUNT	0 rows
X$OFS_RW_LATENCY_STATS	0 rows
X$OFS_RW_SIZE_STATS	0 rows
X$OFS_STATS	0 rows

X$QE Objects

Object Name	Notes
X$QERFXTST	10 rows
X$QESBLSTAT	20 rows
X$QESMMAHIST	462 rows
X$QESMMAPADV	14 rows
X$QESMMIWH	33 rows
X$QESMMIWT	0 rows
X$QESMMSGA	40 rows
X$QESRCDEP	14 rows
X$QESRCDR	27 rows
X$QESRCMEM	64 rows
X$QESRCMSG	0 rows
X$QESRCOBJ	47 rows
X$QESRCRD	0 rows
X$QESRCRR	14 rows
X$QESRCSTA	48 rows
X$QESRSTAT	0 rows
X$QESRSTATALL	22087 rows
X$QESXL	0 rows

X$QK Objects

Object Name	Notes
X$QKSBGSES	116720 rows
X$QKSBGSYS	1459 rows
X$QKSCESES	48560 rows
X$QKSCESYS	607 rows
X$QKSCR	0 rows
X$QKSCR_RSN	0 rows
X$QKSFGI_CURSOR	1432 rows
X$QKSFM	1584 rows
X$QKSFMDEP	1584 rows
X$QKSFMPRT	1589 rows
X$QKSHT	373 rows
X$QKSMMWDS	2556 rows
X$QKSXA_REASON	522 rows

X$RF Objects

Object Name	Notes
X$RFAFO	0 rows
X$RFAHIST	0 rows
X$RFMP	1 row
X$RFMTE	0 rows
X$RFOB	3 rows

X$SK Objects

Object Name	Notes
X$SKGXPIA	0 rows
X$SKGXP_CONNECTION	not queryable
X$SKGXP_MISC	not queryable
X$SKGXP_PORT	not queryable

X$SQL Objects

Object Name	Notes
X$SQL_SHARD	0 rows
X$SQL_TESTCASES	0 rows

X$ZA Objects

Object Name	Notes
X$ZASAXTAB	0 rows
X$ZASAXTD1	0 rows
X$ZASAXTD2	0 rows
X$ZASAXTD3	0 rows

Miscellaneous Objects

Object Name	Notes
X$BMAPNONDURSUB	90 rows
X$CDBVW$	`SQL> desc X$CDBVW$ ERROR: ORA-65318: query to cross-container fixed tables not allowed`
X$COMVW$	`SQL> desc X$COMVW$ ERROR: ORA-65318: query to cross-container fixed tables not allowed`
X$DURABLE_SHARDED_SUBS	0 rows
X$GIMSA	24 rows
X$GLOBALCONTEXT	0 rows
X$GSMREGIONS	0 rows
X$GWMRAFF	0 rows
X$HOFP	0 rows
X$KAUVRSTAT	1 row
X$KBRPSTAT	133 rows
X$KLCIE	0 rows
X$KLPT	0 rows
X$KOCST	1 row
X$LOCKDOWN	0 rows
X$MODACT_LENGTH	1 row
X$MSGBM	0 rows
X$NONDURSUB	0 rows
X$NONDURSUB_LWM	0 rows
X$NSV	0 rows
X$OBJECT_POLICY_STATISTICS	0 rows
X$OBJ_BIN_EXCEPTIONS	64 rows
X$OBLNK$	Not queryable
X$OCT	256 rows
X$OPARG	721 rows
X$OPDESC	1097 rows
X$OPERATORS	1097 rows`SELECT name, operands, in_type, out_type FROM x$operators ORDER BY 1;`
X$OPTIM_CALIB_STATS	Workload calibration and DBMS_STATS.SET_PROCESSING_RATE col statname_kkecstats format a25 SELECT UNIQUE statname_kkecstats, statvalue_kkecstats FROM x$optim_calib_stats WHERE statvalue_kkecstats > 0 ORDER BY 1,2; STATNAME_KKECSTATS STATVALUE_ ------------------------- ---------- AGGR 1000.00000 ALL 200.00000 CPU 200.00000 CPU_ACCESS 200.00000 CPU_AGGR 200.00000 CPU_BYTES_PER_SEC 1000.00000 CPU_FILTER 200.00000 CPU_GBY 200.00000 CPU_HASH_JOIN 200.00000 CPU_IMC_BYTES_PER_SEC 2000.00000 CPU_IMC_ROWS_PER_SEC 2000000.00 CPU_JOIN 200.00000 CPU_NL_JOIN 200.00000 CPU_RANDOM_ACCESS 200.00000 CPU_ROWS_PER_SEC 1000000.00 CPU_SEQUENTIAL_ACCESS 200.00000 CPU_SM_JOIN 200.00000 CPU_SORT 200.00000 HASH 200.00000 IO 200.00000 IO_ACCESS 200.00000 IO_BYTES_PER_SEC 200.00000 IO_IMC_ACCESS 1000.00000 IO_RANDOM_ACCESS 200.00000 IO_ROWS_PER_SEC 1000000.00 IO_SEQUENTIAL_ACCESS 200.00000 MEMCMP 500.00000 MEMCPY 1000.00000
X$OPTION	SELECT parameter, value FROM x$option ORDER BY 1; many, if not all of the rows,tie back to DBA_FEATURE_USAGE_STATISTICS
X$OPVERSION	1097 rows
X$ORAFN	193 rows
X$POLICY_HISTORY	0 rows
X$PQSCANRATE	5 rows
X$PRMSLTYX	26 rows
X$PROPS	42 rows
X$QUIESCE	1 row
X$RMA_LATCH	0 rows
X$RO_USER_ACCOUNT	0 rows
X$RPOP_FILESTATS	0 rows
X$RULE_SET	col name format a12 SELECT name, cpu_time, elapsed_time, last_loading_time, first_load_time FROM x$rule_set; NAME CPU_TIME ELAPSED_TIME LAST_LOADING_TIME FIRST_LOAD_TIME ----------- -------- ------------ ----------------- -------------------- ALERT_QUE_R 3 18 18 30-MAY-2021 03:38:48 SELECT name, last_load_time, sharable_mem, reloads FROM x$rule_set; NAME LAST_LOAD_TIME SHARABLE_MEM RELOADS ----------- -------------------- ------------ ------- ALERT_QUE_R 21-JUL-2021 14:37:10 25184 1
X$RXS_SESSION_ROLES	0 rows
X$SHADOW_DATAFILE	0 rows
X$TARGETRBA	1 row
X$TRACE	7299 rows
X$TRACE_EVENTS	1000 rows
X$UGANCO	0 rows
X$VINST	0 rows
X$XSAGGR	0 rows
X$XSAGOP	29 rows
X$XSAWSO	32 rows
X$XSOBJECT	0 rows
X$XSOQMEHI	0 rows
X$XSOQOJHI	0 rows
X$XSOQOPHI	0 rows
X$XSOQOPLU	0 rows
X$XSOQSEHI	0 rows
X$XSSINFO	0 rows
X$XS_SESSIONS	0 rows
X$XS_SESSION_NS_ATTRIBUTES	0 rows
X$XS_SESSION_ROLES	0 rows

X$ Demos

Find sessions originating across db links

Courtesy of Mark Bobak Finding open database links database wide

Mark Bobak's query (originally in Metalink forum thread 524821.994, where he further attributed authorship) uses this table to find sessions coming from or going to a remote database; in short, x$k2gte.k2gtdses matches v$session.saddr, .k2gtdxcb matches v $transaction.addr. It's more robust than this query, and better than checking for DX locks for outgoing sessions (since a DX lock only shows up in v$lock for the current distributed transaction session).

SELECT /*+ ORDERED */ SUBSTR(s.ksusemnm,1,10)||'-'|| SUBSTR(s.ksusepid,1,10)  ORIGIN, SUBSTR(g.k2gtitid_ora,1,35) GTXID,

      SUBSTR(s.indx,1,4) ||'.'|| SUBSTR(s.ksuseser,1,5) LSESSION,

      s2.username,

      SUBSTR(DECODE(BITAND(s.ksuseidl,11),

             1,'ACTIVE', 0,

             DECODE(BITAND(s.ksuseflg,4096) , 0,'INACTIVE','CACHED'),

             2,'SNIPED',

             3,'SNIPED',

             'KILLED' ),1,1) S,

      SUBSTR(w.event,1,10) "WAITING"

      FROM x$k2gte g, x$ktcxb t, x$ksuse s, gv$session_wait w, gv$session s2

      WHERE g.k2gtdxcb =t.ktcxbxba

      AND g.K2GTDSES=t.ktcxbses

      AND s.addr=g.k2gtdses

      AND w.sid=s.indx

      AND s2.sid = w.sid;

Largest number of blocks per write SELECT kviival write_batch_size FROM x$kvii WHERE kviitag = 'kcbswc'; WRITE_BATCH_SIZE ---------------- 4096

GETS and WAITS for different queue types col ksqstexpl format a52 col ksqstrsn format a27 SELECT * FROM x$ksqst WHERE ksqstsgt > 0; returns 116 rows