Generate DDL for all Indexes in a Schema

set pagesize 0
set linesize 1000
set long 500000
set head off
set trimspool on
set feedback off
set echo off
set verify off

column theClob format a400

accept schemaName prompt "Enter Schema Name: "
accept outputFile prompt "Enter Output File: "

spool '&outputFile'

EXEC dbms_metadata.SET_TRANSFORM_PARAM (dbms_metadata.SESSION_TRANSFORM, 'PRETTY', true);

SELECT DBMS_METADATA.GET_DDL('INDEX', dba_indexes.index_name, '&schemaName') as theClob from dba_indexes where owner = '&schemaName';

spool off