Oracle PL/SQL/System Tables Views/DBA INDEXES

Get owner, index_name and status for DBA_indexes

    
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

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

    
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>