Oracle查詢資料表結構(欄位,型別,大小,備註)

shilei1發表於2014-06-03

作用:想要生成整個Oracle資料庫所有表結構WORD文件(資料庫設計說明書)

 

Oracle資料庫字典介紹
    Oracle資料字典是有表和檢視組成的,儲存有關資料庫結構資訊的一些資料庫物件。資料庫字典描述了實際資料是如何組織的。對它們可以象處理其他資料庫表或檢視一樣進行查詢,但不能進行任何修改。
    Oracle資料庫字典通常是在建立和安裝資料庫時被建立的,Oracle資料字典是Oracle資料庫系統工作的基礎,沒有資料字典的支援,Oracle資料庫系統就不能進行任何工作。
    在Oracle資料庫字典中,許多檢視都有三個不同的例項,它們的字首分別為"USER_"、"ALL_"及"DBA_"。"USER_"為字首的資料庫字典檢視通常記錄執行查詢的帳戶所擁有的物件的資訊,"ALL_"為字首的資料庫字典檢視通常記錄包括執行查詢的帳戶所擁有的物件的資訊及授權至PUBLIC的帳戶使用者所擁有的物件的資訊,"DBA_"為字首的資料庫字典檢視則包含所有資料庫物件的資訊,而不管其所有者。其他的字典檢視中主要的是V$檢視,之所以這樣叫是因為他們都是以V$或GV$開頭的。V$檢視是基於X$虛擬檢視的。V$檢視是SYS使用者所擁有的,在預設狀況下,只有SYS使用者和擁有DBA系統許可權的使用者可以看到所有的檢視,沒有DBA許可權的使用者可以看到USER_和ALL_檢視,但不能看到DBA_檢視。與DBA_,ALL,和USER_檢視中面向資料庫資訊相反,這些檢視可視的給出了面向例項的資訊。
    在Oracle的絕大多數資料字典檢視中都有象DBA_TABLES,ALL_TABLES和USER_TABLES這樣的檢視家族。Oracle中有超過100個檢視家族,下表列出了最重要和最常用的檢視家族,需要注意的是每個檢視家族都有一個DBA_,一個ALL_一個USER_檢視。

檢視家族

描述

COL_PRIVS

包含了表的列許可權,包括授予者、被授予者和許可權

EXTENTS

資料範圍資訊,比如資料檔案,資料段名(segment_name)和大小

INDEXES

索引資訊,比如型別、唯一性和被涉及的表

IND_COLUMNS

索引列資訊,比如索引上的列的排序方式

OBJECTS

物件資訊,比如狀態和DDL time

ROLE_PRIVS

角色許可權,比如GRANT和ADMIN選項

SEGMENTS

表和索引的資料段資訊,比如tablespace和storage

SEQUECNCES

序列資訊,比如序列的cache、cycle和ast_number

SOURCE

除觸發器之外的所有內建過程、函式、包的原始碼

SYNONYMS

別名資訊,比如引用的物件和資料庫連結db_link

SYS_PRIVS

系統許可權,比如grantee、privilege、admin選項

TAB_COLUMNS

表和檢視的列資訊,包括列的資料型別

TAB_PRIVS

表許可權,比如授予者、被授予者和許可權

TABLES

表資訊,比如表空間(tablespace),儲存引數(storage parms)和資料行的數量

TRIGGERS

觸發器資訊,比如型別、事件、觸發體(trigger body)

USERS

使用者資訊,比如臨時的和預設的表空間

VIEWS

檢視資訊,包括檢視定義


    在Oracle中還有一些不常用的資料字典表,但這些表不是真正的字典家族,他們都是一些重要的單一的檢視。這些檢視見下表:

檢視名稱

描述

USER_COL_PRIVS_MADE

使用者授予他人的列許可權

USER_COL_PRIVS_RECD

使用者獲得的列許可權

USER_TAB_PRIVS_MADE

使用者授予他人的表許可權

USER_TAB_PRIVS_RECD

使用者獲得的表許可權

Oracle資料庫字典的應用
  
藉助Oracle資料字典,利用Oracle的DDL語句,我們可以做很多事情,幾乎所有的Oracle開發輔助工具都是利用這一點進行設計的。作者將透過如何取得資料庫表欄位資訊來說明。
首先我們定義一個資料庫表,資料庫表結構如下:

資料庫表名[TABLE_TEST]

欄位名

資料型別

長度

預設值

允許空

主鍵

註釋

NAME

VARCHAR2

40

 

N

Y

姓名

SEX

VARCHAR2

1

''''Y''''

N

 

性別

