SQL Script to speed up Dropping an Oracle User/Schema by Truncating the underlying tables

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