本文共 3661 字，大约阅读时间需要 12 分钟。

手工输入指定对应的业务用户，从而方便DBA查询指定用户下的情况。

--外键无索引：（指定要查询的用户）column con format a20 word_wrappedcolumn idx format a20 word_wrappedcolumn table_name format a30 word_wrappedselect decode( b.table_name, NULL, '****', 'ok' ) Status,a.table_name, a.columns con, b.columns idxfrom( select substr(a.table_name,1,30) table_name,substr(a.constraint_name,1,30) constraint_name,max(decode(position, 1, substr(column_name,1,30),NULL)) ||max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||max(decode(position,16,', '||substr(column_name,1,30),NULL)) columnsfrom dba_cons_columns a, dba_constraints bwhere a.constraint_name = b.constraint_nameand b.constraint_type = 'R'and a.owner = upper('&owner')group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columnsfrom dba_ind_columnswhere INDEX_OWNER = upper('&index_owner')group by substr(table_name,1,30), substr(index_name,1,30) ) bwhere a.table_name = b.table_name (+)and b.columns (+) like a.columns || '%'/

SQL

Copy

测试脚本使用

比如使用修改过的脚本，查询业务SCOTT用户下的情况：

sys@DEMO> @check_fk_info.sqlEnter value for owner: scottold  25: and a.owner = upper('&owner')new  25: and a.owner = upper('scott')Enter value for index_owner: scottold  45: where INDEX_OWNER = upper('&index_owner')new  45: where INDEX_OWNER = upper('scott')STAT TABLE_NAME                     CON                  IDX---- ------------------------------ -------------------- --------------------**** EMP                            DEPTNOsys@DEMO>

SQL

Copy

上面就是查询到EMP表在DEPTNO列上存在外键约束，且没有索引，如果需要进一步查看表的建表语句，可以这样查询：

--查询建表语句；sys@DEMO> set long 999999 pages 1000select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

转载地址：http://ccsof.baihongyu.com/