User Object Removal Generator (drop-user.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Destructive Operation

This script generates and executes DROP statements that will permanently remove all objects belonging to the specified user. Always backup your data before running this script and verify the user is correct.

What This Script Does

This script provides Oracle database administration functionality via the drop-user.sql script.

The Script

rem drop_user.sql

set echo off

accept owner char prompt 'Enter value for owner to be DROPPED!: '

set term off
set pause off
set pages 0
set feed off
spool drop_user1.sql
select
'drop '||object_type||' '||owner||'.'||object_name||';'
from dba_objects
where object_type not in ('INDEX', 'PACKAGE BODY')
and owner = upper('&owner')
order by
   decode( object_type,
       'TABLE', '0',
       'VIEW','1',
       'PACKAGE','2',
       object_type)
/
spool off
undefine owner
set term on
set echo on
set feed on

start drop_user1.sql
set echo off

What This Script Does

The drop-user.sql script generates and automatically executes DROP statements for all database objects owned by a specified user. It creates a dependency-aware order for dropping objects, ensuring that dependent objects are removed before their dependencies. This is useful for completely cleaning up a user’s schema or preparing for user removal.

Usage

⚠️ CRITICAL WARNING: This script immediately executes the generated DROP statements. Always backup your data first!

SQL> @drop-user.sql
Enter value for owner to be DROPPED!: TESTUSER

Parameters:

• owner: Schema owner name (case-insensitive, will be converted to uppercase)

Required Privileges

• SELECT privilege on DBA_OBJECTS (usually requires DBA role)
• DROP privileges on all object types for the target schema
• For non-DBA users, modify script to use USER_OBJECTS for own objects only

Generated Output Files

The script creates a temporary file drop_user1.sql containing all the DROP statements:

-- Example generated content in drop_user1.sql
drop TABLE TESTUSER.CUSTOMERS;
drop TABLE TESTUSER.ORDERS;
drop TABLE TESTUSER.ORDER_ITEMS;
drop VIEW TESTUSER.CUSTOMER_SUMMARY;
drop VIEW TESTUSER.SALES_REPORT;
drop PACKAGE TESTUSER.BUSINESS_LOGIC;
drop FUNCTION TESTUSER.CALCULATE_TAX;
drop PROCEDURE TESTUSER.UPDATE_INVENTORY;
drop SEQUENCE TESTUSER.ORDER_SEQ;
drop SYNONYM TESTUSER.PROD_CUSTOMERS;

Drop Order Logic

The script uses intelligent ordering to minimize dependency conflicts:

1. Tables (Priority 0): Dropped first to remove primary dependencies
2. Views (Priority 1): Dropped after tables they depend on
3. Packages (Priority 2): Dropped after dependent objects
4. Other Objects: Alphabetical order by object type

Excluded Object Types

• INDEX: Automatically dropped with their tables
• PACKAGE BODY: Dropped automatically when package specification is dropped

Common Use Cases

1. Schema Cleanup

   • Remove test or development schemas completely
   • Clean up after failed installations or migrations

2. User Removal Preparation

   • Clear all objects before dropping the user account
   • Ensure clean user deletion without dependency errors

3. Development Environment Reset

   • Reset development schemas to clean state
   • Remove all objects for fresh deployment

4. Schema Migration

   • Clean target schema before importing new objects
   • Prepare for schema restructuring

Safety Features

Interactive Confirmation

The script prompts for the schema name, allowing verification before execution.

Generated Script Review

The script creates drop_user1.sql which can be reviewed before execution (though this script executes it automatically).

Dependency Ordering

Intelligent ordering reduces (but doesn’t eliminate) dependency errors.

Risk Mitigation Strategies

Before Running

1. Full Database Backup:

   -- Export the schema first
   expdp system/password schemas=TESTUSER directory=DATA_PUMP_DIR dumpfile=testuser_backup.dmp

2. Verify Schema Contents:

   SELECT object_type, COUNT(*)
   FROM dba_objects
   WHERE owner = 'TESTUSER'
   GROUP BY object_type;

3. Check Dependencies:

   SELECT owner, name, type, referenced_owner, referenced_name
   FROM dba_dependencies
   WHERE referenced_owner = 'TESTUSER';

Manual Review Option

To review before execution, modify the script:

-- Comment out these lines to prevent automatic execution
-- start drop_user1.sql
-- set echo off

-- Add this instead:
prompt Review drop_user1.sql before executing manually

Troubleshooting Common Issues

Dependency Errors

Some objects may fail to drop due to dependencies:

ORA-00942: table or view does not exist
ORA-02291: integrity constraint violated - parent key not found

Solutions:

• Run the generated script multiple times
• Manually review and reorder problematic statements
• Drop constraints first in complex scenarios

Permission Errors

ORA-01031: insufficient privileges

Solutions:

• Ensure user has DROP privileges on target schema objects
• Use DBA account for complete object removal
• Grant specific DROP privileges if needed

Object Not Found

Some objects may already be dropped or dependent on others:

ORA-00942: table or view does not exist

This is normal when objects have already been dropped by dependency cascading.

Advanced Customization

Include Additional Object Types

-- Add constraints, triggers, etc.
where object_type not in ('PACKAGE BODY')  -- Remove INDEX exclusion

Specific Object Type Focus

-- Only drop tables and views
where object_type in ('TABLE', 'VIEW')
and owner = upper('&owner')

Add Cascade Options

-- For tables with foreign keys
'drop '||object_type||' '||owner||'.'||object_name||' cascade constraints;'

Related Scripts

• duser.sql - User information analysis
• dobject.sql - Object analysis by owner
• dtable.sql - Table structure analysis

Alternative Approaches

Using Oracle’s CASCADE Option

-- Single command to drop user and all objects
DROP USER testuser CASCADE;

Selective Object Removal

-- Drop only specific object types
SELECT 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;'
FROM user_tables;

Best Practices

1. Always Backup First: Export schema before running destructive operations
2. Test Environment: Always test on non-production systems first
3. Review Generated Scripts: Examine drop_user1.sql before execution when possible
4. Document Dependencies: Understand object relationships before mass deletion
5. User Verification: Double-check the schema name before execution

Script Output

The script provides minimal output during execution:

• Prompts for user input
• Creates and executes drop statements
• Shows any errors encountered during drops

Limitations

• May require multiple runs for complex dependency chains
• Cannot handle circular dependencies automatically
• Does not backup data before deletion
• Limited error handling for complex scenarios
• Temporary file drop_user1.sql remains after execution