cleanup the schema Be care full Details

 I use the following script to cleanup the schema.
 Since I use "CASCADE CONSTRAINTS" for table it doesn't throw me an error.

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool c:\temp\drop_objects.lst
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||';'   FROM USER_OBJECTS
WHERE OBJECT_TYPE <> 'TABLE' AND OBJECT_TYPE <> 'INDEX' AND OBJECT_TYPE<>'PACKAGE BODY' AND OBJECT_TYPE<>'TRIGGER'  AND OBJECT_TYPE<>'LOB'
UNION ALL
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||' CASCADE CONSTRAINTS;'   FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE';
spool off;
@c:\temp\drop_objects.lst
prompt 'Purging the recycle bin...'
purge recyclebin;
host del c:\temp\drop_objects.lst
---exit;




=======================================================================
 Hoe to drop all Schema Objects in Oracle 

To drop all schema objects in Oracle database including tables, sequences, indices, views, materialized views, materialized view logs, and all other object like scheduled jobs run the following PL/SQL block:
SQL> create table LIMA (id varchar2(200));
table created


SQL> select * from tab;

LIMA                           TABLE

SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME || DECODE(OBJECT_TYPE,'TYPE',' FORCE') CODE
  4        FROM USER_OBJECTS
  5       WHERE OBJECT_TYPE NOT IN ('INDEX','LOB','TRIGGER','TABLE','PACKAGE BODY','JOB')
  6       ORDER BY 1;
  7    CURSOR c2 IS
  8      SELECT 'DROP TABLE ' || TABLE_NAME || ' CASCADE CONSTRAINTS'  CODE
  9        FROM USER_TABLES
 10       WHERE NESTED = 'NO' AND TABLE_NAME NOT LIKE 'MLOG$%'
 11       ORDER BY 1;
 12    CURSOR c3 IS
 13      SELECT OBJECT_NAME
 14        FROM USER_OBJECTS
 15       WHERE OBJECT_TYPE = 'JOB'
 16       ORDER BY 1;
 17  BEGIN
 18    FOR X IN c3 LOOP
 19      DBMS_SCHEDULER.DROP_JOB (job_name => X.OBJECT_NAME);
 20    END LOOP;
 21    FOR X IN c1 LOOP
 22      EXECUTE IMMEDIATE (X.CODE);
 23    END LOOP;
 24    FOR X IN c2 LOOP
 25      EXECUTE IMMEDIATE (X.CODE);
 26    END LOOP;
 27    EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
 28  END;
 29  /
SQL> select * from tab;
SQL> /
SQL>




=======================================================================
 Dangerous XXXXX  Dangerous Do Things
 How to truncate all tables in oracle schema

SQL> select * from lima;

333333
333333
333333
333333
SQL> create table Kaium (id varchar2(200));
SQL>
SQL>
SQL>
SQL> insert into kaium s('333333')
  2
SQL>
SQL> insert into kaium    values    ('333333')
  2  /
SQL> /
SQL> /
SQL> /
SQL> /
SQL> select * from kaium;

333333
333333
333333
333333
333333
SQL> begin
  2  for r in (select table_name from user_tables order by table_name)
  3    loop
  4    begin
  5     execute immediate 'truncate table '||r.table_name;
  6     exception when others then null;
  7    end;
  8    end loop;
  9  end;
 10  /
SQL> select * from kaium;
SQL> select * from lima;
SQL>
SQL>
SQL>
SQL>

               Warning The script below will remove all tables 
                       under the current schema

begin
for r in (select table_name from user_tables order by table_name)
  loop
  begin
   execute immediate 'drop table '||r.table_name
                     ||' cascade constraints purge';
   exception when others then null;
  end;
  end loop;
end;
/

=======================================================================

 How to truncate all user tables

declare

begin

for c1 in (select table_name, constraint_name from user_constraints) loop
    begin
        execute immediate ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
    end;
end loop;

for t1 in (select table_name from user_tables) loop
    begin
        execute immediate ('truncate table '||t1.table_name);
    end;
end loop;

for c2 in (select table_name, constraint_name from user_constraints) loop
    begin
        execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
    end;
end loop;

end;
/