Title: Speeding Up Drop User

      This Code of the Week entry comes from Jagdeep Singh Anand, a project manager and
      system analyst, for Kuro Soft Exports in New Delhi, India.

      Dropping Oracle users having huge schemas has always been time consuming! This
      script drops an Oracle database user much faster than the conventional DROP USER
      command.

      /* This script asks username to be dropped */
      set echo off
      set head off
      set verify off
      set linesize 200
      set pages 0
      set feedback off
      set term on
      undefine p_user
      def p_user = &&p_user
      Prompt Generating Script To Drop User
      set term off
      SPOOL DROP_USER.sql
      SELECT 'TRUNCATE TABLE ' || OWNER ||'.'||OBJECT_NAME || ' ;'
      FROM   DBA_OBJECTS
      WHERE  OWNER = UPPER('&p_user')
      AND    OBJECT_TYPE = 'TABLE'
      UNION
      SELECT 'DROP TABLE ' || OWNER ||'.'||OBJECT_NAME || ' CASCADE;'
      FROM   DBA_OBJECTS
      WHERE  OWNER = UPPER('&p_user')
      AND    OBJECT_TYPE = 'TABLE'
      UNION
      SELECT 'DROP ' || OWNER ||'.'|| OBJECT_TYPE || ' ' || OBJECT_NAME || ';'
      FROM   DBA_OBJECTS
      WHERE  OWNER = UPPER('&p_user')
      AND    OBJECT_TYPE IN ('PROCEDURE','PACKAGE','PACKAGE BODY', 'FUNCTION', 'SEQUENCE')
      order by 1 desc
      /
      SPOOL OFF
      set term on
      Prompt Dropping User Objects
      set term off
      START DROP_USER.SQL
      set term on
      Prompt Dropping User
      set term off
      DROP USER &P_USER CASCADE;
      set pages 24
      set head on
      set verify on
      set feedback on
      undefine p_user
      set term on
      set echo on