How to see a list of all the indexes (including implicit ones) in SQL*Plus?

sql indexing sqlplus

Moeb · Aug 11, 2010 · Viewed 47.7k times · Source

Is there some way to get a list of all the indexes on a particular table using SQL*Plus?

I created a table

CREATE TABLE temp(
id NUMBER PRIMARY KEY,
name VARCHAR2(20));

There should be an implicit index created on the primary key (id). How can I see that index?

SELECT * FROM all_indexes WHERE table_name = 'temp';

gives

no rows selected

Answer

SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME = 'your_table'

Note: If you want to limit the search to a specific schema, you can also do:

SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME = 'your_table' AND OWNER = 'your_owner'

This is useful in situations where you might have the same table name in multiple schemas.

Also, keep in mind that Oracle stores the table names as upper case, so in your example you need to do:

select * from all_indexes where table_name = 'TEMP';