Oracle 常用脚本之查看数据表、字段、约束、索引等信息

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目录

        数据表
            列出数据库中的表
            列出指定用户的表
            通过表名查找特定的表
            查看表的创建/修改时间
        字段
            查看指定表中的字段
            查看指定表中的虚拟/隐藏字段
            查找包含指定字段的表
        约束
            查找指定表上的主键约束
            查找主键约束对应的字段
            查找缺少主键约束的表
            查找指定表上的唯一约束
            查找指定表上的外键约束
            查找外键约束中涉及的表和字段
            检查约束
            非空约束
            默认值
        索引
            查找指定表上的索引
            查找索引对应的字段
        模式比较
            比较两个模式中的表和字段
            比较两个表中的数据差异

大家好，我是只谈技术不剪发的 Tony 老师。前文介绍了如何查看 Oracle 中的用户、权限、用户资源配置等信息，今天我们来继续讨论如何通过 Oracle 数据字典查看数据库中的表、字段、约束、索引等信息。
数据表
列出数据库中的表

系统视图 dba_tables 中包含了数据库中所有的关系表信息，需要 DBA 权限才能查看：

select owner, table_name, tablespace_name, status, num_rows, avg_row_len
from dba_tables
where owner in ( -- 非系统模式用户
    select username
    from dba_users
    where oracle_maintained = 'N');



其中，

    OWNER 是表的拥有者；
    TABLE_NAME 是表名；
    TABLESPACE_NAME 是表所在的表空间；
    STATUS 是表的状态，VALID 或者 UNUSABLE；
    NUM_ROWS 是表中的数据行数，通过表的统计分析获得，可能不准确；
    AVG_ROW_LEN 是数据行的平均长度，单位为字节。

    📝关于视图 dba_tables 更多字段的说明可以参考官方文档。

另外，我们也可以通过 all_tables 视图查看当前用户可以访问所有表，或者使用 user_tables 视图查看当前用户拥有的所有表。
列出指定用户的表

通过以下语句可以查询指定用户拥有的表：

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



如果想要查看当前登录用户拥有的表，也可以直接查询 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 运算符可以执行相反查找，使用 regexp_like 可以执行正则表达式查找。
查看表的创建/修改时间

系统视图 dba_objects、all_objects 以及 user_objects 中包含了各种对象（表、索引、视图、触发器、程序包等）的信息，可以用于查询对象的创建时间和最后一次修改时间。例如：

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; -- 表名



字段
查看指定表中的字段

系统视图 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 是字段的数据类型，包括 NUMBER、CHAR、VARCHAR2、DATE、TIMESTAMP 等；
    DATA_LENGTH 是字段的长度，单位为字节；
    DATA_PRECISION 是 NUMBER 数据类型的精度或 FLOAT 类型的二进制精度；
    DATA_SCALE 是数字类型的小数点位数；
    NULLABLE 表示字段是否可空。

查看指定表中的虚拟/隐藏字段

Oracle 11g 增加了虚拟列（Virtual Column）的支持，Oracle 12c 增加了隐藏列（Invisible Column）的支持。这些字段的信息需要通过名字更短的视图 dba_tab_cols、all_tab_cols 以及 user_tab_cols 进行查看。例如：

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 字段是计算字段值的表达式。
查找包含指定字段的表

以下查询返回了包含指定字段的数据表：

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



同样也可以通过 like 运算符进行模糊查找。
约束
查找指定表上的主键约束

视图 dba_constraints、all_constraints 以及 user_constraints 包含了关于表中约束的定义信息。

以下查询返回了指定表上的主键：

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 是约束的状态，ENABLED 或者 DISABLED；
    DEFERRABLE 表示约束是否可以延迟验证，DEFERRABLE 或者 NOT DEFERRABLE；
    DEFERRED 表示约束是延迟验证（DEFERRED）或者立即验证（IMMEDIATE）；
    VALIDATED 表示启用约束是是否对已有的数据进行验证，VALIDATED 或者 NOT VALIDATED；
    INDEX_NAME 是主键或者唯一约束使用的索引；
    INVALID 表示约束是否已经失效，INVALID 或者 NULL；
    CONSTRAINT_TYPE 是约束类型，P 表示主键。

查找主键约束对应的字段

视图 dba_cons_columns、all_cons_columns 以及 user_cons_columns 包含了约束对应的字段信息。

以下查询返回了指定表上的主键和对应的字段信息：

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 函数将多字段约束中的字段名使用逗号分隔进行连接。
查找缺少主键约束的表

通常来说，每个表都应该有一个主键。以下查询可以找出指定模式下没有主键的表：

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';



和主键约束类似，可以使用以下查询返回唯一约束对应的字段：

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 是被引用表上对应的主键或者唯一约束；
    DELETE_RULE 是外键约束的级联删除规则，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 是检查约束的验证条件；
    其他字段可以参考上文中的主键约束。

和主键约束类似，可以使用以下查询返回检查约束对应的字段：

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;



非空约束

非空约束可以通过检查约束进行查看，参考上文。

另一种查看非空字段的方式是查询 dba_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 -- 表名
and nullable = 'N';



默认值

字段的默认值可以直接查询 dba_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 -- 表名
and data_default is not null;



索引
查找指定表上的索引

视图 dba_indexes、all_indexes 以及 user_indexes 包含了关于数据库中的索引信息。

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

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 是非分区索引的状态，VALID 或者 UNUSABLE；
    VISIBILITY 是索引的可见性，VISIBLE 或者 INVISIBLE。

查找索引对应的字段

视图 dba_ind_columns、all_ind_columns 以及 user_ind_columns 包含了索引对应的字段信息。

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 函数将复合索引中的字段名使用逗号分隔进行连接。
模式比较
比较两个模式中的表和字段

以下查询用于比较两个模式，返回任意模式中缺少的表和字段，以及数据类型或者字段长度不匹配的字段：

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;



比较两个表中的数据差异

以下语句可以用于比较两个结构相同的表中的数据差异：

(
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
);



如果觉得文章对你有用，欢迎关注❤️、评论📝、点赞👍！