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

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

大家好,我是只谈技术不剪发的 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
);

如果觉得文章对你有用,欢迎关注❤️、评论?、点赞?!

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

不剪发的Tony老师

Oracle 常用脚本之查看数据表、字段、约束、索引等信息
CSDN认证博客专家


数据库架构师

毕业于北京航空航天大学,十多年数据库管理与开发经验,拥有OCP以及RHCE证书。目前在一家全球性的博彩企业从事数据库架构设计和开发,CSDN学院签约讲师以及GitChat专栏作者。

关注博主即可阅读全文


原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

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

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » Oracle 常用脚本之查看数据表、字段、约束、索引等信息
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