sqlindexingsqlplus

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

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

Solution

  • 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';