Quick Search:
 Oracle PL/SQL: INDEXES: View table indexes Jump to:  
Category: >> Oracle PL/SQL >> INDEXES: View table indexes  

<< lastnext >>

Snippet Name: INDEXES: View table indexes

Description: Examples of viewing the indexes on an Oracle table. Replace the example table names with the actual table name.

Also see:
» INDEXES: Analyze Index
» INDEXES: Block Dump
» INDEXES: Rebuild Reverse
» INDEXES: ENABLE (function-based index)
» INDEXES: DISABLE (function-based index)
» INDEXES: Alter Index Parallel
» INDEXES: Alter Index Deallocate Unused
» INDEXES: Alter Index Allocate Extent
» INDEXES: Virtual / NoSegment
» INDEXES: Reverse Key Indexes
» INDEXES: Bitmap Join Indexes
» INDEXES: Bitmap Indexes
» INDEXES: Unique indexes
» INDEXES: Parallel Index
» INDEXES: Compute Statistics
» INDEXES: Function-Based Index
» INDEXES: Alter index
» INDEXES: Single Column Non-unique
» INDEXES: Index Usage Notes
» Compressed Indexes
» Create INDEX

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 11th, 2009

SELECT index_name, column_name, column_position 
FROM user_ind_columns
ORDER BY index_name, column_position
-- list all the indexes for a table:
SELECT index_name FROM user_indexes
    WHERE table_name = 'TABLE_NAME';
-- If the table is a system table then it will not be listed 
-- in the user_indexes view, so instead use all_indexes:
    SELECT index_name FROM all_indexes
        WHERE table_name = 'TABLE_NAME';
-- alternate method, shows column positions:
SET linesize 110
SET verify off
col index_owner format a20
col column_name format a20
col tablespace_name format a20
break ON table_name skip 1;
SELECT c.index_owner, i.index_name,
DECODE(i.uniqueness, 'UNIQUE', 'YES', 'NO') UNIQUENESS,
c.column_name, c.column_position, i.tablespace_name
FROM dba_ind_columns c, dba_indexes i
WHERE i.index_name = c.index_name
AND i.table_owner = c.table_owner
ORDER BY c.index_owner, i.index_name, c.column_position;

   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org