BIRTHDAY

DATE

0

 

Y

 

生日

HEIGHT

NUMBER

3,1

 

Y

 

身高

WEIGHT

NUMBER

3,1

 

Y

 

體重

MEMO

BLOB

0

 

Y

 

備註

建立表的SQL語句如下
-- 建立資料表
create table TABLE_TEST
(
    NAME varchar2(40) not null,
    SEX varchar2(1) default ''''Y'''' not null,
    BIRTHDAY date not null,
    HEIGHT number(3,2),
    WEIGHT number(3,2),
    MEMO blob
);
-- 給列新增備註
  comment on column TABLE_TEST.NAME is ''''姓名
'''';
  comment on column TABLE_TEST.SEX is ''''
性別
'''';
  comment on column TABLE_TEST.BIRTHDAY is ''''
生日
'''';
  comment on column TABLE_TEST.HEIGHT is ''''
身高
'''';
  comment on column TABLE_TEST.WEIGHT is ''''
體重
'''';
  comment on column TABLE_TEST.MEMO is ''''
備註'''';
-- 建立約束關係 主鍵 外來鍵 其他
  alter table TABLE_TEST add constraint TB_TEST_P_NAME primary key (NAME);

資料表建立完畢,執行下列SQL語句:
select
    A.column_name 
欄位名,A.data_type 資料型別,A.data_length 長度,A.data_precision 整數位
,
    A.Data_Scale 
小數位,A.nullable 允許空值,A.Data_default 預設值,B.comments 
備註
from
    user_tab_columns A,user_col_comments B
where
    A.Table_Name = B.Table_Name
    and A.Column_Name = B.Column_Name
    and A.Table_Name = ''''TABLE_TEST''''

我們可以得出一下結果:

欄位名
資料型別
長度
整數位
小數位
允許空值
預設值
備註
NAME
VARCHAR2
40
 
 
N
姓名
SEX
VARCHAR2
1
 
 
N
性別
BIRTHDAY
DATE
7
 
 
N
生日
HEIGHT
NUMBER
22
3
2
Y
身高
WEIGHT
NUMBER
22
3
2
Y
體重
MEMO
BLOB
4000
 
 
Y
備註

這樣,我們在進行程式設計時,透過一條簡單的SQL語句,然好透過Ole呼叫Word,便可為終端使用者匯出完整的資料庫表字典文件。
再執行下列SQL語句:
select
    INDEX_NAME 
索引名,INDEX_TYPE 索引型別,UNIQUENESS 
索引類別
from
    user_indexes
where
    TABLE_NAME = ''''TABLE_TEST''''

得到結果如下(注:SYS_IL0000031226C00006$$索引為系統在建立資料庫表時自動建立的,用於資料庫表內容的維護):

 
索引名
索引型別
索引類別
1
SYS_IL0000031226C00006$$
LOB
UNIQUE
2
TB_TEST_P_NAME
NORMAL
UNIQUE

 

執行下列SQL語句,我們將得到更多的關於資料庫表結構的資訊:
select
    A.column_name 
欄位名,A.data_type 資料型別,A.data_length 長度,A.data_precision 整數位
,
    A.Data_Scale 
小數位,A.nullable 允許空值,A.Data_default 預設值,B.comments 備註
,
    C.IndexCount 
索引次數
from
    user_tab_columns A,
    user_col_comments B,
    (select count(*) IndexCount,Column_Name from User_Ind_Columns where Table_Name = ''''TABLE_TEST'''' group by Column_Name) C
where
    A.Table_Name = B.Table_Name
    and A.Column_Name = B.Column_Name
    and A.Column_Name = C.Column_Name(+)
    and A.Table_Name = ''''TABLE_TEST''''

得到結果如下:

欄位名
資料型別
長度
整數位
小數位
允許空值
預設值
備註
索引次數
BIRTHDAY
DATE
7
 
 
N
生日
 
HEIGHT
NUMBER
22
3
2
Y
身高
 
MEMO
BLOB
4000
 
 
Y
備註
 
NAME
VARCHAR2
40
 
 
N
姓名
1
SEX
VARCHAR2
1
 
 
N
性別
 
WEIGHT
NUMBER
22
3
2
Y
體重
 

 

    當然Oracle資料字典的應用遠不止這些,透過Oracle資料庫字典的支援,我們可以得到Oracle資料庫結構的所有資訊,著名的資料庫開發工具PL/SQL Developer完全就是基於Oracle的資料庫字典實現的。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/196700/viewspace-1174693/,如需轉載,請註明出處,否則將追究法律責任。

相關文章