set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* script to rebuild the SQL to build a given       */
/*     sequence statement                           */
/*                                                  */
/*         @c:\oracle\sql\bld_seqs.sql              */ 
/*                                                  */
/* ************************************************ */

col dummy     noprint format a1;

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

select 'DROP SEQUENCE "'||sequence_owner||'"."'||sequence_name||'";', 0 dummy
from all_sequences
where sequence_owner = upper('&&schema')    
UNION
select 'CREATE SEQUENCE "'||sequence_owner||'"."'||sequence_name||'" 
        INCREMENT BY '||increment_by||'
        START WITH '||last_number||'
        MINVALUE '||min_value||' MAXVALUE '||max_value||' 
        CACHE '||greatest(cache_size,2)||'
        '||decode(cycle_flag, 'Y', 'CYCLE', 'NOCYCLE')||'
        '||decode(order_flag, 'Y', 'ORDER', 'NOORDER')||';', 1 dummy
from all_sequences
where sequence_owner = upper('&&schema')
UNION
select 'GRANT SELECT ON "'||sequence_owner||'"."'||sequence_name||'" TO PUBLIC;', 2 dummy
from all_sequences
where sequence_owner = upper('&schema')
UNION
select 'CREATE PUBLIC SYNONYM "'||SEQUENCE_NAME||'" for "'||sequence_owner||'"."'||sequence_name||'";', 3 dummy
from all_sequences
where sequence_owner = upper('&&schema')
ORDER BY 2;

spool bld_all_seqs.rslt
/
spool off;
undefine schema;
commit;
