--------------- APAGANDO OBJETOS DE UM USUÁRIO ------------------------------------

 

show user;

RESPOSTA ==> USER is “MON_USER”

 

BEGIN

   for rec1 in (select decode(object_type, 'TABLE', 'A', '')||OBJECT_TYPE AS ORDEM_TYPE, object_type, object_name from user_objects where object_type not in ('PACKAGE BODY','INDEX','TRIGGER', 'LOB', 'JOB') order by 1) loop

     EXECUTE IMMEDIATE 'drop ' || rec1.object_type || ' ' || rec1.object_name || case rec1.object_type when 'TABLE' then ' cascade constraints' when 'TYPE' then ' FORCE' else '' end;

   end loop;

       

   for rec in (select job_creator, job_name from user_SCHEDULER_JOBS) loop

       DBMS_SCHEDULER.DROP_JOB(JOB_NAME => '"'||rec.job_creator||'"."'||rec.job_name||'"', defer=>false, force=>false);

   end loop;

 

   for rec in (select job from user_JOBS) loop

       DBMS_JOB.REMOVE(job => rec.job);

   end loop;

 

exception when others then

   raise_application_error(-20001,'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);

END;

/

--Limpa a Lixeira a nível de Usuário

PURGE RECYCLEBIN;

/

 

veja dicas em :

 

http://olivertconsultoria.blogspot.com/2013/09/?m=0

http://olivertconsultoria.blogspot.com/2013/09/como-ler-o-alertlog-diretamente-do.html