Generate drop schema indexes script

I use the below script to drop indexes before running complete refresh in Oracle database.
Before dropping the indexes I run export without rows so that indexes can be re-created (get
sql script from export dump) after snapshots have been refreshed.


--
-- Replace USERNAME with the actual username
--
set head off pagesize 0 echo off verify off feedback off

spool coalesce_tablespaces.sql

select 'alter tablespace ' || tablespace_name || ' coalesce;'
from
        (select distinct tablespace_name
        from dba_segments
        where owner = 'USERNAME'
        and segment_type = 'INDEX')
/

spool off

spool drop_idx.sql

select 'set head on echo on verify on feedback on'
from dual
/

select 'spool log.drop_idx'
from dual
/

--
-- PV is the naming convention for primary keys
--
select 'drop index ' || owner || '.' || index_name || ';'
from dba_indexes
where owner = 'USERNAME'
and index_name not like 'PV%'
and index_name not like 'I_SNAP$%'
/

select '@coalesce_tablespaces.sql'
from dual
/

select 'spool off'
from dual
/

select 'exit;'
from dual
/

spool off

exit;