getindexes.sql

set serverout on  size 999999 lines 300 longchunksize 500000
spool go
DECLARE
  CURSOR c_objects IS
    SELECT  table_name
         ,  constraint_name name 
         ,  'CONSTRAINT' type
      FROM  user_constraints
     WHERE  constraint_type='P'
     UNION
    SELECT  table_name
         ,  index_name name
         ,  'INDEX' type
      FROM  user_indexes;
BEGIN
  DBMS_OUTPUT.PUT_LINE('set pages 0 long 999999 longchunksize 9999999');
  DBMS_OUTPUT.PUT_LINE('set head off trimspool on');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  DBMS_OUTPUT.PUT_LINE('dbms_metadata.set_transform_param(dbms_metadata.session_transform,''PRETTY'',true);');
  DBMS_OUTPUT.PUT_LINE('dbms_metadata.set_transform_param(dbms_metadata.session_transform,''SQLTERMINATOR'',true);');
  DBMS_OUTPUT.PUT_LINE('END;');
  DBMS_OUTPUT.PUT_LINE('/');
  DBMS_OUTPUT.PUT_LINE('column a format a200 word_wrapped');
  DBMS_OUTPUT.PUT_LINE('spool indexdefs');
  FOR v_objects IN c_objects
  LOOP
NULL;
    DBMS_OUTPUT.PUT_LINE('SELECT  DBMS_METADATA.GET_DDL('''||v_objects.type||''','''||v_objects.name||''',USER) a');
    DBMS_OUTPUT.PUT_LINE('FROM  DUAL;');
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('spool off');
END;
/
spool off;
@go.lst
host rm go.lst