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>