Skip to content

Generate Drop Sequence Statements (drop_sequence.gen)

What This Script Does

This generator script creates DROP SEQUENCE statements for sequences matching the specified criteria. It excludes system schemas to prevent accidental drops of critical sequences.

The Script

rem  drop_sequence.gen
rem
select 'drop sequence ' || sequence_owner || '.' || sequence_name || ';'
  from sys.dba_sequences
 where sequence_owner like upper('&owner')
   and sequence_name like upper('&sequence_name')
   and sequence_owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
 order by sequence_owner, sequence_name
/

Usage

-- Basic usage
@drop_sequence.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- sequence_name: Sequence name pattern (use % for wildcard)

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)
&sequence_name - Sequence name pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_SEQUENCES

Sample Output

drop sequence HR.EMPLOYEES_SEQ;
drop sequence HR.DEPARTMENTS_SEQ;
drop sequence SCOTT.TEST_SEQ;

Key Features

System Schema Protection: Excludes critical system schemas
Pattern Matching: Use wildcards to match multiple sequences
Clean Output: Simple DROP SEQUENCE statements
Sorted Results: Ordered by owner and sequence name

Common Use Cases

Drop All Sequences in Schema

@drop_sequence.gen
-- Enter: SCOTT for owner, % for sequence_name

Drop Sequences with Pattern

@drop_sequence.gen
-- Enter: % for owner, %_OLD for sequence_name

Drop Table Generator - Drop related tables
Object Analysis - View sequence details