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

文章目录

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

前文介绍了如何查看 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 进行查看。

更多请见：http://www.mark-to-win.com/tutorial/51523.html