Oracle CREATE SEQUENCE 文取得

get_ddl_sequences.sql

-- #!/bin/sh
-- #@(#) \$Id get_ddl_sequences.sh
-- #
-- # su - oracle
-- # export ORACLE_SID=xxxx
-- # export ORACLE_HOME=/usr/app/oracle/product/12.1.0
-- #
-- #export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
-- ${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF
--
--  CREATE SEQUENCE 文取得
--
--    SEQUENCEはROLLBACKしても元に戻らないし、またCACHEで複数の連番を一時的に
--    メモリに持つ都合、元のSTART WITHがそのまま保障されるとは限らない。
--
--  history
--    2018/08/05  CONST.LF → CONST.NL
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 80
orySET PAGESIZE 5000
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL get_ddl_sequences_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL get_ddl_sequences_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..sql
COLUMN CREATE_SEQUENCE  FORMAT A80
-- CREATE SEQUENCE文
SELECT  -- https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_6017.htm#i2067093  SQL language reference
    'CREATE SEQUENCE ' || US.SEQUENCE_OWNER || '.' || US.SEQUENCE_NAME || CONST.NL
 || 'INCREMENT BY '    || TO_CHAR(US.INCREMENT_BY) || CONST.NL
 || 'START WITH '      || TO_CHAR(US.LAST_NUMBER)  || CONST.NL
 || 'MAXVALUE '        || TO_CHAR(US.MAX_VALUE)    || CONST.NL
 || 'MINVALUE '        || TO_CHAR(US.MIN_VALUE)    || CONST.NL
 || DECODE(US.CYCLE_FLAG, 'Y', 'CYCLE', 'NOCYCLE') || CONST.NL
 || DECODE(US.CACHE_SIZE, 0, 'NOCACHE', 'CACHE ' || TO_CHAR(US.CACHE_SIZE)) || CONST.NL
 || DECODE(US.ORDER_FLAG, 'Y', 'ORDER', 'NOORDER') || ' ;' AS CREATE_SEQUENCE
FROM(
    SELECT COLUMN_VALUE AS OWNER FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA)  -- ユーザーが作成したスキーマのみ
    ) MS
INNER JOIN DBA_SEQUENCES US  -- https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-5867804F-1339-4CB6-9A56-6B6DCECB61BB.htm  reference
ON  US.SEQUENCE_OWNER = MS.OWNER
ORDER BY
    US.SEQUENCE_OWNER
  , US.SEQUENCE_NAME ;
SPOOL OFF
COLUMN CREATE_SEQUENCE  CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET TERMOUT ON
-- exit
-- EOF
-- #export NLS_LANG=en_US
-- # ---------------------- End of get_ddl_sequences.sh ------------------------------

参照
Oracle 定数
Oracle 純粋にユーザー側のスキーマのみを取得