Oracle 常用腳本之查看數(shù)據(jù)表、字段、約束、索引等信息

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        數(shù)據(jù)表
            列出數(shù)據(jù)庫中的表
            列出指定用戶的表
            通過表名查找特定的表
            查看表的創(chuàng)建/修改時(shí)間
        字段
            查看指定表中的字段
            查看指定表中的虛擬/隱藏字段
            查找包含指定字段的表
        約束
            查找指定表上的主鍵約束
            查找主鍵約束對應(yīng)的字段
            查找缺少主鍵約束的表
            查找指定表上的唯一約束
            查找指定表上的外鍵約束
            查找外鍵約束中涉及的表和字段
            檢查約束
            非空約束
            默認(rèn)值
        索引
            查找指定表上的索引
            查找索引對應(yīng)的字段
        模式比較
            比較兩個(gè)模式中的表和字段
            比較兩個(gè)表中的數(shù)據(jù)差異

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。前文介紹了如何查看 Oracle 中的用戶、權(quán)限、用戶資源配置等信息，今天我們來繼續(xù)討論如何通過 Oracle 數(shù)據(jù)字典查看數(shù)據(jù)庫中的表、字段、約束、索引等信息。
數(shù)據(jù)表
列出數(shù)據(jù)庫中的表

系統(tǒng)視圖 dba_tables 中包含了數(shù)據(jù)庫中所有的關(guān)系表信息，需要 DBA 權(quán)限才能查看：

select owner, table_name, tablespace_name, status, num_rows, avg_row_len
from dba_tables
where owner in ( -- 非系統(tǒng)模式用戶
    select username
    from dba_users
    where oracle_maintained = 'N');



其中，

    OWNER 是表的擁有者；
    TABLE_NAME 是表名；
    TABLESPACE_NAME 是表所在的表空間；
    STATUS 是表的狀態(tài)，VALID 或者 UNUSABLE；
    NUM_ROWS 是表中的數(shù)據(jù)行數(shù)，通過表的統(tǒng)計(jì)分析獲得，可能不準(zhǔn)確；
    AVG_ROW_LEN 是數(shù)據(jù)行的平均長度，單位為字節(jié)。

    ??關(guān)于視圖 dba_tables 更多字段的說明可以參考官方文檔。

另外，我們也可以通過 all_tables 視圖查看當(dāng)前用戶可以訪問所有表，或者使用 user_tables 視圖查看當(dāng)前用戶擁有的所有表。
列出指定用戶的表

通過以下語句可以查詢指定用戶擁有的表：

select owner, table_name, tablespace_name, status, num_rows, avg_row_len
from dba_tables
where owner = :user_name; -- 用戶名



如果想要查看當(dāng)前登錄用戶擁有的表，也可以直接查詢 user_tables 視圖：

select table_name, tablespace_name, status, num_rows, avg_row_len
from user_tables;



注意，user_tables 視圖沒有 OWNER 字段。
通過表名查找特定的表

以下語句可以用于查找名字中包含字符串“EMP”的所有表：

select owner, table_name, tablespace_name, status, num_rows, avg_row_len
from dba_tables
where table_name like '%EMP%';


使用 not like 運(yùn)算符可以執(zhí)行相反查找，使用 regexp_like 可以執(zhí)行正則表達(dá)式查找。
查看表的創(chuàng)建/修改時(shí)間

系統(tǒng)視圖 dba_objects、all_objects 以及 user_objects 中包含了各種對象（表、索引、視圖、觸發(fā)器、程序包等）的信息，可以用于查詢對象的創(chuàng)建時(shí)間和最后一次修改時(shí)間。例如：

select t.owner, t.table_name, o.created, o.last_ddl_time
from dba_tables t
join dba_objects o on (o.owner = t.owner and o.object_name = t.table_name)
where t.owner = :user_name -- 用戶名
and t.table_name = :table_name; -- 表名



字段
查看指定表中的字段

系統(tǒng)視圖 dba_tab_columns、all_tab_columns 以及 user_tab_columns 包含了表、視圖以及聚簇表中的字段信息。我們可以使用以下語句查看指定表中的字段：

select owner, table_name, column_name, column_id, data_type,
       data_length, data_precision, data_scale, nullable, data_default
