資料字典學習筆記

N年前寫的文檔，呵呵，還是貼過來。

前言

我們在日常工作中，常常要對Oracle資料字典進行查詢，但是很多時候，我們在進行查詢的同僚卻對資料字典的知識不是很了解，這幾天我在複習Oracle基礎知識的時候，又浏覽一遍資料字典的概念，現在把一些相關知識記錄下來，友善大家一起總結學習。

2.    資料字典的概念

資料字典是Oracle資料庫的一個重要的組成部分，Oracle通過使用資料字典記錄和管理對象資訊和安全資訊等，使用者和資料庫系統管理者可以通過資料字典來擷取資料庫的相關資訊。

       資料字典包括以下内容：

l           所有資料庫schema對象的定義（表、視圖、索引、聚簇、同義詞、序列、過程、函數、包、觸發器等）；

l           資料庫的空間配置設定和使用情況；

l           字段的預設值；

l           完整性限制資訊；

l           Oracle使用者名稱、角色、權限等資訊；

l           審計資訊；

l           其他資料庫資訊。

一般來說，資料字典都是隻讀的，通常不建議對任何資料字典表進行修改操作。

通常我們所說的資料字典由4部分組成：内部RDBMS(X$)表、資料字典表、動态性能(v$)視圖和資料字典視圖,下面我們對這4部分分别進行下講解。

3.    内部RDBMS(x$)表

    X$表是oracle資料庫的核心部分，這些表用于跟蹤内部資料庫資料，維護資料庫的正常運作。Oracle通過這些X$建立起其他大量視圖，提供給使用者查詢管理資料庫之用，也就是說，我們查詢的動态視圖、資料字典視圖等都是通過這些内部表來實作的。

X$表是oracle資料庫的運作基礎，在資料庫啟動時由oracle應用程式動态建立。這部分表對資料庫來說至關重要，是以oracle不允許sysdba之外的使用者直接通路，顯示授權不允許。

    我們可以通過設定autotrace功能發現構成這些視圖的底層表，如下我們最常見的v$session視圖的跟蹤資訊；

SQL> set autotrace trace explain

SQL> select * from v$session;

執行計劃

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

Plan hash value: 3733760267

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

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

| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)

| Time     |

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

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

|   0 | SELECT STATEMENT         |                 |     1 |  1065 |     0   (0)

| 00:00:01 |

|   1 |  NESTED LOOPS            |                 |     1 |  1065 |     0   (0)

| 00:00:01 |

|*  2 |   FIXED TABLE FULL       | X$KSUSE         |     1 |   856 |     0   (0)

| 00:00:01 |

|*  3 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |   209 |     0   (0)

| 00:00:01 |

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

    通過跟蹤，我們可以知道構成這個視圖的低層表為X$KSUSE，另外我所知道比較有用的還有X$KSPPI表，該表存放的是隐含參數的資訊，不過X$表oracle不作文檔說明，這部分的知識屬于oracle公司的技術機密,有興趣的同僚可以多研究下。

4.    資料字典表

    資料字典表用以存儲表、索引、限制以及其他資料庫結構的資訊。這些對象通常以“$”結尾（如tab$、obj$、ts$等），這些表都是通過運作sql.bsq腳本來建立的。

    sql.bsq檔案裡面包含了這些資料字典表的定義和注釋說明，這個我們接觸得不多，在這裡就不多做研究，有興趣的可以打開sql.bsq檔案看下就知道了，該檔案存放在$ORACLE_HOME/RDBMS/ADMIN目錄下。

5.    動态性能視圖

    動态性能視圖記錄了資料庫運作時資訊和統計資料，大部分動态性能視圖被實時更新及反映資料庫的目前狀态。

Oracle 通過動态性能視圖将oracle資料庫的狀态展示出來提供給使用者和資料庫管理者，是以其是我們研究和管理資料庫的重要依據。

動态視圖有GV$和V$兩種視圖，其中GV$視圖的含義是global V$視圖，它是為了滿足ops環境需要，每個V$視圖都有個GV$視圖，GV$視圖是在V$視圖的基礎上，多了個INST_ID列，我們以RAC環境為例說明下大家就明白了。

