Quando precisamos "Limpar" um schema/usuário no Oracle, utilizamos o comando:
DROP USER schema
CASCADE;
O problema disso é ter o trabalho de recriar o usuário e conceder todas as permissões (Grants) novamente.
Porém...
Podemos utilizar este Script que apaga (DROP) todos os objetos do schema, facilitando assim muito o nosso trabalho.
-- ORACLE 11g
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;
/
-- ORACLE 10g
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||'"', 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;
/
Abs,
Bruno Duarte.