---

B-tree | IOT | Unique | Reverse Key | Key Compressed | Descending | Bitmap | Bitmap Join | Function-based | Indexed virtual column | Virtual | Invisible | Global Partitioned | Local Partitioned | Domain | B-tree cluster | Hash Cluster
Generating DDL for creating an index:
1, Using the DBMS_METADATA package SQL> select DBMS_METADATA.get_ddl('INDEX','EMP_NAME_IDX') from dual;
Note: get_ddl procedure of the DBMS_METADATA package provides a quick way to generate the DDL for recreating both indexes as well as tables. if you want to create DDL for all indexes in a schema execute  SQL> select DBMS_METADATA.get_ddl('INDEX',di.index_name) from dba_indexes di where owner='DBAREF';
To format the output nicely formatted
SQL>set linesize 80
SQL>column xyz format a100 word_wrapped
SQL>column x format a200 word_wrapped
You can also use SESSION_TRANSFORM procedure to customize the output generated by get_ddl procedure. If you want to restrict the object to be retrieved by DBMS_METADATA use SET_FILTER procedure.
2. Using Data Pump a) execute  $ expdp content=metadata_only $impdp sqlfile=mydumpfile (the dump file generated in step a).
Collecting STATS for indexes: You can specify CASCADE=>true option with the GATHER_DATABASE_STATS, GATHER_SCHEMA_STATS, GATHER_TABLE_STATS procedure to collect the STATS for indexes along with the tables.
SQL> execute dbma_stats.gather_schema_stats('DBAREF', cascade=>TRUE);
SQL> execute dbma_stats.gather_table_stats(ownname=>'DBAREF', tabname=>'EMPLOYEE',cascade=>TRUE);
SQL> execute dbma_stats.gather_index_stats('DBAREF','EMPLOYEE_PK');
SQL> execute dbma_stats.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);
==============================================================================
you can also set the default preference to collect the index statistics automatically when collecting table statistics.
SQL>exec dbms_stats.set_database_prefs('CASCADE','TRUE');
==============================================================================

 SET SERVEROUTPUT ON FEEDBACK OFF

DECLARE
  CURSOR idx_rbld IS
    WITH scaler1 AS (SELECT object_name FROM user_recyclebin)
    SELECT 'ALTER INDEX '||index_name||' REBUILD ONLINE' rbld_stmt
    FROM user_indexes
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    UNION ALL
    SELECT 'ALTER INDEX '||index_name||' REBUILD PARTITION '||partition_name||' ONLINE' rbld_stmt
    FROM user_ind_partitions
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    AND partition_name NOT IN(SELECT object_name FROM scaler1)
    UNION ALL
    SELECT 'ALTER INDEX '||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE' rbld_stmt
    FROM user_ind_subpartitions
    WHERE status='UNUSABLE'
    AND index_name NOT IN(SELECT object_name FROM scaler1)
    AND subpartition_name NOT IN(SELECT object_name FROM scaler1);
BEGIN
  FOR i IN idx_rbld
  LOOP
    BEGIN
      -- DBMS_OUTPUT.PUT_LINE(i.rbld_stmt);
      EXECUTE IMMEDIATE i.rbld_stmt;
    EXCEPTION
      WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(i.rbld_stmt);
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
  END LOOP;
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

---