from dba_tab_columns
where owner = :user_name -- 用戶名
and table_name = :table_name; -- 表名



其中，

    OWNER 是表、視圖、聚簇表的擁有者；
    TABLE_NAME 是表、視圖、聚簇表的名字；
    COLUMN_NAME 是字段名；
    COLUMN_ID 是字段的編號；
    DATA_TYPE 是字段的數(shù)據(jù)類型，包括 NUMBER、CHAR、VARCHAR2、DATE、TIMESTAMP 等；
    DATA_LENGTH 是字段的長度，單位為字節(jié)；
    DATA_PRECISION 是 NUMBER 數(shù)據(jù)類型的精度或 FLOAT 類型的二進(jìn)制精度；
    DATA_SCALE 是數(shù)字類型的小數(shù)點(diǎn)位數(shù)；
    NULLABLE 表示字段是否可空。

查看指定表中的虛擬/隱藏字段

Oracle 11g 增加了虛擬列（Virtual Column）的支持，Oracle 12c 增加了隱藏列（Invisible Column）的支持。這些字段的信息需要通過名字更短的視圖 dba_tab_cols、all_tab_cols 以及 user_tab_cols 進(jìn)行查看。例如：

select owner, table_name, column_name, column_id, data_type,
       data_length, data_precision, data_scale, nullable, data_default
from dba_tab_cols
where owner = :user_name -- 用戶名
and table_name = :table_name -- 表名
and (virtual_column = 'Y' or hidden_column = 'Y');



對于虛擬列，data_default 字段是計(jì)算字段值的表達(dá)式。
查找包含指定字段的表

以下查詢返回了包含指定字段的數(shù)據(jù)表：

select owner, table_name
from dba_tab_cols
where column_name = :column_name; -- 字段名



同樣也可以通過 like 運(yùn)算符進(jìn)行模糊查找。
約束
查找指定表上的主鍵約束

視圖 dba_constraints、all_constraints 以及 user_constraints 包含了關(guān)于表中約束的定義信息。

以下查詢返回了指定表上的主鍵：

select owner, table_name, constraint_name, status, deferrable, deferred, validated, index_name, invalid
from dba_constraints
where owner = :user_name -- 用戶名
and table_name = :table_name -- 表名
and constraint_type = 'P';



其中，

    OWNER 是約束的擁有者；
    TABLE_NAME 是主鍵所在的表名；
    CONSTRAINT_NAME 是約束名；
    STATUS 是約束的狀態(tài)，ENABLED 或者 DISABLED；
    DEFERRABLE 表示約束是否可以延遲驗(yàn)證，DEFERRABLE 或者 NOT DEFERRABLE；
    DEFERRED 表示約束是延遲驗(yàn)證（DEFERRED）或者立即驗(yàn)證（IMMEDIATE）；
    VALIDATED 表示啟用約束是是否對已有的數(shù)據(jù)進(jìn)行驗(yàn)證，VALIDATED 或者 NOT VALIDATED；
    INDEX_NAME 是主鍵或者唯一約束使用的索引；
    INVALID 表示約束是否已經(jīng)失效，INVALID 或者 NULL；
    CONSTRAINT_TYPE 是約束類型，P 表示主鍵。

查找主鍵約束對應(yīng)的字段

視圖 dba_cons_columns、all_cons_columns 以及 user_cons_columns 包含了約束對應(yīng)的字段信息。

以下查詢返回了指定表上的主鍵和對應(yīng)的字段信息：

select cc.owner, cc.table_name, cc.constraint_name,
       listagg(cc.column_name,',') within group (order by cc.position) as columns
from dba_constraints c
join dba_cons_columns cc on (c.owner = cc.owner and c.constraint_name = cc.constraint_name)
where c.constraint_type = 'P'
and cc.owner = :user_name -- 用戶名
and cc.table_name = :table_name -- 表名
group by cc.owner, cc.table_name, cc.constraint_name;



其中，listagg 函數(shù)將多字段約束中的字段名使用逗號分隔進(jìn)行連接。
查找缺少主鍵約束的表

