Drop all objects of a schema-

Pre-requisite-  (Very Important)

We have to login in particular schema of which we want to drop all objects. Otherwise it will drop all the objects of SYSTEM and other important table and views.

begin

for i in 1..3 loop

for r in (select object_name, object_type from user_objects

          order by object_type, object_name)

  loop

  begin

    if (r.object_type = 'MINING MODEL') then

     execute immediate ' begin dbms_data_mining.drop_model('||''''||

       r.object_name||''''||'); end;';

    elsif (r.object_type = 'TABLE') then

      execute immediate 'drop table "'||r.object_name

                    ||'" cascade constraints purge';

    else

       execute immediate 'drop '||r.object_type||' "'||r.object_name||'"';

    end if;

    exception when others then null;

  end;

  end loop;

end loop;

end;

/