Oracle Query To Generate Create Index Statement For Tables

Find Oracle Indexes for Tables

Here is a query to generate create index statements for single column index using Oracle system tables.

SELECT index_name FROM dba_indexes WHERE index_name ='
                || ''''
                || t1.index_name
                || ''''
                || ') '
                || 'create '
                || t1.index_type
                || ' index '
                || t1.index_name
                || ' on '
                || t1.table_name
                || '('
                || t2.column_name
                || ')'
                || 'tablespace ts_indx;'
   FROM dba_indexes t1, dba_ind_columns t2
  WHERE t1.index_name = t2.index_name
          and t1.table_owner = 'SALES';

This is usefull not only for recreating the indexes for tables in Oracle database but also for moving tables from Oracle to other database. For example, here a statement to generate the same index for tables in MS SQL Server:

SELECT 'IF NOT EXISTS (SELECT name FROM sysindexes WHERE name ='
                || ''''
                || t1.index_name
                || ''''
                || ') '
                || 'create '
                || ' index '
                || t1.index_name
                || ' on '
               ||'dbo.['
               || t1.table_name
               ||']'
               || '('
               || t2.column_name
               || ')'
               || ';'
FROM dba_indexes t1, dba_ind_columns t2
WHERE t1.index_name = t2.index_name
         and t1.table_owner = 'SALES' ;

Reference:

• Expert Oracle Database 11g Administration (Expert's Voice in Oracle)  
• Other reference on  the web

Related Notes:

• Find Oracle Table Size, Number Of Columns and Rows
• Retrieve Oracle Table Column Name And Data Type