rem =========================================================================
rem 
rem                     recreate_sequences.sql
rem 
rem     Copyright (C) Oriole Software, 2000
rem 
rem     Downloaded from http://www.oriolecorp.com
rem 
rem     This script for Oracle database administration is free software; you
rem     can redistribute it and/or modify it under the terms of the GNU General
rem     Public License as published by the Free Software Foundation; either
rem     version 2 of the License, or any later version.
rem 
rem     This script is distributed in the hope that it will be useful,
rem     but WITHOUT ANY WARRANTY; without even the implied warranty of
rem     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
rem     GNU General Public License for more details.
rem 
rem     You should have received a copy of the GNU General Public License
rem     along with this program; if not, write to the Free Software
rem     Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem 
rem =========================================================================
--
--   Usage : @recreate_sequences <schema name>
--
--   This script is handy when transferring data between two databases
--   without recreating all of a user's objects.
--   When run (as a DBA) this script generates <schema name>_sequences.sql,
--   which allows to drop and recreate sequences with the current value
--   as starting point. Note that the current value as read from the 
--   dictionary (which is done) is NOT what sequence.nextval would return,
--   because sequence numbers are cached in memory - a gap at most equal
--   to the 'CACHE' value is to be expected.
--   Because GRANTs are also included in the script, the generated file
--   should not be run from a DBA account, but from the account which
--   owns the sequences.
--
--   Warning : since sequences are dropped, this will invalidate stored
--             procedures, functions and packages which reference them.
--             It is advised to recompile invalidated objects (free script
--             available at www.oriolecorp.com)
--
set linesize 512
set pause off
set echo off
set pagesize 0
set scan on
set verify off
set feedback off
set recsep off
spool &1._sequences.sql
column dummy noprint
select 1 dummy,
       'drop sequence "' || sequence_name || '"' || chr(10) || 
       '/' || chr(10)
       'create sequence "' || sequence_name || '"' || chr(10) || 
       'increment by ' || to_char(increment_by) || chr(10) || 
       'start with ' || to_char(last_number) || chr(10) || 
       decode(max_value,999999999999999999999999999,'nomaxvalue',
       'max_value ' || to_char(max_value)) || chr(10) || 
       'min_value ' || to_char(min_value) || chr(10) || 
       decode(cycle_flag, 'Y', 'nocycle', 'cycle') || chr(10) || 
       decode(cache_size, 0, 'nocache',
                         'cache ' || to_char(cache_size)) || chr(10) || 
       decode(order_flag,'Y','noorder','order') || chr(10) || 
       '/' || chr(10)
from dba_sequences
where sequence_owner = upper('&1')
union
select 2,
       'grant ' || p.privilege || ' on "' || s.sequence_name || '"' || chr(10)
        || 'to "' || p.grantee || '"' ||
                  decode(p.grantable, ' YES',' with grant option','')
        || chr(10) || '/' || chr(10)
from dba_tab_privs p,
     dba_sequences s
where p.table_name = s.sequence_name
  and  p.owner = s.sequence_owner
  and s.sequence_owner = upper('&1')
order by 1,2;
spool off