Generate index ddl statements
                                                 Last update (2008-04-01 11:22:55)
                                                                                                                   Date added (2008-02-25 10:50:47)

Summary
To generate the creation statements for one table's indexes use the following query. For example to create all index ddl statements for table GL.GL_CODE_COMBINATIONS login as system to sqlplus.

SQL> set heading off;
SQL>spool /oracle/app/oracle/product/9.2/ddl.sql

Execute the script
SELECT SQL FROM 
(SELECT  table_name z,
        index_name y,
        -1 x,
        'create index ' || owner || '.' || RTRIM(DECODE(uniqueness,'UNIQUE','UNIQUE',NULL))
        || RTRIM(index_name) SQL
FROM    dba_indexes
WHERE   table_owner = 'GL'
AND table_name = 'GL_CODE_COMBINATIONS'
UNION
SELECT  table_name z,
        index_name y,
        0 x,
        'on ' || table_owner || '.'||
        RTRIM(table_name) ||
        '(' SQL
FROM    dba_indexes
WHERE   table_owner = 'GL'
AND table_name = 'GL_CODE_COMBINATIONS'
UNION
SELECT  table_name z,
        index_name y,
        column_position x,
        RTRIM(DECODE(column_position,1,NULL,','))||
        RTRIM(column_name) SQL
FROM    dba_ind_columns
WHERE   table_owner = 'GL'
AND table_name = 'GL_CODE_COMBINATIONS'
UNION
SELECT  table_name z,
        index_name y,
        999999 x,
        ')'  || CHR(10)
        ||'unrecoverable ' || CHR(10)
        ||'STORAGE('                            || CHR(10)
        ||'INITIAL '     || initial_extent      || CHR(10)
        ||'NEXT '        || next_extent         || CHR(10)
        ||'MINEXTENTS ' || '1' || CHR(10)
        ||'MAXEXTENTS ' || max_extents  || CHR(10)
        ||'PCTINCREASE '|| '0'  ||')'   || CHR(10)
        ||'INITRANS '   || ini_trans         || CHR(10)
        ||'MAXTRANS '   || max_trans         || CHR(10)
        ||'PCTFREE '    || '0' || CHR(10)
        ||'TABLESPACE ' || tablespace_name ||CHR(10)
        ||'PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10)
        ||'/'||CHR(10)||CHR(10) SQL
FROM    dba_indexes
WHERE   table_owner = 'GL'
AND table_name = 'GL_CODE_COMBINATIONS'
ORDER BY 1,2,3);

SQL> spool off;
Now you have the script c:ddl.sql with all creation ddl statements for indexes

Tip:Another way to get ddl for any type of object is dbms_metadata.get_ddl function.

Other links
Generate trigger ddl statements
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search