Increase Oracle sequences by a percentage

gen_incr_sequences.sql

/* gen_incr_sequences.sql
 * Generates SQL script to increase Oracle sequences.
 *
 * For use in migration scenarios, e.g. with DMS or GoldenGate,
 * when access to the source database is difficult.
 * Run this
 *   - either as the target schema owner
 *   - or as an admin user (edit list of schema_owner first)
 * , then run the generated script "incr_sequences.sql" during cutover
 * on the target.
 *
 * 2023-01-25 Uwe Kuechler
 */

set lines 300 pages 0 trimspool on feed off
col script for a300 word_wrapped
-- When this runs as a batch script, you might also want to:
--set termout off echo off
spool incr_sequences.sql

------------------------------------------------------------------------------

prompt "spool incr_sequences.log"
/**********************************************
 * Adapt further down for multiple schemas
 **********************************************/
SELECT
    'alter sequence '
    || sequence_owner
    || '.'
    || sequence_name
    || ' restart start with '
    || CASE WHEN last_number > 99 THEN TRUNC( last_number * 1.2 ) ELSE last_number + 20 END
    || ';' cmd
FROM
    all_sequences
WHERE
    sequence_owner IN (USER) -- adapt here for multiple schemas
AND
    last_number > 1 -- do not increment unused sequences
;
prompt " **********************************************/"
prompt "spool off"

------------------------------------------------------------------------------

spool off
exit