Find Indexes created on Oracle Database Schema/Table

Hey everyone, here is a small tip on finding and managing Oracle indexes in a database or schema. Below are a set of queries that can be used for the same. Note that you need to have SYS/DBA privilege on the database to perform the same. This is intended for people who are new to Oracle databases. 

All information related to indexes on an oracle database are stored in the DBA_INDEXES table that belongs to SYS schema. Below are a few common scenarios when related to querying from this table. You may replace the predicates accordingly. 

Find all indexes defined on a database,

SELECT * FROM DBA_INDEXES

DBA_INDEXES table in Oracle 12c. 

Find all indexes on a particular schema, 

SELECT * FROM DBA_INDEXES WHERE OWNER IN ('HR')

Find Indexes on Schema.

Find the indexes defined on a specific table, 

SELECT * FROM DBA_INDEXES WHERE OWNER IN ('HR') AND TABLE_NAME IN ('EMPLOYEES')

Indexes defined on a table.

Find Unique indexes in a database,

SELECT * FROM DBA_INDEXES WHERE UNIQUENESS IN ('UNIQUE')

Find number of indexes defined on a database schema by index type,

SELECT OWNER, INDEX_TYPE, COUNT(*) INDEX_CNT FROM DBA_INDEXES GROUP BY OWNER, INDEX_TYPE

Find the number of Indexes on a particular table,

SELECT OWNER, TABLE_NAME, INDEX_TYPE, COUNT(*) INDEX_CNT FROM DBA_INDEXES
WHERE OWNER IN ('HR') 
AND TABLE_NAME IN ('EMPLOYEES')
GROUP BY 
OWNER, 
TABLE_NAME,
INDEX_TYPE