Here is a SQL Script to speed up Dropping an Oracle User/Schema by Truncating/Dropping the underlying tables first:
set linesize 255
set pages 0
set echo off
set feedback off
set term on
set head off
set verify off
undefine which_user_p
def which_user_p = &&which_user_p
Prompt Generating the SQL script To Drop a User by Truncating the encapsulated Tables first
set term off
SPOOL DROP_USER_TRUNC_TABLES.sql
SELECT 'TRUNCATE TABLE ' || OWNER ||'.'||OBJECT_NAME || ' ;'
FROM DBA_OBJECTS
WHERE OWNER = UPPER('&which_user_p')
AND OBJECT_TYPE = 'TABLE'
UNION
SELECT 'DROP TABLE ' || OWNER ||'.'||OBJECT_NAME || ';'
FROM DBA_OBJECTS
WHERE OWNER = UPPER('&which_user_p')
AND OBJECT_TYPE = 'TABLE'
UNION
SELECT 'DROP ' || OWNER ||'.'|| OBJECT_TYPE || ' ' || OBJECT_NAME || ';'
FROM DBA_OBJECTS
WHERE OWNER = UPPER('&which_user_p')
AND OBJECT_TYPE IN ('PROCEDURE','PACKAGE','PACKAGE BODY', 'FUNCTION', 'SEQUENCE')
order by 1 desc
/
DROP USER &which_user_p CASCADE;
SPOOL OFF
