Script that export sequence object from Oracle database

expSeq.sql

	set linesize 200
	set pagesize 0
	set trimspool on
	set heading off
	set feedback off
	spool /tmp/impSeq.sql
	select '-- delete sequence' from dual;
	select 'DROP SEQUENCE ' || SEQUENCE_NAME || ';' from USER_SEQUENCES;
	select '-- create sequence' from dual;
	select 'CREATE SEQUENCE ' || SEQUENCE_NAME ||
	' START WITH ' || LAST_NUMBER ||
	' INCREMENT BY ' || INCREMENT_BY ||
	' MINVALUE ' || MIN_VALUE ||
	' MAXVALUE ' || MAX_VALUE ||
	case CYCLE_FLAG when 'Y' then ' CYCLE' else ' NOCYCLE' end ||
	' CACHE ' || CACHE_SIZE || ';'
	from USER_SEQUENCES;
	select 'quit' from dual;
	spool off
	quit

view raw gistfile1.sql hosted with ❤ by GitHub

Usage

sqlplus user/pass @expSeq.sql

Example

bash-2.05$ sqlplus mais/mais @expSeq.sql

SQL*Plus: Release 9.2.0.6.0 - Production on 日 Dec 4 01:19:02 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
に接続されました。
-- シーケンスを削除する
DROP SEQUENCE aaaaSEQ;
DROP SEQUENCE bbbbSEQ;
DROP SEQUENCE ccccSEQ;
(省略)
-- シーケンスを生成する
CREATE SEQUENCE aaaaSEQ START WITH 26857 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999 CYCLE CACHE 20;
CREATE SEQUENCE bbbbSEQ START WITH 34343 INCREMENT BY 1 MINVALUE 30000 MAXVALUE 49999 CYCLE CACHE 20;
CREATE SEQUENCE ccccSEQ START WITH 33367 INCREMENT BY 1 MINVALUE 30000 MAXVALUE 49999 CYCLE CACHE 20;
(省略)
quit
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Productionとの接続が切断されました。