Generate Drop User Statements (drop_user.gen)

What This Script Does

This generator script creates DROP USER CASCADE statements to remove database users and all their objects. It includes extensive protection against dropping critical system users.

The Script

rem  drop_user.gen
rem
select 'drop user ' || username || ' cascade;'
  from sys.dba_users
 where username like upper('&username')
   and username not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','FLOWS_FILES',
                        'MDSYS','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_PUBLIC_USER',
                        'DIP','ORACLE_OCM','XDB','ANONYMOUS','CTXSYS','XS$NULL','ORDDATA',
                        'ORDPLUGINS','SI_INFORMTN_SCHEMA','OLAPSYS','MDDATA','SPATIAL_WFS_ADMIN_USR',
                        'SPATIAL_CSW_ADMIN_USR','APEX_040200')
 order by username
/

Usage

-- Basic usage
@drop_user.gen

-- When prompted, enter:
-- username: Username pattern (use % for wildcard)

Parameters

The script prompts for:

&username - Username pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_USERS
DROP USER privilege

Sample Output

drop user TEST_USER cascade;
drop user DEV_USER cascade;
drop user APP_USER_OLD cascade;

Key Features

CASCADE Option: Drops all objects owned by the user
Extensive System User Protection: Excludes 20+ critical system users
Pattern Matching: Use wildcards to find users to drop
Sorted Output: Alphabetically ordered for easy review

Common Use Cases

Drop Test Users

@drop_user.gen
-- Enter: TEST% for username

Drop Old Application Users

@drop_user.gen
-- Enter: %_OLD for username

User Analysis - Review users before dropping
Kill Session Generator - Kill user sessions before dropping