講解Oracle數(shù)據(jù)庫中的數(shù)據(jù)字典及相關SQL查詢用法

Oracle數(shù)據(jù)字典概述 
 
數(shù)據(jù)庫是數(shù)據(jù)的集合，數(shù)據(jù)庫維護和管理這用戶的數(shù)據(jù)，那么這些用戶數(shù)據(jù)表都存在哪里，用戶的信息是怎樣的，存儲這些用戶的數(shù)據(jù)的路徑在哪里，這些信息不屬于用戶的信息，卻是數(shù)據(jù)庫維護和管理用戶數(shù)據(jù)的核心，這些信息就是數(shù)據(jù)庫的數(shù)據(jù)字典來維護的，數(shù)據(jù)庫的數(shù)據(jù)字典就匯集了這些數(shù)據(jù)庫運行所需要的基礎信息叻。每個數(shù)據(jù)庫都提供了各自的數(shù)據(jù)字典的方案，雖然形式不同，但是目的和作用是一樣的，比如在mysql里數(shù)據(jù)字典是在information_schema 里表現(xiàn)的，sqlserver則是在sys這個系統(tǒng)schema里來展示的。
Oracle的數(shù)據(jù)字典是Oracle數(shù)據(jù)庫安裝之后，自動創(chuàng)建的一系列數(shù)據(jù)庫對象。數(shù)據(jù)字典是Oracle數(shù)據(jù)庫對象結(jié)構(gòu)的元數(shù)據(jù)信息。熟悉和深入研究數(shù)據(jù)字典對象，可以很大程度的幫助我們了解Oracle內(nèi)部機制。
Oracle字典視圖包括四個層次，分別為X$內(nèi)部表、基礎數(shù)據(jù)字典表、數(shù)據(jù)字典視圖和動態(tài)性能視圖。
數(shù)據(jù)字典表和用戶創(chuàng)建的表沒有什么區(qū)別，不過數(shù)據(jù)字典表里的數(shù)據(jù)是Oracle系統(tǒng)存放的系統(tǒng)數(shù)據(jù)，而普通表存放的是用戶的數(shù)據(jù)而已，為了方便的區(qū)別這些表，這些表的名字都是用"$"結(jié)尾，在我們看到的sql語句里看到"$"結(jié)尾的這些表，大家就可以想到這可能是一個數(shù)據(jù)字典表了，同樣，既然如此，我們創(chuàng)建自己的用戶表的時候就不要用“$”結(jié)尾，以免讓別人誤會，數(shù)據(jù)字典表是系統(tǒng)存放系統(tǒng)用戶的，所以他的owner是sys，在手工用create database的命令的時候，會調(diào)用$ORACLE_HOME/rdbms/admin/sql.bsq文件，這個就會執(zhí)行生成我們這些數(shù)據(jù)字典表。打開sql.bsq會發(fā)現(xiàn)很多數(shù)據(jù)字典幾乎都以$結(jié)尾，比如col$,tab$等。
對于數(shù)據(jù)字典表，里面的數(shù)據(jù)是有數(shù)據(jù)庫系統(tǒng)自身來維護的，所以這里雖然和普通表一樣可以用DML語句來修改數(shù)據(jù)內(nèi)容，但是大家最好還是不要自己來做了，因為這些表都是作用于數(shù)據(jù)庫內(nèi)部的，我們有時看到的604的的recursive sql這里的sql實際上很有可能就是我們的相關的數(shù)據(jù)字典表的執(zhí)行內(nèi)容。所以這里我們切記記住不要去修改這些表里的內(nèi)容。
剛才說道了。這里的數(shù)據(jù)字典表的用戶都是sys，存在在system這個表空間里，表名都用"$"結(jié)尾，為了便于用戶對數(shù)據(jù)字典表的查詢，這樣的名字是不利于我們記憶的，所以Oracle對這些數(shù)據(jù)字典都分別建立了用戶視圖視圖，不僅有更容易接受的名字，還隱藏了數(shù)據(jù)字典表表之間的關系，讓我們字節(jié)通過視圖來進行查詢，簡單而形象，Oracle針對這些對象的范圍，分別把視圖命名為DBA_XXXX, ALL_XXXX和USER_XXXX
user_對象視圖：描述了當前用戶schema下的對象；
all_對象視圖：描述了當前用戶有權(quán)限訪問到的所有對象的信息；
dba_對象視圖：包括了所有數(shù)據(jù)庫對象的信息；
注意：在創(chuàng)建數(shù)據(jù)庫是會運行兩個腳本。先運行catalog.sql，該腳本用來創(chuàng)建數(shù)據(jù)庫的內(nèi)部字典表。然后再運行catrpoc.sql，該腳本用來創(chuàng)建數(shù)據(jù)庫內(nèi)建的存儲過程、包等pl\sql對象。如果我們是使用dbca來創(chuàng)建數(shù)據(jù)庫，則dbca會自動調(diào)用這兩個腳本。否則在執(zhí)行create database命令來創(chuàng)建時，則需要手工運行這兩個腳本。    
 
數(shù)據(jù)字典SQL查詢
下面按類別列出一些ORACLE用戶常用數(shù)據(jù)字典的查詢使用方法。
 
1、用戶
查看當前用戶的缺省表空間

SQL>select username,default_tablespace from user_users;

 
查看當前用戶的角色

SQL>select * from user_role_privs;

 
查看當前用戶的系統(tǒng)權(quán)限和表級權(quán)限

SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;

 
2、表
查看用戶下所有的表

SQL>select * from user_tables;

 
查看名稱包含log字符的表

SQL>select object_name,object_id from user_objects
where instr(object_name,'LOG')>0;

 
查看某表的創(chuàng)建時間

SQL>select object_name,created from user_objects where object_name=upper('table_name');

 
查看某表的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('table_name');

 
查看放在ORACLE的內(nèi)存區(qū)里的表

SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

 
3、索引
查看索引個數(shù)和類別

SQL>select index_name,index_type,table_name from user_indexes order by table_name;

 
查看索引被索引的字段

SQL>select * from user_ind_columns where index_name=upper('index_name');

 
查看索引的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('index_name');

 
4、序列號
查看序列號，last_number是當前值

SQL>select * from user_sequences;

 
5、視圖
查看視圖的名稱

SQL>select view_name from user_views;

 
查看創(chuàng)建視圖的select語句

SQL>set view_name,text_length from user_views;
SQL>set long 2000;        說明：可以根據(jù)視圖的text_length值設定set long 的大小
SQL>select text from user_views where view_name=upper('view_name');

 
6、同義詞
查看同義詞的名稱

SQL>select * from user_synonyms;

 
7、約束條件
查看某表的約束條件

SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('table_name');
 
SQL>select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner = upper('table_owner') and c.table_name = upper('table_name')
and c.owner = cc.owner and c.constraint_name = cc.constraint_name
order by cc.position;

 
8、存儲函數(shù)和過程
查看函數(shù)和過程的狀態(tài)

SQL>select object_name,status from user_objects where object_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';

 
查看函數(shù)和過程的源代碼

SQL>select text from all_source where owner=user and name=upper('plsql_name');