The problem of listing indexes for a table is much the same as that of listing constraints on a table. You have a master-detail relationship between the index and its columns , and you may have multiple indexes on one table.

To list only the indexes on a table, query the all_indexes view:

SELECT index_name, index_type, uniqueness

FROM all_indexes

WHERE owner = UPPER('&owner')

 AND table_name = UPPER('&table_name');

Listing the indexes alone is seldom enough. You need to know at least the columns involved in each index. To that end, join with all_ind_columns . Example 10-8 shows the query.

Example 10-8. A script to list all indexes on a table

COLUMN index_name FORMAT A20

COLUMN index_type FORMAT A10


COLUMN column_name FORMAT A15

SELECT ai.index_name, ai.index_type, ai.uniqueness, aic.column_name

FROM all_indexes ai INNER JOIN all_ind_columns aic

 ON ai.owner = aic.index_owner

 AND ai.index_name = aic.index_name

WHERE ai.owner = UPPER('&owner')

 AND ai.table_name = UPPER('&table_name')

ORDER BY aic.column_position;

Here's a run of Example 10-8 showing the two indexes on the employee table:



Enter value for owner:


old 5: WHERE ai.owner = UPPER('&owner')

new 5: WHERE ai.owner = UPPER('gennick')

Enter value for table_name:


old 6: AND ai.table_name = UPPER('&table_name')

new 6: AND ai.table_name = UPPER('employee')


-------------------- ---------- ---------- ---------------



One thing to keep in mind when working with unique indexes is that Oracle will report a unique index violation as if it were a constraint violation. The error message you get is the same as the one used when you violate a unique constraint, and it looks like this:

ORA-00001: unique constraint (GENNICK.UNIQUE_BILLING_RATE) violated

The reason for this is no doubt because Oracle enforces unique and primary key constraints by creating indexes on the constrained fields. If you do get the error message just shown, you might want to check two things. First, list the constraints on the table you are updating. Second, if you don't find one with a name that matches the one in the error message, check to see whether there happens to be a unique index with that same name .

Beginning with Oracle8 i Database, defining a unique or primary key constraint without having a corresponding index created is possible.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: