select case when i.index_name is not null then 'OK' else '****' end ok , c.owner , c.table_name , c.constraint_name , c.cols , i.index_name from ( select a.owner , a.table_name , a.constraint_name , listagg(b.column_name, ' ' ) within group (order by column_name) cols from all_constraints a, all_cons_columns b where a.owner = b.owner and a.constraint_name = b.constraint_name and a.constraint_type = 'R' group by a.owner,a.table_name, a.constraint_name ) c left outer join ( select index_owner , table_name , index_name , cr , listagg(column_name, ' ' ) within group (order by column_name) cols from ( select index_owner , table_name , index_name , column_position , column_name , connect_by_root(column_name) cr from all_ind_columns connect by prior column_position-1 = column_position and prior index_name = index_name and prior index_owner = index_owner ) group by index_owner, table_name, index_name, cr ) i on c.owner = i.index_owner and c.cols = i.cols and c.table_name = i.table_name order by ok,owner,table_name,constraint_name;
unindex 11.2 cross schema
Foreign keys might point to another schema. Here is an unindex query for such situation. My earlier queries unindex and unindex 11.2 were built on top of user_* views. Here the queries are using all_* views. I am not using dba_* views as all_* views are usable for wider audience than just dba privileged. Reminder: you probably do not need all of those, but they might explain you some poor execution times or TM lock waits.
- ► 2012 (33)
- ▼ 2011 (38)
- ► 2010 (37)
- I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.