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.
Para quem está migrando dados, excelente Store Procedure. Parabéns!
ResponderExcluirParabéns Bruno! Ajudou muito.
ResponderExcluir