Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Show All Indexes in Your Schema

By: FYIcenter.com

(Continued from previous topic...)

How To List All Indexes in Your Schema?

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.

(Continued on next topic...)

1. What Is an Index?
2. How To Run SQL Statements through the Web Interface?
3. How To Create a Table Index?
4. How To List All Indexes in Your Schema?
5. What Is an Index Associated with a Constraint?
6. How To Rename an Index?
7. How To Drop an Index?
8. Can You Drop an Index Associated with a Unique or Primary Key Constraint?
9. What Happens to Indexes If You Drop a Table?
10. How To Recover a Dropped Index?
11. What Happens to the Indexes If a Table Is Recovered?
12. How To Rebuild an Index?
13. How To See the Table Columns Used in an Index?
14. How To Create a Single Index for Multiple Columns?