前言
最早從Yong Huang那裡看到關於比較詳細的X$表的介紹,後來陸續從其他Oracle專家那裡得到了不少信息。在Steve Adams 的書中對X$表多有提及,而且他的站點也是個資源比較豐富的地方。不過在中文Oracle
Oracle 數據庫引擎是個複雜無比的系統,隨着每個新版本的推出,Oracle都會有不少新功能新特性加入其中,越發變得複雜。從X$表的數量上可見一斑:
Oracle 816 有265 個 X$ 表
Oracle 901 有352 個 X$ 表
Oracle 920 有394 個 X$ 表
Oracle 10g 有543 個 X$ 表
可以用如下腳本進行基本查看X$ 表的基本信息:
SELECT NAME x$table_name
FROM v$fixed_table
WHERE NAME LIKE 'X$%'
ORDER BY NAME;
V$fixed_table中可以查到所有的X$TABLE的名字。其值等於:X$KQFTA+X$KQFDT這兩個表中X$Table的數量。也可以通過 Unix 的 strings 命令抽取 oracle 命令里的 X$ 表的信息。
$ strings $ORACLE_HOME/bin/oracle >xtable.sql
然後查看 xtables.sql 文件的內容。相關站點:
http://www.dbanotes.net/X$TABLES/xtable.sql
http://www.orafaq.com/faqdbain.htm
http://www.stormloader.com/yonghuang/computer/x$table.html
http://www.adp-gmbh.ch/ora/misc/x.html
http://www.geocities.com/kgkrish/ http://www.oracle-training.cc/oracle_tips_x$.htm
注釋
一些特定的縮寫的含義
X$KS - Kernel Services
X$KSL - Kernel Service Latch
X$KC - Kernel Cache
X$KQ - Kernel Query
X$KQ - Kernel Generic
X$LE - Lock Element
X$KZ - Kernel Security
Table_Name
Meanings
Comments
V$ Views / Scripts
X$ABSTRACT_LOB
LOB's ABSTRACT
X$ACTIVECKPT
ACTIVE Check Point
X$ASH
Active Session History
X$BH
Buffer Header
Buffer Cache
中所有塊的當前狀態
從State列可得到緩衝區狀態值表:
0,'free' 空閑
1,'xcur' 獨佔
2,'scur' 並發共享
3,'cr' 一致性讀取
4,'read' 讀取
5,'mrec' MediaRecovery
6,'irec' InstanceRecovery
7,'write' 寫入
8,'pi', Pinned
9,'memory' 內存
10,'mwrite' 內存寫
11,'donated' ?
This is a very useful internal view on the buffer cache headers for database blocks in the buffer cache.
GV$BH / V$BH
腳本:
查詢當前X$BH狀態的腳本
X$BUFFER
X$BUFFERED_PUBLISHERS
GV$BUFFERED_PUBLISHERS
&
V$BUFFERED_PUBLISHERS
X$BUFFERED_QUEUES
X$BUFFERED_SUBSCRIBERS
X$CKPTBUF
Check Point Buffer
X$CONTEXT
X$DUAL
DUAL table
SYS.STANDARD需要用到X$DUAL,
X$ESTIMATED_MTTR
X$GLOBALCONTEXT
X$HOFP
X$HS_SESSION
X$INSTANCE_CACHE_TRANSFER
X$JOXFC
X$JOXFD
X$JOXFM
X$JOXFR
X$JOXFS
X$JOXFT
X$JSKJOBQ
X$JSKSLV
X$K2GTE
X$K2GTE2
X$KAUVRSTAT
X$KCBBES
X$KCBBF
X$KCBBHS
X$KCBFWAIT
X$KCBKPFS
X$KCBKWRL
X$KCBLDRHIST
X$KCBLSC
X$KCBMMAV
X$KCBOBH
X$KCBOQH
X$KCBSC
X$KCBSDS
X$KCBSH
X$KCBSW
X$KCBVBL
X$KCBWAIT
X$KCBWBPD
X$KCBWDS
X$KCBWH
X$KCCAGF
X$KCCAL
X$KCCBF
X$KCCBI
X$KCCBL
X$KCCBP
X$KCCBS
X$KCCCC
X$KCCCF
Kernel Cache Controlfile management ControlFile name
X$KCCCP
X$KCCDC
X$KCCDFHIST
X$KCCDI
MAXLOGMEMBERS is available via x$kccdi.dimlm
X$KCCDI2
X$KCCDL
X$KCCFC
X$KCCFE
X$KCCFLE
X$KCCFN
X$KCCIC
X$KCCIRT
X$KCCLE
X$KCCLH
X$KCCOR
X$KCCPA
X$KCCPD
X$KCCRDI
X$KCCRM
X$KCCRS
X$KCCRSR
X$KCCRT
X$KCCSL
X$KCCTF
X$KCCTIR
X$KCCTS
X$KCFIO
X$KCFIOHIST
X$KCFTIO
X$KCFTIOHIST
X$KCKCE
X$KCKFM
X$KCKTY
X$KCLCRST
X$KCLCURST
X$KCLFH
X$KCLFI
X$KCLFX
X$KCLLS
X$KCLQN
X$KCLRCVST
X$KCPXPL
X$KCRFDEBUG
X$KCRFSTRAND
X$KCRFWS
X$KCRFX
X$KCRMF
X$KCRMT
X$KCRMX
X$KCRRALG
X$KCRRARCH
X$KCRRDEST
X$KCRRDGC
X$KCRRDSTAT
X$KCRRLNS
X$KCRRMS
X$KCTICW
X$KCTLAX
X$KCVFH
X$KCVFHALL
X$KCVFHMRR
X$KCVFHONL
X$KCVFHTMP
X$KDLT
X$KDNSSF
X$KDXHS
X$KDXST
X$KEACMDN
X$KEAOBJT
X$KEHECLMAP
X$KEHEVTMAP
X$KEHF
X$KEHOSMAP
X$KEHPRMMAP
X$KEHR
X$KEHRP
X$KEHR_CHILD
X$KEHSQT
X$KEHSYSMAP
X$KEHTIMMAP
X$KELRTD
X$KELTGSD
X$KELTOSD
X$KELTSD
X$KEWAM
X$KEWASH
X$KEWECLS
X$KEWEFXT
X$KEWESMAS
X$KEWESMS
X$KEWMAFMV
X$KEWMDRMV
X$KEWMDSM
X$KEWMEVMV
X$KEWMFLMV
X$KEWMGSM
X$KEWMRSM
X$KEWMRWMV
X$KEWMSEMV
X$KEWMSMDV
X$KEWMSVCMV
X$KEWRSQLIDTAB
X$KEWRTB
X$KEWRTSEGSTAT
X$KEWRTSQLSTAT
X$KEWSSESV
X$KEWSSMAP
X$KEWSSVCV
X$KEWSSYSV
X$KEWXOCF
X$KEWX_LOBS
X$KEWX_SEGMENTS
X$KFALS
X$KFDAT
X$KFDPARTNER
X$KFDSK
X$KFFIL
X$KFFXP
X$KFGMG
X$KFGRP
X$KFKID
X$KFNCL
X$KFTMTA
X$KGHLU
X$KGICC
X$KGICS
X$KGLAU
X$KGLBODY
X$KGLCLUSTER
X$KGLCURSOR
X$KGLDP
X$KGLINDEX
X$KGLJMEM
X$KGLJSIM
X$KGLLC
X$KGLLK
X$KGLMEM
X$KGLNA
X$KGLNA1
X$KGLOB
X$KGLPN
X$KGLRD
X$KGLSIM
X$KGLSN
X$KGLST
X$KGLTABLE
X$KGLTR
X$KGLTRIGGER
X$KGLXS
X$KGSKASP
X$KGSKCFT
X$KGSKCP
X$KGSKDOPP
X$KGSKPFT
X$KGSKPP
X$KGSKQUEP
X$KGSKTE
X$KGSKTO
X$KJBL
X$KJBLFX
X$KJBR
X$KJBRFX
X$KJCTFR
X$KJCTFRI
X$KJCTFS
X$KJDRHV
X$KJDRMAFNSTATS
X$KJDRMHVSTATS
X$KJDRMREQ
X$KJDRPCMHV
X$KJDRPCMPF
X$KJICVT
X$KJILFT
X$KJILKFT
X$KJIRFT
X$KJISFT
X$KJITRFT
X$KJMDDP
X$KJMSDP
X$KJXM
X$KKSAI
X$KKSBV
X$KKSCS
Kernel Kompile Shared Cursor Sharing
X$KKSSRD
X$KLCIE
X$KLPT
X$KMCQS
X$KMCVC
X$KMGSCT
X$KMGSOP
X$KMMDI
X$KMMDP
X$KMMRD
X$KMMSG
X$KMMSI
X$KNGFL
X$KNSTACR
X$KNSTASL
X$KNSTCAP
X$KNSTMVR
X$KNSTRPP
X$KNSTRQU
X$KOCST
X$KQDPG
X$KQFCO
X$KQFDT
X$KQFP
X$KQFSZ
X$KQFTA
X$KQFVI
X$KQFVT
X$KQLFBC
X$KQLFSQCE
X$KQLFXPL
X$KQLSET
X$KQRFP
X$KQRFS
X$KQRPD
X$KQRSD
X$KQRST
X$KRBAFF
X$KRBMROT
X$KRBMRST
X$KRBMSFT
X$KRCBIT
X$KRCCDE
X$KRCCDR
X$KRCCDS
X$KRCEXT
X$KRCFBH
X$KRCFDE
X$KRCFH
X$KRCGFE
X$KRCSTAT
X$KRFBLOG
X$KRFGSTAT
X$KRVSLV
X$KRVSLVS
X$KRVXDKA
X$KRVXSV
X$KSBDD
X$KSBDP
X$KSBTABACT
X$KSFMCOMPL
X$KSFMELEM
X$KSFMEXTELEM
X$KSFMFILE
X$KSFMFILEEXT
X$KSFMIOST
X$KSFMLIB
X$KSFMSUBELEM
X$KSFQDVNT
X$KSFQP
X$KSFVQST
X$KSFVSL
X$KSFVSTA
X$KSIMAT
X$KSIMAV
X$KSIMSI
X$KSIRESTYP
X$KSLCS
X$KSLECLASS
X$KSLED
X$KSLEI
X$KSLEMAP
X$KSLES
X$KSLLCLASS
X$KSLLD
X$KSLLT
X$KSLLW
X$KSLPO
X$KSLSCS
X$KSLSESHIST
X$KSLWSC
X$KSMDD
X$KSMDUT1
X$KSMFS
X$KSMFSV
X$KSMGE
X$KSMHP
X$KSMJCH
X$KSMJS
X$KSMLRU
X$KSMLS
X$KSMMEM
X$KSMNIM
X$KSMNS
X$KSMPP
X$KSMSD
X$KSMSGMEM
X$KSMSP
Kernel Service Memory in Shared Pool
X$KSMSPR
X$KSMSP_DSNEW
X$KSMSP_NWEX
X$KSMSS
X$KSMSTRS
X$KSMUP
X$KSOLSFTS
X$KSOLSSTAT
X$KSPPCV
X$KSPPCV2
X$KSPPI
Kernel Service, Parameter, Parameter Info
X$KSPPO
X$KSPPSV
X$KSPPSV2
X$KSPSPFH
X$KSPSPFILE
X$KSPXFR
X$KSQDN
X$KSQEQ
X$KSQEQTYP
X$KSQRS
X$KSQST
X$KSRCCTX
X$KSRCDES
X$KSRCHDL
X$KSRMPCTX
X$KSRMSGDES
X$KSRMSGO
X$KSTEX
X$KSUCF
X$KSUCPUSTAT
X$KSULL
X$KSULOP
X$KSULV
X$KSUMYSTA
X$KSUPGP
X$KSUPGS
X$KSUPL
X$KSUPR
Kernel Services User Process
V$PROCESS is based on GV$PROCESS;
GV$PROCESS is based on X$KSUPR
X$KSUPRLAT
X$KSURLMT
X$KSURU
X$KSUSD
X$KSUSE
X$KSUSECON
X$KSUSECST
X$KSUSEH
X$KSUSESTA
X$KSUSEX
X$KSUSGIF
X$KSUSGSTA
X$KSUSIO
X$KSUTM
X$KSUVMSTAT
X$KSUXSINST
X$KSWSASTAB
X$KSWSCLSTAB
X$KSWSEVTAB
X$KSXAFA
X$KSXPIA
X$KSXRCH
X$KSXRCONQ
X$KSXRMSG
X$KSXRREPQ
X$KSXRSG
X$KTADM
X$KTCSP
X$KTCXB
X$KTFBFE
X$KTFBHC
X$KTFBUE
X$KTFTHC
X$KTFTME
X$KTIFB
X$KTIFF
X$KTIFP
X$KTIFV
X$KTPRHIST
X$KTPRXRS
X$KTPRXRT
X$KTRSO
X$KTSKSTAT
X$KTSPSTAT
X$KTSSO
X$KTSTFC
X$KTSTSSD
X$KTTEFINFO
X$KTTVS
X$KTUGD
X$KTUQQRY
X$KTURD
X$KTURHIST
X$KTUSMST
X$KTUSMST2
X$KTUXE
Kernel Transaction Undo Transaxtion Entry
This view holds entry for each active undo slot.
To see any DEAD transactions for deferred transaction recovery after startup:
SELECT *
FROM x$ktuxe
WHERE ktuxecfl = 'DEAD';
X$KUPVA
X$KUPVJ
X$KVII
X$KVIS
SELECT kvisval, kvisdsc
FROM x$kvis;
X$KVIT
X$KWDDEF
X$KWQBPMT
X$KWQPD
X$KWQPS
X$KWQSI
X$KWRSNV
X$KXFPCDS
X$KXFPCMS
X$KXFPCST
X$KXFPDP
X$KXFPNS
X$KXFPPFT
X$KXFPSDS
X$KXFPSMS
X$KXFPSST
X$KXFPYS
X$KXFQSROW
X$KXSBD
X$KXSCC
X$KZDOS
X$KZEMAEA
X$KZEMAIE
X$KZRTPD
X$KZSPR
X$KZSRO
X$KZSRT
X$LCR
X$LE
X$LOGMNR_ATTRIBUTE$
X$LOGMNR_CALLBACK
X$LOGMNR_COL$
X$LOGMNR_COLTYPE$
X$LOGMNR_CONTENTS
X$LOGMNR_DICT$
X$LOGMNR_DICTIONARY
X$LOGMNR_DICTIONARY_LOAD
X$LOGMNR_ENCRYPTED_OBJ$
X$LOGMNR_ENCRYPTION_PROFILE$
X$LOGMNR_IND$
X$LOGMNR_INDPART$
X$LOGMNR_LATCH
X$LOGMNR_LOGFILE
X$LOGMNR_LOGS
X$LOGMNR_OBJ$
X$LOGMNR_PARAMETERS
X$LOGMNR_PROCESS
X$LOGMNR_REGION
X$LOGMNR_ROOT$
X$LOGMNR_SESSION
X$LOGMNR_TAB$
X$LOGMNR_TABCOMPART$
X$LOGMNR_TABPART$
X$LOGMNR_TABSUBPART$
X$LOGMNR_TRANSACTION
X$LOGMNR_TS$
X$LOGMNR_TYPE$
X$LOGMNR_USER$
X$MESSAGES
X$NLS_PARAMETERS
X$NSV
X$OPTION
X$PRMSLTYX
X$QESMMAHIST
X$QESMMAPADV
X$QESMMIWH
X$QESMMIWT
X$QESMMSGA
X$QESRSTAT
X$QESRSTATALL
X$QKSCESES
X$QKSCESYS
X$QKSMMWDS
X$QUIESCE
X$RFMP
X$RFMTE
X$SKGXPIA
X$TARGETRBA
X$TEMPORARY_LOB_REFCNT
X$TIMEZONE_FILE
X$TIMEZONE_NAMES
X$TRACE
X$TRACE_EVENTS
X$UGANCO
X$VERSION
X$VINST
X$XSAGGR
X$XSAGOP
X$XSAWSO
X$XSLONGOPS
X$XSOBJECT
X$XSOQMEHI
X$XSOQOJHI
X$XSOQOPHI
X$XSOQOPLU
X$XSOQSEHI
X$XSSINFO