Oracle PL/SQL/System Tables Views/DBA INDEXES

Get owner, index_name and status for DBA_indexes

   <source lang="sql">
   

SQL> select Owner, /*Owner of the index*/

 2         Index_Name,    /*Name of the index*/
 3         Status         /*Either DIRECT PATH or VALID*/
 4    from DBA_INDEXES
 5    where  rownum < 10
 6  /
                                                             

OWNER INDEX_NAME STATUS

---

------------------------------ --------

XDB XDBHI_IDX VALID DEFINER SYS_IOT_TOP_16249 VALID INV20 SYS_IOT_TOP_16247 VALID INV19 SYS_IOT_TOP_16245 VALID INV18 SYS_IOT_TOP_16243 VALID INV17 SYS_IOT_TOP_16241 VALID INV16 SYS_IOT_TOP_16239 VALID INV14 SYS_IOT_TOP_16235 VALID INV13 SYS_IOT_TOP_16233 VALID

 </source>
   
  

Search for indexes created by anyone other than the table owner.

   <source lang="sql">
   

SQL> select

 2        Owner,                /*Owner of the index*/
 3        Index_Name,           /*Name of the index*/
 4        Table_Owner,          /*Owner of the table*/
 5        Table_Name            /*Name of the indexed table*/
 6  from DBA_INDEXES
 7  where Owner != Table_Owner and rownum < 10;

SQL> SQL>

 </source>