genseq.sql

--###################################################################
--#
--# Title:         genseq.sql
--# Description:   Script to generate CREATE SEQUENCE commands.
--#
--# Usage Note:    Should be run as '/ as sysdba'
--#                Should be run from a directory that is writable.
--#
--# Copyright:     ABCdba.com
--#
--###################################################################
 
col MIN_VALUE for 999999999999999
col MAX_VALUE for 999999999999999
col INCREMENT_BY for 999999999999999
col CACHE_SIZE for 999999999999999
col LAST_NUMBER for 999999999999999
 
ACCEPT seqown PROMPT 'Enter name of SOURCE sequence owner :';
ACCEPT newown PROMPT 'Enter name of TARGET sequence owner :';
 
SET PAGES 0
SET LINES 2000
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET VERIFY OFF
 
SPOOL create_seq.sql
SELECT
   'create sequence &newown..'||sequence_name||
   ' minvalue '||min_value||
   ' maxvalue '||max_value||
   ' increment by '||increment_by||
   decode(cycle_flag,'Y',' cycle ',' nocycle ')||
   decode(order_flag,'Y',' order ',' noorder ')||
   decode(cache_size,null,' nocache ',0,' nocache ',' cache '||cache_size||' ')||
   ' start with '|| last_number ||';'
FROM
   dba_sequences
WHERE
   sequence_owner='&seqown'
ORDER BY
   sequence_name
/
SPOOL OFF
 
SET PAGES 20
SET LINES 80
SET TRIMSPOOL OFF
SET FEEDBACK ON
SET VERIFY ON
© copyright 2001-2014 ABCdba.com | all rights reserved