SQL> select inst_id,instance_name,status,version

  2  from gv$instance;

   INST_ID INSTANCE_NAME    STATUS       VERSION

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

         1 orcl1             OPEN         10.2.0.1.0

         2 orcl2             OPEN         10.2.0.1.0

    可以看到GV$視圖它包含了2個執行個體的資訊，其中inst_id字段儲存的是每個節點的情況，oracle提供了一些特殊的視圖用以記錄其他視圖的建立方式。V$fixed_view_definition就是其中一個，我們來查詢下剛才哪個gv$instance視圖的建立情況

SQL> select * from v_$fixed_view_definition t

  2  Where view_name ='GV$INSTANCE'

  3  /

VIEW_NAME                      VIEW_DEFINITION

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

GV$INSTANCE                    select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksux

sver,ksuxstim,decode(ksuxssts,0,'STARTED',1,'MOUNTED',2,'OPEN',3,'OPEN MIGRATE',

'UNKNOWN'),decode(ksuxsshr,0,'NO',1,'YES',2,NULL),ksuxsthr,decode(ksuxsarc,0,'ST

OPPED',1,'STARTED','FAILED'),decode(ksuxslsw,0,NULL,2,'ARCHIVE LOG',3,'CLEAR LOG

',4,'CHECKPOINT',       5,'REDO GENERATION'),decode(ksuxsdba,0,'ALLOWED','RESTRI

CTED'),decode(ksuxsshp,0,'NO','YES'),decode(kvitval,0,'ACTIVE',2147483647,'SUSPE

NDED','INSTANCE RECOVERY'),decode(ksuxsrol,1,'PRIMARY_INSTANCE',2,'SECONDARY_INS

TANCE','UNKNOWN'), decode(qui_state,0,'NORMAL',1,'QUIESCING',2,'QUIESCED','UNKNO

WN'), decode(bitand(ksuxsdst, 1), 0, 'NO', 1, 'YES', 'NO') from x$ksuxsinst ks,

x$kvit kv, x$quiesce qu where kvittag = 'kcbwst'

SQL>

    我們可以看到GV$INSTANCE視圖是由x$ksuxsinst ,x$kvit , x$quiesce三個内部x$表建構成的，同樣的道理，我們來查詢V$INSTANCE視圖，

SQL> select * from v_$fixed_view_definition t

  2  Where view_name ='V$INSTANCE'

  3  /

VIEW_NAME                      VIEW_DEFINITION

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

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

V$INSTANCE                     select  INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSION , STARTUP_TIME , STATUS , PARALLEL , THREAD# , ARCHIVER , LOG_SWITCH_WAIT , LOGINS , SHUTDOWN_PENDING, DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE, BLOCKED from GV$INSTANCE where inst_id = USERENV('Instance')

我們可以清楚的看到，其實V$INSTANCE就是查詢GV$INSTANCE視圖，然後多了個條件inst_id = USERENV('Instance')，再次證明了前面說的每個V$視圖都有個GV$視圖。

    另外，oracle不允許任何對v$視圖的授權，這點有同僚會問了那為什麼我們可以對v$session授予查詢的權限呢？其實，oracle在建立GV$和V$視圖後，還建立了GV_$和V_$視圖，随後為這些視圖建立了公共同義詞，這些我們可以通過catalog.sql腳本檢視。

以下是從catalog.sql腳本中摘要的一些資訊：

create or replace view v_$bgprocess as select * from v$bgprocess;

create or replace public synonym v$bgprocess for v_$bgprocess;

grant select on v_$bgprocess to select_catalog_role;

create or replace view v_$session as select * from v$session;

create or replace public synonym v$session for v_$session;

grant select on v_$session to select_catalog_role;

create or replace view v_$license as select * from v$license;

create or replace public synonym v$license for v_$license;

grant select on v_$license to select_catalog_role;

    可以看到，我們授予普通使用者select 視圖v$session的權限，其實是授予的v_$session的視圖的權限，這下大家就明白了，實際上大部分對象通路的v$對象，并不是視圖，而是v_$視圖的同義詞，而v_$視圖是基于v$視圖建立的。

一些常用的動态性能視圖如下：

v$controlfile：控制檔案的資訊；

v$datafile：資料檔案的資訊；

v$log： 日志檔案的資訊；

v$process：處理器的資訊；

v$session：會話資訊；

v$transaction：事務資訊；

v$resource：資源資訊；

v$sga：系統全局區的資訊

6.    資料字典視圖

    資料字典視圖是在X$表和資料字典表之上建立的視圖。

    按照字首的不同，資料字典視圖可以分為3類：

l           User_類視圖：包括了使用者所擁有的相關對象資訊。

l           All_類視圖：包含了使用者有權限通路的所有對象的資訊。

l           DBA_類視圖：包含了資料庫所有對象的資訊。

其實我們可以以這些視圖建立結構就可以看到這些視圖的差別，我們以user_tables、all_tables、dba_tables來具體的看下，首先是user_tables,其結構如下：

create or replace view user_tables

(table_name, tablespace_name, cluster_name, iot_name, status, pct_free, pct_used, ini_trans,

       decode(bitand(o.flags, 128), 128, 'YES', 'NO')

……

from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,

     sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi

where o.owner# = userenv('SCHEMAID')

  and o.obj# = t.obj#

……

  and cx.owner# = cu.user# (+)

  and ksppi.indx = ksppcv.indx

  and ksppi.ksppinm = '_dml_monitoring_enabled'

注意到了where條件下有這樣一個限制：

where o.owner# = userenv('SCHEMAID')

    這就限制了目前查詢隻傳回目前使用者的SCHEMA對象資訊。

    而對于all_tables視圖，在where子句中，關于使用者部分的限制為：

  and (o.owner# = userenv('SCHEMAID')

       or o.obj# in

            (select oa.obj#

             from sys.objauth$ oa

             where grantee# in ( select kzsrorol

                                 from x$kzsro

                               )

            )

       or

         exists (select null from v$enabledprivs

                 where priv_number in (-45 ,

                                       -47 ,

                                       -48 ,

                                       -49 ,

                                       -50 )

                 )

      )

這個條件擴充了關于使用者有權限通路的對象資訊，是以我們看出來，實際上user_tables的結果是all_tables結果的一個子集。

最後，我們來看一下dba_tables視圖的where條件，其資訊如下：

where o.owner# = u.user#

  and o.obj# = t.obj#

  and bitand(t.property, 1) = 0

  and bitand(o.flags, 128) = 0

  and t.bobj# = co.obj# (+)

  and t.ts# = ts.ts#

  and t.file# = s.file# (+)

  and t.block# = s.block# (+)

  and t.ts# = s.ts# (+)

  and t.dataobj# = cx.obj# (+)

  and cx.owner# = cu.user# (+)

  and ksppi.indx = ksppcv.indx

  and ksppi.ksppinm = '_dml_monitoring_enabled'

可以看出，沒有對于owner的限制，是以查詢出來的結果是資料庫中所有表的資訊，這就是這3類資料字典視圖的差別所在。

最後我們來列出一些常用的資料字典視圖：

　　表和列

　　DBA_TABLES、ALL_TABLES和USER_TABLES顯示了有關資料庫表的一般資訊。

　　DBA_TAB_COLUMNS、ALL_TAB_COLUMNS和USER_TAB_COLUMNS顯示了每個資料庫表的列的資訊。

注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS顯示了模式對象的資訊，包括表。

　　視圖

　　DBA_VIEWS、ALL_VIEWS和USER_VIEWS。

注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS顯示了模式對象的資訊，包括視圖。

　　同義詞

　　DBA_SYNONYMS、ALL_SYNONYMS和USER_SYNONYMS。

注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS顯示了模式對象的資訊，包括同義詞。

　　索引

　　DBA_INDEXS、ALL_INDEXS、USER_INDEXS、DBA_IND_COLUMNS、ALL_IND_COLUMNS和USER_IND_COLUMNS。

7.    總結

       以上就是對oracle資料字典的一些總結，不過有些地方可能還比較模糊，大家如果有興趣，可以進一步深入的研究學習，掌握這些知識對以後進行資料庫維護是很有幫助的。