通常來說，每個(gè)表都應(yīng)該有一個(gè)主鍵。以下查詢可以找出指定模式下沒有主鍵的表：

select t.owner, t.table_name
from dba_tables t
left join dba_constraints c on (t.owner = c.owner and t.table_name = c.table_name and c.constraint_type = 'P')
where t.owner = :user_name -- 用戶名
and c.constraint_type is null;



查找指定表上的唯一約束

外鍵約束的類型為 U，以下查詢返回了指定表上的主鍵：

select owner, table_name, constraint_name, status, deferrable, deferred, validated, index_name, invalid
from dba_constraints
where owner = :user_name -- 用戶名
and table_name = :table_name -- 表名
and constraint_type = 'U';



和主鍵約束類似，可以使用以下查詢返回唯一約束對應(yīng)的字段：

select cc.owner, cc.table_name, cc.constraint_name,
       listagg(cc.column_name,',') within group (order by cc.position) as columns
from dba_constraints c
join dba_cons_columns cc on (c.owner = cc.owner and c.constraint_name = cc.constraint_name)
where c.constraint_type = 'U'
and cc.owner = :user_name -- 用戶名
and cc.table_name = :table_name -- 表名
group by cc.owner, cc.table_name, cc.constraint_name;



查找指定表上的外鍵約束

外鍵約束的類型為 R，以下查詢返回了指定表上的外鍵：

select owner, table_name, constraint_name, r_owner, r_constraint_name, delete_rule,
       status, deferrable, deferred, validated, invalid
from dba_constraints
where owner = :user_name -- 用戶名
and table_name = :table_name -- 表名
and constraint_type = 'R';



其中，

    R_OWNER 是外鍵約束被引用表的擁有者；
    R_CONSTRAINT_NAME 是被引用表上對應(yīng)的主鍵或者唯一約束；
    DELETE_RULE 是外鍵約束的級聯(lián)刪除規(guī)則，CASCADE、SET NULL 或者 NO ACTION；
    其他字段可以參考上文中的主鍵約束。

查找外鍵約束中涉及的表和字段

以下查詢返回了指定表上的外鍵約束，包括引用字段和被引用表上的參照字段：

select c.owner, c.table_name, listagg(cc.column_name,',') within group (order by cc.position) columns, c.constraint_name,
       c.r_owner, rc.table_name r_table_name, listagg(rcc.column_name,',') within group (order by rcc.position) r_columns, c.r_constraint_name
from dba_constraints c
join dba_cons_columns cc on (c.owner = cc.owner and c.constraint_name = cc.constraint_name)
join dba_constraints rc on (rc.constraint_name = c.r_constraint_name)
join dba_cons_columns rcc on (rc.owner = rcc.owner and rc.constraint_name = rcc.constraint_name)
where c.owner = :user_name -- 用戶名
and c.table_name = :table_name -- 表名
and c.constraint_type = 'R'
group by c.owner, c.table_name, c.constraint_name, c.r_owner, rc.table_name, c.r_constraint_name;



檢查約束

檢查約束和非空約束的類型都是 C，以下查詢返回了指定表上的檢查約束和非空約束

select owner, table_name, constraint_name, search_condition_vc, delete_rule,
       status, deferrable, deferred, validated, invalid
from dba_constraints
where owner = :user_name -- 用戶名
and table_name = :table_name -- 表名
and constraint_type = 'C';



其中，

    SEARCH_CONDITION_VC 是檢查約束的驗(yàn)證條件；
    其他字段可以參考上文中的主鍵約束。

和主鍵約束類似，可以使用以下查詢返回檢查約束對應(yīng)的字段：

select c.owner, c.table_name, c.constraint_name, c.search_condition_vc,
       listagg(cc.column_name,',') within group (order by cc.position) as columns
from dba_constraints c
join dba_cons_columns cc on (c.owner = cc.owner and c.constraint_name = cc.constraint_name)
where c.constraint_type = 'C'
and c.owner = :user_name -- 用戶名
and c.table_name = :table_name -- 表名
group by c.owner, c.table_name, c.constraint_name, c.search_condition_vc;



非空約束

非空約束可以通過檢查約束進(jìn)行查看，參考上文。

另一種查看非空字段的方式是查詢 dba_tab_columns 或者相關(guān)視圖，例如：

