How To List All Indexes in Your Schema

Q

How To List All Indexes in Your Schema? - Oracle DBA FAQ - Managing Oracle Table Indexes

✍: FYIcenter.com

A

If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script:

SELECT index_name, table_name, uniqueness 
  FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';
INDEX_NAME              TABLE_NAME            UNIQUENES
----------------------- --------------------- ---------
EMP_EMAIL_UK            EMPLOYEES             UNIQUE
EMP_EMP_ID_PK           EMPLOYEES             UNIQUE
EMP_DEPARTMENT_IX       EMPLOYEES             NONUNIQUE
EMP_JOB_IX              EMPLOYEES             NONUNIQUE
EMP_MANAGER_IX          EMPLOYEES             NONUNIQUE
EMP_NAME_IX             EMPLOYEES             NONUNIQUE

As you can see, the pre-defined table EMPLOYEES has 6 indexes defined in the default sample database.

2007-05-02, 4893👍, 0💬