原文转自：http://www.oracleblog.org/study-note/summary-of-some-x-tables/
今天在eygle的网站上看到了一篇关于死事务的恢复进度查询的文章，里面介绍x$ktuxe表的使用。因此想对X$表进行一个整理，以便在今后使用的时候能方便的查询。如果大家有其他使用X$表的经验，也欢迎补充。
在oracle 9i R2的版本，有394个X$表：
sys
@
ORALOCAL
(
192.168.0.22
)
>
select
count
(
*
)
from
v
$
fixed_table
where
upper
(
NAME
)
like
'
X$%
'
;
COUNT ( * )
-------- --
394
COUNT ( * )
-------- --
394
以下是一些用过的或者常用的X$表：
目前已经小结的有：
1.X$KTUXE
2.X$KGLPN
3.X$KGLOB
4.X$KSMSP
5.X$KCCCP
6.X$KSMLRU
7.X$KSQRS
8.X$KGLLK
9.X$KTSSO
1.X$KTUXE——[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry
sys
@
ORALOCAL
(
192.168.0.22
)
>
desc
X
$
KTUXE
##主要用来查看scn和smon正在清理的undo block
Name Null ? Type
--------------------------------------------------- -- -------- ---------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KTUXEUSN NUMBER
KTUXESLT NUMBER
KTUXESQN NUMBER
KTUXERDBF NUMBER
KTUXERDBB NUMBER
KTUXESCNB NUMBER
KTUXESCNW NUMBER
##9i之前版本根据max(ktuxescnw * power(2, 32) + ktuxescnb) 算出当前scn
KTUXESTA VARCHAR2 ( 16 )
KTUXECFL VARCHAR2 ( 24 )
## 如果值为'DEAD'，表示有事务在做回滚；NONE表示正常；
KTUXEUEL NUMBER
KTUXEDDBF NUMBER
KTUXEDDBB NUMBER
KTUXEPUSN NUMBER
KTUXEPSLT NUMBER
KTUXEPSQN NUMBER
KTUXESIZ NUMBER
##需要回滚的undo block大小
##主要用来查看scn和smon正在清理的undo block
Name Null ? Type
--------------------------------------------------- -- -------- ---------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KTUXEUSN NUMBER
KTUXESLT NUMBER
KTUXESQN NUMBER
KTUXERDBF NUMBER
KTUXERDBB NUMBER
KTUXESCNB NUMBER
KTUXESCNW NUMBER
##9i之前版本根据max(ktuxescnw * power(2, 32) + ktuxescnb) 算出当前scn
KTUXESTA VARCHAR2 ( 16 )
KTUXECFL VARCHAR2 ( 24 )
## 如果值为'DEAD'，表示有事务在做回滚；NONE表示正常；
KTUXEUEL NUMBER
KTUXEDDBF NUMBER
KTUXEDDBB NUMBER
KTUXEPUSN NUMBER
KTUXEPSLT NUMBER
KTUXEPSQN NUMBER
KTUXESIZ NUMBER
##需要回滚的undo block大小
2.x$kglpn——[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
sys
@
ORALOCAL
(
192.168.0.22
)
>
desc
x
$
kglpn
##主要用来处理library cache pin holder
Name Null ? Type
--------------------------------------------------- -- -------- ------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW ( 4 )
KGLPNUSE RAW ( 4 )
KGLPNSES RAW ( 4 )
KGLPNHDL RAW ( 4 )
##关联v$session_wait中event为library cache pin的P1RAW，再关联v$session，可以查出sid和serial#
KGLPNLCK RAW ( 4 )
KGLPNCNT NUMBER
KGLPNMOD NUMBER
##如果值为3，表示为library cache pin的holder；如果值为0，表示为waiter
KGLPNREQ NUMBER
##如果值为0，表示为library cache pin的holder；如果值为2，表示为waiter
KGLPNDMK NUMBER
KGLPNSPN NUMBER
##主要用来处理library cache pin holder
Name Null ? Type
--------------------------------------------------- -- -------- ------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW ( 4 )
KGLPNUSE RAW ( 4 )
KGLPNSES RAW ( 4 )
KGLPNHDL RAW ( 4 )
##关联v$session_wait中event为library cache pin的P1RAW，再关联v$session，可以查出sid和serial#
KGLPNLCK RAW ( 4 )
KGLPNCNT NUMBER
KGLPNMOD NUMBER
##如果值为3，表示为library cache pin的holder；如果值为0，表示为waiter
KGLPNREQ NUMBER
##如果值为0，表示为library cache pin的holder；如果值为2，表示为waiter
KGLPNDMK NUMBER
KGLPNSPN NUMBER
3.x$kglob——–[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
sys
@
ORALOCAL
(
192.168.0.22
)
>
desc
x
$
kglob
##主要用来查看library cache 的对象
Name Null ? Type
--------------------------------------------------- -- -------- ------------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KGLHDADR RAW ( 4 )
##关联v$session_wait中event为library cache pin的P1RAW ——也就是 KGL Handle address.
KGLHDPAR RAW ( 4 )
KGLHDCLT NUMBER
KGLNAOWN VARCHAR2 ( 64 )
##当前处于library cache pin的owner
KGLNAOBJ VARCHAR2 ( 1000 )
##当前处于library cache pin的对象
KGLNADLK VARCHAR2 ( 64 )
KGLNAHSH NUMBER
KGLNATIM DATE
KGLNAPTM DATE
KGLHDNSP NUMBER
KGLHDLMD NUMBER
KGLHDPMD NUMBER
KGLHDFLG NUMBER
KGLHDOBJ RAW ( 4 )
KGLHDLDC NUMBER
KGLHDIVC NUMBER
KGLHDEXC NUMBER
KGLHDLKC NUMBER
KGLHDKMK NUMBER
KGLHDDMK NUMBER
KGLHDAMK NUMBER
KGLOBFLG NUMBER
KGLOBSTA NUMBER
KGLOBTYP NUMBER
KGLOBHS0 NUMBER
KGLOBHS1 NUMBER
KGLOBHS2 NUMBER
KGLOBHS3 NUMBER
KGLOBHS4 NUMBER
KGLOBHS5 NUMBER
KGLOBHS6 NUMBER
KGLOBHS7 NUMBER
KGLOBHD0 RAW ( 4 )
KGLOBHD1 RAW ( 4 )
KGLOBHD2 RAW ( 4 )
KGLOBHD3 RAW ( 4 )
KGLOBHD4 RAW ( 4 )
KGLOBHD5 RAW ( 4 )
KGLOBHD6 RAW ( 4 )
KGLOBHD7 RAW ( 4 )
KGLOBPC0 NUMBER
KGLOBPC6 NUMBER
KGLOBTP0 RAW ( 4 )
KGLOBT00 NUMBER
KGLOBT01 NUMBER
KGLOBT02 NUMBER
KGLOBT04 NUMBER
KGLOBT05 NUMBER
KGLOBT06 NUMBER
KGLOBT07 NUMBER
KGLOBT08 NUMBER
KGLOBT09 NUMBER
KGLOBT10 NUMBER
KGLOBT11 NUMBER
KGLOBT12 NUMBER
KGLOBT13 NUMBER
KGLOBT14 NUMBER
KGLOBT15 NUMBER
KGLOBT16 NUMBER
KGLOBT17 NUMBER
KGLOBT18 NUMBER
KGLOBT19 NUMBER
KGLOBT20 NUMBER
KGLOBT21 NUMBER
KGLOBT22 NUMBER
KGLOBT23 NUMBER
KGLOBT24 NUMBER
KGLOBT25 NUMBER
KGLOBT26 NUMBER
KGLOBT28 NUMBER
KGLOBT29 NUMBER
KGLOBT30 NUMBER
KGLOBT31 NUMBER
KGLOBT27 NUMBER
KGLOBT32 NUMBER
KGLOBT33 NUMBER
KGLOBTL0 NUMBER
KGLOBTL1 NUMBER
KGLOBTS0 VARCHAR2 ( 64 )
KGLOBTS1 VARCHAR2 ( 64 )
KGLOBTN0 NUMBER
KGLOBTN1 NUMBER
KGLOBTN2 NUMBER
KGLOBTN3 NUMBER
KGLOBTN4 NUMBER
KGLOBTN5 NUMBER
KGLOBTS2 VARCHAR2 ( 64 )
KGLOBTT0 DATE
##主要用来查看library cache 的对象
Name Null ? Type
--------------------------------------------------- -- -------- ------------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KGLHDADR RAW ( 4 )
##关联v$session_wait中event为library cache pin的P1RAW ——也就是 KGL Handle address.
KGLHDPAR RAW ( 4 )
KGLHDCLT NUMBER
KGLNAOWN VARCHAR2 ( 64 )
##当前处于library cache pin的owner
KGLNAOBJ VARCHAR2 ( 1000 )
##当前处于library cache pin的对象
KGLNADLK VARCHAR2 ( 64 )
KGLNAHSH NUMBER
KGLNATIM DATE
KGLNAPTM DATE
KGLHDNSP NUMBER
KGLHDLMD NUMBER
KGLHDPMD NUMBER
KGLHDFLG NUMBER
KGLHDOBJ RAW ( 4 )
KGLHDLDC NUMBER
KGLHDIVC NUMBER
KGLHDEXC NUMBER
KGLHDLKC NUMBER
KGLHDKMK NUMBER
KGLHDDMK NUMBER
KGLHDAMK NUMBER
KGLOBFLG NUMBER
KGLOBSTA NUMBER
KGLOBTYP NUMBER
KGLOBHS0 NUMBER
KGLOBHS1 NUMBER
KGLOBHS2 NUMBER
KGLOBHS3 NUMBER
KGLOBHS4 NUMBER
KGLOBHS5 NUMBER
KGLOBHS6 NUMBER
KGLOBHS7 NUMBER
KGLOBHD0 RAW ( 4 )
KGLOBHD1 RAW ( 4 )
KGLOBHD2 RAW ( 4 )
KGLOBHD3 RAW ( 4 )
KGLOBHD4 RAW ( 4 )
KGLOBHD5 RAW ( 4 )
KGLOBHD6 RAW ( 4 )
KGLOBHD7 RAW ( 4 )
KGLOBPC0 NUMBER
KGLOBPC6 NUMBER
KGLOBTP0 RAW ( 4 )
KGLOBT00 NUMBER
KGLOBT01 NUMBER
KGLOBT02 NUMBER
KGLOBT04 NUMBER
KGLOBT05 NUMBER
KGLOBT06 NUMBER
KGLOBT07 NUMBER
KGLOBT08 NUMBER
KGLOBT09 NUMBER
KGLOBT10 NUMBER
KGLOBT11 NUMBER
KGLOBT12 NUMBER
KGLOBT13 NUMBER
KGLOBT14 NUMBER
KGLOBT15 NUMBER
KGLOBT16 NUMBER
KGLOBT17 NUMBER
KGLOBT18 NUMBER
KGLOBT19 NUMBER
KGLOBT20 NUMBER
KGLOBT21 NUMBER
KGLOBT22 NUMBER
KGLOBT23 NUMBER
KGLOBT24 NUMBER
KGLOBT25 NUMBER
KGLOBT26 NUMBER
KGLOBT28 NUMBER
KGLOBT29 NUMBER
KGLOBT30 NUMBER
KGLOBT31 NUMBER
KGLOBT27 NUMBER
KGLOBT32 NUMBER
KGLOBT33 NUMBER
KGLOBTL0 NUMBER
KGLOBTL1 NUMBER
KGLOBTS0 VARCHAR2 ( 64 )
KGLOBTS1 VARCHAR2 ( 64 )
KGLOBTN0 NUMBER
KGLOBTN1 NUMBER
KGLOBTN2 NUMBER
KGLOBTN3 NUMBER
KGLOBTN4 NUMBER
KGLOBTN5 NUMBER
KGLOBTS2 VARCHAR2 ( 64 )
KGLOBTT0 DATE
4.X$KSMSP——[K]ernal [S]torage [M]emory Management [S]GA Hea[P]
具体参照：http://blog.csdn.net/changyanmanman/article/details/8226522
sys
@
ORALOCAL
(
192.168.0.22
)
>
desc
X
$
KSMSP
## 主要用于查看shared pool中chunk的信息
Name Null ? Type
--------------------------------------------------- -- -------- ------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2 ( 16 )
KSMCHPTR RAW ( 4 )
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2 ( 8 )
##shared pool 的chunk的类型，如R-free，R-freea，free，freeabl，perm，recr
KSMCHTYP NUMBER
KSMCHPAR RAW ( 4 )
## 主要用于查看shared pool中chunk的信息
Name Null ? Type
--------------------------------------------------- -- -------- ------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2 ( 16 )
KSMCHPTR RAW ( 4 )
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2 ( 8 )
##shared pool 的chunk的类型，如R-free，R-freea，free，freeabl，perm，recr
KSMCHTYP NUMBER
KSMCHPAR RAW ( 4 )
5.X$KCCCP——[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress
sys
@
ORALOCAL
(
192.168.0.22
)
>
desc
X
$
KCCCP
##主要查看checkpoint的heartbeat
Name Null ? Type
--------------------------------------------------- -- -------- --------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
CPTNO NUMBER
CPSTA NUMBER
CPFLG NUMBER
CPDRT NUMBER
CPRDB NUMBER
CPLRBA_SEQ NUMBER
CPLRBA_BNO NUMBER
CPLRBA_BOF NUMBER
CPODR_SEQ NUMBER
CPODR_BNO NUMBER
CPODR_BOF NUMBER
CPODS VARCHAR2 ( 16 )
CPODT VARCHAR2 ( 20 )
CPODT_I NUMBER
CPHBT NUMBER
##checkpoint的心跳，每隔3秒变化一次，并且写入到控制文件中。
CPRLS VARCHAR2 ( 16 )
CPRLC NUMBER
CPMID NUMBER
CPSDR_SEQ NUMBER
CPSDR_BNO NUMBER
CPSDR_ADB NUMBER
##主要查看checkpoint的heartbeat
Name Null ? Type
--------------------------------------------------- -- -------- --------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
CPTNO NUMBER
CPSTA NUMBER
CPFLG NUMBER
CPDRT NUMBER
CPRDB NUMBER
CPLRBA_SEQ NUMBER
CPLRBA_BNO NUMBER
CPLRBA_BOF NUMBER
CPODR_SEQ NUMBER
CPODR_BNO NUMBER
CPODR_BOF NUMBER
CPODS VARCHAR2 ( 16 )
CPODT VARCHAR2 ( 20 )
CPODT_I NUMBER
CPHBT NUMBER
##checkpoint的心跳，每隔3秒变化一次，并且写入到控制文件中。
CPRLS VARCHAR2 ( 16 )
CPRLC NUMBER
CPMID NUMBER
CPSDR_SEQ NUMBER
CPSDR_BNO NUMBER
CPSDR_ADB NUMBER
6.X$KSMLRU——[K]ernal [S]torage [M]emory Management [LRU]
sys
@
ORALOCAL
(
192.168.0.22
)
>
desc
X
$
KSMLRU
##looking for the cause of memory allocations in the shared pool
Name Null ? Type
--------------------------------------------------- -- -------- ---------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KSMLRIDX NUMBER
KSMLRDUR NUMBER
KSMLRCOM VARCHAR2 ( 20 )
KSMLRSIZ NUMBER
KSMLRNUM NUMBER
##Number of items flushed from the shared pool
KSMLRHON VARCHAR2 ( 32 )
KSMLROHV NUMBER
KSMLRSES RAW ( 4 )
##Session performing the allocation.Join to V$SESSION.SADDR
##looking for the cause of memory allocations in the shared pool
Name Null ? Type
--------------------------------------------------- -- -------- ---------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KSMLRIDX NUMBER
KSMLRDUR NUMBER
KSMLRCOM VARCHAR2 ( 20 )
KSMLRSIZ NUMBER
KSMLRNUM NUMBER
##Number of items flushed from the shared pool
KSMLRHON VARCHAR2 ( 32 )
KSMLROHV NUMBER
KSMLRSES RAW ( 4 )
##Session performing the allocation.Join to V$SESSION.SADDR
7.X$KSQRS——[K]ernel [S]ervice en[Q]ueue [R]e[S]ource
sys
@
ORALOCAL
(
192.168.0.22
)
>
desc
X
$
KSQRS
##查找由于受到enqueue lock的数据库资源
sys @ ORALOCAL ( 10.1.19.13 ) > desc X $ KSQRS
名称 是否为空? 类型
--------------------------------------------------- -- -------- ----------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KSQRSID1 NUMBER
KSQRSID2 NUMBER
KSQRSIDT VARCHAR2 ( 2 )
KSQRSFLG NUMBER
##查找由于受到enqueue lock的数据库资源
sys @ ORALOCAL ( 10.1.19.13 ) > desc X $ KSQRS
名称 是否为空? 类型
--------------------------------------------------- -- -------- ----------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KSQRSID1 NUMBER
KSQRSID2 NUMBER
KSQRSIDT VARCHAR2 ( 2 )
KSQRSFLG NUMBER
8.X$KGLLK——[K]ernel [G]eneric [L]ibrary Cache Manager object [L]oc[K]s
SQL
>
desc
X
$
KGLLK
##主要用来查看library cache 的对象的锁
名称 是否为空? 类型
--------------------------------------- -- -------- ----------------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW ( 4 )
KGLLKUSE RAW ( 4 )
##关联v$session的saddr这一列，即session的地址。
KGLLKSES RAW ( 4 )
KGLLKSNM NUMBER
##关联v$session的sid
KGLLKHDL RAW ( 4 )
##关联v$session_wait的p1raw列，表明library cache lock的锁地址。也对应于x$kglob的kglhdadr这一列。
KGLLKPNC RAW ( 4 )
KGLLKPNS RAW ( 4 )
KGLLKCNT NUMBER
KGLLKMOD NUMBER
##代表的锁”占用”模式，0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’
KGLLKREQ NUMBER
##代表”请求”模式,0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’,另一个角度说明0->lock的占有者,其它->锁的请求者。
KGLLKFLG NUMBER
KGLLKSPN NUMBER
KGLLKHTB RAW ( 4 )
KGLNAHSH NUMBER
KGLLKSQLID VARCHAR2 ( 13 )
KGLHDPAR RAW ( 4 )
KGLHDNSP NUMBER
USER_NAME VARCHAR2 ( 30 )
KGLNAOBJ VARCHAR2 ( 60 )
##包含了在librarky cache中的对象上执行命令的语句的前80个字符。
##主要用来查看library cache 的对象的锁
名称 是否为空? 类型
--------------------------------------- -- -------- ----------------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW ( 4 )
KGLLKUSE RAW ( 4 )
##关联v$session的saddr这一列，即session的地址。
KGLLKSES RAW ( 4 )
KGLLKSNM NUMBER
##关联v$session的sid
KGLLKHDL RAW ( 4 )
##关联v$session_wait的p1raw列，表明library cache lock的锁地址。也对应于x$kglob的kglhdadr这一列。
KGLLKPNC RAW ( 4 )
KGLLKPNS RAW ( 4 )
KGLLKCNT NUMBER
KGLLKMOD NUMBER
##代表的锁”占用”模式，0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’
KGLLKREQ NUMBER
##代表”请求”模式,0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’,另一个角度说明0->lock的占有者,其它->锁的请求者。
KGLLKFLG NUMBER
KGLLKSPN NUMBER
KGLLKHTB RAW ( 4 )
KGLNAHSH NUMBER
KGLLKSQLID VARCHAR2 ( 13 )
KGLHDPAR RAW ( 4 )
KGLHDNSP NUMBER
USER_NAME VARCHAR2 ( 30 )
KGLNAOBJ VARCHAR2 ( 60 )
##包含了在librarky cache中的对象上执行命令的语句的前80个字符。
9.X$KTSSO——[K]ernal [T]ransaction [S]ort [S]egment
SQL
>
desc
X
$
KTSSO
##主要用来查看占据temp表空间的session sid
-- 9 i 的：
Name Null ? Type
--------------------------------------------------- -- -------- ------
ADDR RAW ( 8 )
INDX NUMBER
INST_ID NUMBER
KTSSOSES RAW ( 8 )
##对应v$session的saddr
KTSSOSNO NUMBER
KTSSOTSN VARCHAR2 ( 31 )
KTSSOCNT NUMBER
KTSSOSEGT NUMBER
##表示占据temp段的类型:1,SORT/2,HASH/ 3,DATA,/4,INDEX/5, LOB_DATA/6, LOB_INDEX/UNDEFINED
KTSSOFNO NUMBER
KTSSOBNO NUMBER
KTSSOEXTS NUMBER
KTSSOBLKS NUMBER
##占据的temp段的block数，注意如果是temporary table，也在此显示，显示结果也为TEMP
KTSSORFNO NUMBER
-- 10 g 的：
名称 是否为空? 类型
--------------------------------------- -- -------- ----------------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KTSSOSES RAW ( 4 )
KTSSOSNO NUMBER
KTSSOTSN VARCHAR2 ( 31 )
KTSSOCNT NUMBER
KTSSOSEGT NUMBER
KTSSOFNO NUMBER
KTSSOBNO NUMBER
KTSSOEXTS NUMBER
KTSSOBLKS NUMBER
KTSSORFNO NUMBER
KTSSOOBJD NUMBER
KTSSOOBJN NUMBER
##对应的dba_objects中的object id
KTSSOTSNUM NUMBER
##主要用来查看占据temp表空间的session sid
-- 9 i 的：
Name Null ? Type
--------------------------------------------------- -- -------- ------
ADDR RAW ( 8 )
INDX NUMBER
INST_ID NUMBER
KTSSOSES RAW ( 8 )
##对应v$session的saddr
KTSSOSNO NUMBER
KTSSOTSN VARCHAR2 ( 31 )
KTSSOCNT NUMBER
KTSSOSEGT NUMBER
##表示占据temp段的类型:1,SORT/2,HASH/ 3,DATA,/4,INDEX/5, LOB_DATA/6, LOB_INDEX/UNDEFINED
KTSSOFNO NUMBER
KTSSOBNO NUMBER
KTSSOEXTS NUMBER
KTSSOBLKS NUMBER
##占据的temp段的block数，注意如果是temporary table，也在此显示，显示结果也为TEMP
KTSSORFNO NUMBER
-- 10 g 的：
名称 是否为空? 类型
--------------------------------------- -- -------- ----------------------------
ADDR RAW ( 4 )
INDX NUMBER
INST_ID NUMBER
KTSSOSES RAW ( 4 )
KTSSOSNO NUMBER
KTSSOTSN VARCHAR2 ( 31 )
KTSSOCNT NUMBER
KTSSOSEGT NUMBER
KTSSOFNO NUMBER
KTSSOBNO NUMBER
KTSSOEXTS NUMBER
KTSSOBLKS NUMBER
KTSSORFNO NUMBER
KTSSOOBJD NUMBER
KTSSOOBJN NUMBER
##对应的dba_objects中的object id
KTSSOTSNUM NUMBER
附：X$表命名规则：
** INTERNAL ONLY **
This is a summary list of X$ Table Definitions - Last revision was 7.3.2
The main purpose of this note is to show the naming conventions.
[K]ernel Layer
[2]-Phase Commit
[G]lobal [T]ransaction [E]ntry
X$K2GTE - Current 2PC tx
X$K2GTE2 - Current 2PC tx
[C]ache Layer
[B]uffer Management
Buffer [H]ash
X$BH - Hash Table
Buffer LRU Statistics
X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics
X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended
Buffer [WAIT]s
X$KCBWAIT - Waits by block class
X$KCBFWAIT - Waits by File
[W]orking Sets - 7.3 or higher
X$KCBWDS - Set [D]escriptors
[C]ontrol File Management
[C]ontrol [F]ile List - 7.0.16 or higher
X$KCCCF - Control File Names & status
[D]atabase [I]nformation
X$KCCDI - Database Information
Data [F]iles
X$KCCFE - File [E]ntries ( from control file )
X$KCCFN - [F]ile [N]ames
[L]og Files
X$KCCLE - Log File [E]ntries
X$KCCLH - Log [H]istory ( archive entries )
Thread Information
X$KCCRT - [R]edo [T]hread Information
[F]ile Management
X$KCFIO - File [IO] Statistics
[L]ock Manager Component ( LCK )
[H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher
X$KCLFH - File [H]ash Table
X$KCLFI - File Bucket Table
X$LE - Lock [E]lements
X$LE_STAT - Lock Conversion [STAT]istics
X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher
X$KCLLS - Per LCK free list statistics - 7.3 or higher
X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher
[R]edo Component
[M]edia recovery - kcra.h - 7.3 or higher
X$KCRMF - [F]ile context
X$KCRMT - [T]hread context
X$KCRMX - Recovery Conte[X]t
[F]ile read
X$KCRFX - File Read Conte[X]t - 7.3 or higher
Reco[V]ery Component
[F]ile [H]eaders
X$KCVFH - All file headers
X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired
X$KCVFHONL - [ONL]ine File headers
[K]ompatibility Management - 7.1.1 or higher
X$KCKCE - [C]ompatibility Segment [E]ntries
X$KCKTY - Compatibility [TY]pes
X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE )
[D]ata Layer
Sequence [N]umber Component
X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower
[S]equence Enqueues - common area for enqueue objects
X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower
X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower
X$KDNST - Cache [ST]atistics - 7.2 or lower
Inde[X] Block Component
X$KDXHS - Index [H]i[S]togram
X$KDXST - Index [ST]atistics
[G]eneric Layer
[H]eap Manager
X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h
[I]nstantiation Manager
[C]ursor [C]ache
X$KGICC - Session statistics - defined in kqlf.h
X$KGICS - System wide statistics - defined in kqlf.h
[L]ibrary Cache Manager ( defined and mapped from kqlf )
Bind Variables
X$KKSBV - Library Object [B]ind [V]ariables
Object Cache
X$KGLOB - All [OB]jects
X$KGLTABLE - Filter for [TABLE]s
X$KGLBODY - Filter for [BODY] ( packages )
X$KGLTRIGGER - Filter for [TRIGGER]s
X$KGLINDEX - Filter for [INDEX]es
X$KGLCLUSTER - Filter for [CLUSTER]s
X$KGLCURSOR - Filter for [CURSOR]s
Cache Dependency
X$KGLDP - Object [D]e[P]endency table
X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher
Object Locks
X$KGLLK - Object [L]oc[K]s
Object Names
X$KGLNA - Object [NA]mes (sql text)
X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher
Object Pins
X$KGLPN - Object [P]i[N]s
Cache Statistics
X$KGLST - Library cache [ST]atistics
Translation Table
X$KGLTR - Address [TR]anslation
Access Table
X$KGLXS - Object Access Table
Authorization Table - 7.1.5 or higher
X$KGLAU - Object Authorization table
Latch Cleanup - 7.0.15 or higher
X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches
[K]ompile Layer
[S]hared Objects
X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher
[L]oader
[L]ibrary
X$KLLCNT - [C]o[NT]rol Statistics
X$KLLTAB - [TAB]le Statistics
[M]ulti-Threaded Layer
[C]ircuit component
X$KMCQS - Current [Q]ueue [S]tate
X$KMCVC - [V]irtual [C]ircuit state
[M]onitor Server/dispatcher
[D]ispatcher
X$KMMDI - [D]ispatcher [I]nfo (status)
X$KMMDP - [D]ispatcher Config ( [P]rotocol info )
[S]erver
X$KMMSI - [S]erver [I]nfo ( status )
X$KMMSG - [SG]a info ( global statistics)
X$KMMRD - [R]equest timing [D]istributions
s[Q]l Version and Option Layer
Kernel [V]ersions
X$VERSION - Library versions
Kernel [O]ptions - 7.1.3 or higher
X$OPTION - Server Options
[Q]uery Layer
[D]ictionary Cache Management
X$KQDPG - [PG]a row cache cursor statistics
[F]ixed Tables/views Management
X$KQFCO - Table [CO]lumn definitions
X$KQFDT - [D]erived [T]ables
X$KQFSZ - Kernel Data structure type [S]i[Z]es
X$KQFTA - Fixed [TA]bles
X$KQFVI - Fixed [VI]ews
X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher
[R]ow Cache Management
X$KQRST - Cache [ST]atistics
X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher
X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher
[S]ervice Layer
[B]ackground Management
[D]etached Process
X$KSBDD - Detached Process [D]efinition (info)
X$KSBDP - Detached [P]rocess Descriptor (name)
X$MESSAGES - Background Message table
[I]nstance [M]anagement - 7.3 or higher
X$KSIMAT - Instance [AT]tributes
X$KSIMAV - [A]ttribute [V]alues for all instances
X$KSIMSI - [S]erial and [I]nstance numbers
[L]ock Management
[E]vent Waits
X$KSLED - Event [D]escriptors
X$KSLEI - [I]nstance wide statistics since startup
X$KSLES - Current [S]ession statistics
[L]atches
X$KSLLD - Latch [D]escriptor (name)
X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ]
X$KSLLW - Latch context ( [W]here ) descriptors - 7.3 or higher
X$KSLPO - Latch [PO]st statistics - 7.3 or higher
X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3 or higher
[M]emory Management
[C]ontext areas
X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower
Heap Areas
X$KSMSP - SGA Hea[P] （http://blog.csdn.net/changyanmanman/article/details/8226522）
X$KSMPP - [P]GA Hea[P] - 7.3.2 and above
X$KSMUP - [U]GA Hea[P] - 7.3.2 and above
X$KSMHP - Any [H]ea[P] - 7.3.2 and above
X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher
[L]east recently used shared pool chunks
X$KSMLRU - LR[U] flushes from the shared pool
[S]GA Objects
X$KSMSD - Size [D]efinition for Fixed/Variable summary
X$KSMSS - Statistics (lengths) of SGA objects
SGA [MEM]ory
X$KSMMEM - map of the entire SGA - 7.2.0 or higher
X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1 or higher
[P]arameter Component
X$KSPPI - [P]arameter [I]nfo ( Names )
This is a summary list of X$ Table Definitions - Last revision was 7.3.2
The main purpose of this note is to show the naming conventions.
[K]ernel Layer
[2]-Phase Commit
[G]lobal [T]ransaction [E]ntry
X$K2GTE - Current 2PC tx
X$K2GTE2 - Current 2PC tx
[C]ache Layer
[B]uffer Management
Buffer [H]ash
X$BH - Hash Table
Buffer LRU Statistics
X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics
X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended
Buffer [WAIT]s
X$KCBWAIT - Waits by block class
X$KCBFWAIT - Waits by File
[W]orking Sets - 7.3 or higher
X$KCBWDS - Set [D]escriptors
[C]ontrol File Management
[C]ontrol [F]ile List - 7.0.16 or higher
X$KCCCF - Control File Names & status
[D]atabase [I]nformation
X$KCCDI - Database Information
Data [F]iles
X$KCCFE - File [E]ntries ( from control file )
X$KCCFN - [F]ile [N]ames
[L]og Files
X$KCCLE - Log File [E]ntries
X$KCCLH - Log [H]istory ( archive entries )
Thread Information
X$KCCRT - [R]edo [T]hread Information
[F]ile Management
X$KCFIO - File [IO] Statistics
[L]ock Manager Component ( LCK )
[H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher
X$KCLFH - File [H]ash Table
X$KCLFI - File Bucket Table
X$LE - Lock [E]lements
X$LE_STAT - Lock Conversion [STAT]istics
X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher
X$KCLLS - Per LCK free list statistics - 7.3 or higher
X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher
[R]edo Component
[M]edia recovery - kcra.h - 7.3 or higher
X$KCRMF - [F]ile context
X$KCRMT - [T]hread context
X$KCRMX - Recovery Conte[X]t
[F]ile read
X$KCRFX - File Read Conte[X]t - 7.3 or higher
Reco[V]ery Component
[F]ile [H]eaders
X$KCVFH - All file headers
X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired
X$KCVFHONL - [ONL]ine File headers
[K]ompatibility Management - 7.1.1 or higher
X$KCKCE - [C]ompatibility Segment [E]ntries
X$KCKTY - Compatibility [TY]pes
X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE )
[D]ata Layer
Sequence [N]umber Component
X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower
[S]equence Enqueues - common area for enqueue objects
X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower
X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower
X$KDNST - Cache [ST]atistics - 7.2 or lower
Inde[X] Block Component
X$KDXHS - Index [H]i[S]togram
X$KDXST - Index [ST]atistics
[G]eneric Layer
[H]eap Manager
X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h
[I]nstantiation Manager
[C]ursor [C]ache
X$KGICC - Session statistics - defined in kqlf.h
X$KGICS - System wide statistics - defined in kqlf.h
[L]ibrary Cache Manager ( defined and mapped from kqlf )
Bind Variables
X$KKSBV - Library Object [B]ind [V]ariables
Object Cache
X$KGLOB - All [OB]jects
X$KGLTABLE - Filter for [TABLE]s
X$KGLBODY - Filter for [BODY] ( packages )
X$KGLTRIGGER - Filter for [TRIGGER]s
X$KGLINDEX - Filter for [INDEX]es
X$KGLCLUSTER - Filter for [CLUSTER]s
X$KGLCURSOR - Filter for [CURSOR]s
Cache Dependency
X$KGLDP - Object [D]e[P]endency table
X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher
Object Locks
X$KGLLK - Object [L]oc[K]s
Object Names
X$KGLNA - Object [NA]mes (sql text)
X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher
Object Pins
X$KGLPN - Object [P]i[N]s
Cache Statistics
X$KGLST - Library cache [ST]atistics
Translation Table
X$KGLTR - Address [TR]anslation
Access Table
X$KGLXS - Object Access Table
Authorization Table - 7.1.5 or higher
X$KGLAU - Object Authorization table
Latch Cleanup - 7.0.15 or higher
X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches
[K]ompile Layer
[S]hared Objects
X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher
[L]oader
[L]ibrary
X$KLLCNT - [C]o[NT]rol Statistics
X$KLLTAB - [TAB]le Statistics
[M]ulti-Threaded Layer
[C]ircuit component
X$KMCQS - Current [Q]ueue [S]tate
X$KMCVC - [V]irtual [C]ircuit state
[M]onitor Server/dispatcher
[D]ispatcher
X$KMMDI - [D]ispatcher [I]nfo (status)
X$KMMDP - [D]ispatcher Config ( [P]rotocol info )
[S]erver
X$KMMSI - [S]erver [I]nfo ( status )
X$KMMSG - [SG]a info ( global statistics)
X$KMMRD - [R]equest timing [D]istributions
s[Q]l Version and Option Layer
Kernel [V]ersions
X$VERSION - Library versions
Kernel [O]ptions - 7.1.3 or higher
X$OPTION - Server Options
[Q]uery Layer
[D]ictionary Cache Management
X$KQDPG - [PG]a row cache cursor statistics
[F]ixed Tables/views Management
X$KQFCO - Table [CO]lumn definitions
X$KQFDT - [D]erived [T]ables
X$KQFSZ - Kernel Data structure type [S]i[Z]es
X$KQFTA - Fixed [TA]bles
X$KQFVI - Fixed [VI]ews
X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher
[R]ow Cache Management
X$KQRST - Cache [ST]atistics
X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher
X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher
[S]ervice Layer
[B]ackground Management
[D]etached Process
X$KSBDD - Detached Process [D]efinition (info)
X$KSBDP - Detached [P]rocess Descriptor (name)
X$MESSAGES - Background Message table
[I]nstance [M]anagement - 7.3 or higher
X$KSIMAT - Instance [AT]tributes
X$KSIMAV - [A]ttribute [V]alues for all instances
X$KSIMSI - [S]erial and [I]nstance numbers
[L]ock Management
[E]vent Waits
X$KSLED - Event [D]escriptors
X$KSLEI - [I]nstance wide statistics since startup
X$KSLES - Current [S]ession statistics
[L]atches
X$KSLLD - Latch [D]escriptor (name)
X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ]
X$KSLLW - Latch context ( [W]here ) descriptors - 7.3 or higher
X$KSLPO - Latch [PO]st statistics - 7.3 or higher
X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3 or higher
[M]emory Management
[C]ontext areas
X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower
Heap Areas
X$KSMSP - SGA Hea[P] （http://blog.csdn.net/changyanmanman/article/details/8226522）
X$KSMPP - [P]GA Hea[P] - 7.3.2 and above
X$KSMUP - [U]GA Hea[P] - 7.3.2 and above
X$KSMHP - Any [H]ea[P] - 7.3.2 and above
X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher
[L]east recently used shared pool chunks
X$KSMLRU - LR[U] flushes from the shared pool
[S]GA Objects
X$KSMSD - Size [D]efinition for Fixed/Variable summary
X$KSMSS - Statistics (lengths) of SGA objects
SGA [MEM]ory
X$KSMMEM - map of the entire SGA - 7.2.0 or higher
X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1 or higher
[P]arameter Component
X$KSPPI - [P]arameter [I]nfo ( Names )
X$KSPPCV
- [C]urrent Session [V]alues - 7.3.2 or above
X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above
X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above
oracle数据库的初始化参数， 主要来源于两个oracle内部的数据字典表：x$ksppi 和 x$ksppcv . 通常我们查询的v$parameter 视图的结果就是来源于这两个表，只不过隐去了部分结果。
通过一个脚本可以查看这些被隐藏的参数： http://blog.csdn.net/changyanmanman/article/details/7899039
En[Q]ueue Management
X$KSQDN - Global [D]atabase [N]ame
X$KSQEQ - [E]n[Q]ueue Object
X$KSQRS - Enqueue [R]e[S]ource
X$KSQST - Enqueue [S]tatistics by [T]ype
[U]ser Management
[C]ost
X$KSUCF - Cost [F]unction (resource limit)
[L]icence
X$KSULL - Licence [L]imits
[L]anguage Manager
X$NLS_PARAMETERS - NLS parameters
X$KSULV - NLS [V]alid Values - 7.1.2 or higher
[MY] [ST]atistics
X$KSUMYSTA - [MY] [ST]atisics (current session)
[P]rocess Info
X$KSUPL - Process (resource) [L]imits
X$KSUPRLAT - [LAT]ch Holder
X$KSUPR - Process object
[R]esource
X$KSURU - Resource [U]sage
[S]tatistics
X$KSUSD - [D]escriptors (statistic names)
X$KSUSGSTA - [G]lobal [ST]atistics
[SE]ssions
X$KSUSECST - Session status for events
X$KSUSESTA - Session [STA]tistics
X$KSUSECON - [CON]nection Authentication - 7.2.1 or higher
X$KSUSE - [SE]ssion Info
X$KSUSIO - [S]ystem [IO] statistics per session
[T]imer
X$KSUTM - Ti[M]e in 1/100th seconds
Instance [X]
X$KSUXSINST - [INST]ance state
[T]race management
X$TRACE - Current traced events
X$TRACES - All possible traces
X$KSTEX - Code [EX]ecution - 7.2.1 or higher
E[X]ecution Management
Device/Node [A]ffinity - 7.3.2 and above
X$KSXAFA - Current File/Node Affinity
[T]ransaction Layer
Table [A]ccess [D]efinition
X$KTADM - D[M]L lock
[C]ontrol Component
X$KTCXB - Transaction O[B]ject
[S]or[T] Segments - 7.3 or higher
X$KTSTSSD - [S]ort [S]egment [D]escriptor - per tablespace statistics
[T]ablespace
X$KTTVS - [V]alid [S]aveundo
[U]ndo
X$KTURD - Inuse [D]escriptors
X$KTUXE - Transaction [E]ntry (table) - 7.3.2 or above
Performance Layer [V] - 7.0.16 or higher
[I]nformation tables
X$KVII - [I]nitialisation Instance parameters
X$KVIS - [S]izes of structure elements
X$KVIT - [T]ransitory Instance parameters
Security Layer [Z]
[D]ictionary Component
X$KZDOS - [OS] roles
[S]ecurity State
X$KZSPR - Enabled [PR]ivileges
X$KZSRO - Enabled [RO]les
[R]emote Logins - 7.1.1 or higher
X$KZSRT - [R]emote Password File [T]able entries
E[X]ecution Layer
Parallel Query (Execute [F]ast) - 7.1.1 or higher
[P]rocess and Queue Manager
Statistics - 7.1.3 or higher
X$KXFPYS - S[YS]tem Statistics
X$KXFPDP - [D]etached [P]rocess (slave) statistics
X$KXFQSROW - Table [Q]ueue Statistics - 7.3.2 or higher
[C]oordinator Component
X$KXFPCST - Query [ST]atistics
X$KXFPCMS - [M]essage [S]tatistics
X$KXFPCDS - [D]equeue [S]tatistics
[S]lave Component
X$KXFPSST - Query [ST]atistics
X$KXFPSMS - [M]essage [S]tatistics
X$KXFPCDS - [D]equeue [S]tatistics
[S]hared Cursor
X$KXSBD - [B]ind [D]ata - 7.3.2 and above
X$KXSCC - SQL [C]ursor [C]ache Data - 7.3.2 and above
[N]etwork Layer - 7.0.15 or higher
Network [CO]nnections
X$UGANCO - Current [N]etwork [CO]nnections
.
2 条评论 »
-
Rill 于 2008-06-05 @ 10:58:20 留言 ：
我是老宋，给你补充一个哈：
X$KGLLK——[K]ernel [G]eneric [L]ibrary Cache Manager object [L]oc[K]s
##主要用来查看library cache 的对象的锁
KGLLKHDL RAW(4)
##关联v$session_wait的p1raw列，表明library cache lock的锁地址。也对应于x$kglob的kglhdadr这一列。
KGLLKSNM RAW(4)
##关联v$session的sid
KGLLKUSE RAW(4)
##关联v$session的saddr这一列，即session的地址。
KGLNAOBJ RAW(4)
##包含了在librarky cache中的对象上执行命令的语句的前80个字符。
KGLLKMOD RAW(4)
##代表的锁”占用”模式，0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’
KGLLKREP RAW(4)
##代表”请求”模式,0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’,另一个角度说明0->lock的占有者,其它->锁的请求者。