select owner, table_name, column_name, column_id, data_type,
       data_length, data_precision, data_scale, nullable, data_default
from dba_tab_columns
where owner = :user_name -- 用戶名
and table_name = :table_name -- 表名
and nullable = 'N';



默認(rèn)值

字段的默認(rèn)值可以直接查詢 dba_tab_columns 或者相關(guān)視圖，例如：

select owner, table_name, column_name, column_id, data_type,
       data_length, data_precision, data_scale, nullable, data_default
from dba_tab_columns
where owner = :user_name -- 用戶名
and table_name = :table_name -- 表名
and data_default is not null;



索引
查找指定表上的索引

視圖 dba_indexes、all_indexes 以及 user_indexes 包含了關(guān)于數(shù)據(jù)庫中的索引信息。

以下查詢返回了指定表上的索引：

select table_owner, table_name, table_type, owner, index_name, index_type, uniqueness, status, visibility
from dba_indexes
where table_owner = :user_name -- 用戶名
and table_name = :table_name; -- 表名



其中，

    TABLE_OWNER 是被索引對象的擁有者；
    TABLE_NAME 被索引對象的名稱；
    TABLE_TYPE 是被索引對象的類型，包括 TABLE、VIEW、SYNONYM 等；
    OWNER 是索引的擁有者；
    INDEX_NAME 是索引的名稱；
    INDEX_TYPE 是索引的類型，包括 NORMAL、BITMAP、FUNCTION-BASED NORMAL 等；
    UNIQUENESS 表示該索引是否具有唯一性，UNIQUE 或者 NONUNIQUE；
    STATUS 是非分區(qū)索引的狀態(tài)，VALID 或者 UNUSABLE；
    VISIBILITY 是索引的可見性，VISIBLE 或者 INVISIBLE。

查找索引對應(yīng)的字段

視圖 dba_ind_columns、all_ind_columns 以及 user_ind_columns 包含了索引對應(yīng)的字段信息。

select i.table_owner, i.table_name, i.index_name, i.index_type, i.uniqueness,
       listagg(ic.column_name||' '|| ic.descend, ',') within group(order by ic.column_position) as columns
from dba_indexes i
join dba_ind_columns ic on (i.table_owner = ic.table_owner and i.index_name = ic.index_name)
where i.table_owner = :user_name -- 用戶名
and i.table_name = :table_name -- 表名
group by i.table_owner, i.table_name, i.index_name, i.index_type, i.uniqueness;



其中，listagg 函數(shù)將復(fù)合索引中的字段名使用逗號分隔進(jìn)行連接。
模式比較
比較兩個(gè)模式中的表和字段

以下查詢用于比較兩個(gè)模式，返回任意模式中缺少的表和字段，以及數(shù)據(jù)類型或者字段長度不匹配的字段：

select coalesce(s1.table_name, s2.table_name) table_name,
       coalesce(s1.column_name, s2.column_name) column_name,
       s1.data_type data_type1, s1.data_length data_length1,
       s2.data_type data_type2, s2.data_length data_length2
from (
    select table_name,
           column_name,
           data_type,
           data_length
    from dba_tab_cols
    where owner = :user_name1 -- 模式用戶 1
) s1
full join (
    select table_name,
           column_name,
           data_type,
           data_length
    from dba_tab_cols
    where owner = :user_name2 -- 模式用戶 2
) s2
on (s2.table_name = s1.table_name and s2.column_name = s1.column_name)
where s1.column_name is null
or s2.column_name is null
or s1.data_type != s2.data_type
or s1.data_length != s2.data_length
order by table_name, column_name;



比較兩個(gè)表中的數(shù)據(jù)差異

以下語句可以用于比較兩個(gè)結(jié)構(gòu)相同的表中的數(shù)據(jù)差異：

(
select 'T1', col1, col2, ... from schema1.table1
minus
select 'T1', col1, col2, ... from schema2.table2
)
union all
(
select 'T2', col1, col2, ... from schema2.table2
minus
select 'T2', col1, col2, ... from schema1.table1
);



如果覺得文章對你有用，歡迎關(guān)注??、評論??、點(diǎn)贊??！