Empty a schema(s) without dropping

#############################################
#
# Empty schema(s) script generation script
#
#
#############################################

# Check listof users on the database
sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select ''''||username||''','
 from dba_users
where username NOT in
('ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBA_TOOLS','DBSNMP','DIP','DMSYS', 'ESMDBA','ESMDBA1','EXFSYS','HGIOSD','JPMCEPV','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','OPS$ORACLE','ORACLE', 'ORACLE_COM','ORDDATA','ORDPLUGINS','ORDSYS','OSDAUD','OUTLN','SCOTT','SECURITY','SI_INFORMTN_SCHEMA','SRDF_DBA', 'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')
order by 1
/
END

# Generate the drop commands
for z in SED
do
echo -e "Enter list of names as in 'TEST','ONE','TWO'  :"
read USERS

for i in 1
do
echo -e "
spool sed_user_drop.log

select object_type,count(*) from dba_objects where owner in (${USERS});

"

echo -e "
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,object_name, object_type
                  FROM   dba_objects
                  WHERE owner in (${USERS}) and  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE','SYNONYM','TYPE','TYPE BODY'))
LOOP
    BEGIN
     IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || ' CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || '';
      END IF;

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"');
    END;
  END LOOP;
END;
/



BEGIN
  FOR cur_rec IN (SELECT owner,object_name, object_type
                  FROM   dba_objects
                  WHERE owner in (${USERS}) and  object_type IN ('MATERIALIZED VIEW'))
LOOP
    BEGIN
     IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || ' CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || '';
      END IF;

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"');
    END;
  END LOOP;
END;
/


prompt If required purge recycle_bin

purge dba_recycle_bin;

select object_type,count(*) from dba_objects where owner in (${USERS});

prompt confirm that all objects are dropped apart from DB LINK

"
done > sed_drop_user.info
echo -e "\nCheck the details in: sed_drop_user.info
done