Tuesday, February 11, 2014

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