Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba indexes

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>

Union dba_indexes and dba_tables

SQL> select
  2        Owner,
  3        Tablespace_Name,
  4        COUNT(*)||" tables" Objects
  5   from DBA_TABLES
  6  group by
  7        Owner,
  8        Tablespace_Name
  9  union
 10  select
 11        Owner,
 12        Tablespace_Name,
 13        COUNT(*)||" indexes" Objects
 14  from dba_indexes
 15  group by
 16        Owner,
 17        Tablespace_Name;
                                                              
OWNER                           TABLESPACE_NAME                 OBJECTS
------------------------------  ------------------------------  ------------------------------------------------
CTXSYS                          SYSAUX                          26 tables
CTXSYS                          SYSAUX                          47 indexes
CTXSYS                                                          11 tables
DBSNMP                          SYSAUX                          17 tables
DBSNMP                          SYSAUX                          8 indexes
DBSNMP                                                          2 indexes
DBSNMP                                                          4 tables
DEFINER                         SYSTEM                          1 indexes
DEFINER                                                         1 tables
FLOWS_020100                    SYSAUX                          160 tables
FLOWS_020100                    SYSAUX                          422 indexes
FLOWS_020100                                                    4 tables
FLOWS_FILES                     SYSAUX                          1 tables
FLOWS_FILES                     SYSAUX                          5 indexes
HR                              USERS                           19 indexes
HR                              USERS                           6 tables
HR                                                              1 tables
INV10                           SYSTEM                          1 indexes
INV10                                                           1 tables
INV11                           SYSTEM                          1 indexes
INV11                                                           1 tables
INV12                           SYSTEM                          1 indexes
INV12                                                           1 tables
INV13                           SYSTEM                          1 indexes
INV13                                                           1 tables
INV14                           SYSTEM                          1 indexes
INV14                                                           1 tables
INV15                           SYSTEM                          1 indexes
INV15                                                           1 tables
INV16                           SYSTEM                          1 indexes
INV16                                                           1 tables
INV17                           SYSTEM                          1 indexes
INV17                                                           1 tables
INV18                           SYSTEM                          1 indexes
INV18                                                           1 tables
INV19                           SYSTEM                          1 indexes
INV19                                                           1 tables
INV20                           SYSTEM                          1 indexes
INV20                                                           1 tables
sqle                          SYSTEM                          12 indexes
sqle                          SYSTEM                          14 tables
sqle                                                          3 indexes
sqle                                                          9 tables
MDSYS                           SYSTEM                          30 tables
MDSYS                           SYSTEM                          51 indexes
MDSYS                                                           3 indexes
MDSYS                                                           7 tables
OUTLN                           SYSTEM                          3 tables
OUTLN                           SYSTEM                          4 indexes
SYS                             SYSAUX                          175 tables
SYS                             SYSAUX                          247 indexes
SYS                             SYSTEM                          402 tables
SYS                             SYSTEM                          513 indexes
SYS                                                             28 indexes
SYS                                                             92 tables
SYSTEM                          SYSAUX                          21 indexes
SYSTEM                          SYSAUX                          22 tables
SYSTEM                          SYSTEM                          145 indexes
SYSTEM                          SYSTEM                          85 tables
SYSTEM                                                          31 tables
SYSTEM                                                          36 indexes
TSMSYS                          SYSAUX                          1 tables
TSMSYS                          SYSAUX                          2 indexes
XDB                             SYSAUX                          11 tables
XDB                             SYSAUX                          384 indexes
XDB                                                             1 indexes
                    
SQL>
SQL>
SQL>