Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database. If you need to have us drop and re-create your user please open support ticket and we will complete this for you.

If you don’t have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.

select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;','')
from user_objects

Then, I normally purge the recycle bin to really clean things up. To be honest, I don’t see a lot of use for oracle’s recycle bin, and wish i could disable it… but anyway:

purge recyclebin;

 Another option is to use this procedure which integrates all steps.

BEGIN
  FOR cur_rec IN (SELECT object_name, object_type
                  FROM   all_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE') AND 
                  owner = '<schema_name>')
  LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || 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;
/