Oracle中tablesinfo_Oracle X$Tables | 學步園

前言

最早從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