如何快速查看数据库里哪些表无主键和唯一索引？

原创 jieguo 2023-08-08

1439

在配置ogg的时候，我们会需要考虑表是否有主键和唯一索引。如果没有建议建上避免性能问题。

你是不是还以为需要自己写如下类似脚本查询？

查看当前用户下既没有主键也没有唯一性索引的表：

可用用户名登录，则
select table_name
  from user_tables u
 where not exists
 (select table_name
          from (select table_name
                  from user_tables a
                 where exists (select *
                          from user_constraints b
                         where b.constraint_type = 'P'
                           and a.table_name = b.table_name)
                union
                select distinct cu.table_name
                  from user_cons_columns cu, user_constraints au
                 where cu.constraint_name = au.constraint_name
                   and au.constraint_type = 'U') T
         where u.table_name = T.table_name)
   and u.table_name not like '%$%';

如果不知道用户名密码没法连接，则用本地sysdba

select table_name
  from dba_tables u
 where u.owner='用户名' and not exists
 (select table_name
          from (select table_name
                  from dba_tables a
                 where a.owner='用户名' and exists (select *
                          from dba_constraints b
                         where b.constraint_type = 'P' 
                           and a.table_name = b.table_name and a.owner=b.owner and b.owner='用户名')
                union
                select distinct cu.table_name
                  from dba_cons_columns cu, dba_constraints au
                 where cu.constraint_name = au.constraint_name and cu.owner=au.owner and cu.owner='用户名'
                   and au.constraint_type = 'U') T
         where u.table_name = T.table_name)
   and u.table_name not like '%$%';

复制

其实oracle已经准备有视图了，您只需查询DBA_LOGSTDBY_NOT_UNIQUE就行了。

col owner for a20
col table_name for a30
select * from DBA_LOGSTDBY_NOT_UNIQUE;
一条一条输出比较慢些
DBA_LOGSTDBY_NOT_UNIQUE displays all tables that have no primary and no non-null unique indexes.

Most of the tables displayed by this view are supported because their columns contain enough information to be maintained in a logical standby database. Some tables, however, cannot be supported because their columns do not contain the necessary information. Unsupported tables usually contain a column defined using an unsupported data type.

In a CDB, the data displayed pertains to the container in which the view is queried.
复制