Como apagar todos os objetos de um esquema utilizando o Comandos SQL

Olá pessoal,

este post é para apresentar um script muito interessante que apaga todos os objetos de um esquema, ou seja, deixa o esquema do seu espaço de trabalho vazio.

Lembro que ele não apaga as aplicações, mas apaga todos os objetos e dados do banco.

Passo 1: Logue no espaço de trabalho, entre no "SQL Workshop" > "Comandos SQL"


Passo 2: Copie o comando abaixo e cole na área de texto do Comandos SQL

------INÍCIO SCRIPT-----
--MUITO CUIDADO--
--Este script DROPA todos os objetos do banco de dados

DECLARE 
v_comando VARCHAR2(200);

BEGIN
--Primeiro dropamos as views materializadas, pois internamente o Oracle cria um objeto do tipo TABLE e só permite dropar esse objeto com o comando DROP MATERIALIZED VIEW.
FOR cur IN (SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'MATERIALIZED VIEW') LOOP

     v_comando := 'DROP MATERIALIZED VIEW "'||cur.object_name||'"';
     htp.p(v_comando);
     EXECUTE IMMEDIATE v_comando;

END LOOP;

--Depois dropamos as tabelas, pois devemos utilizar o comando cascade constraints para excluir em cascata as constraints da tabela
FOR cur IN (SELECT * FROM USER_TABLES) LOOP

     v_comando := 'DROP TABLE "'||cur.table_name||'" cascade constraints';
     htp.p(v_comando);
     EXECUTE IMMEDIATE v_comando;

END LOOP;

--A seguir dropamos os outros objetos (lembrando que package body e jobs são dropados de outra maneira)
FOR cur IN (SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE NOT IN ('PACKAGE BODY', 'JOB', 'LOB')  ) LOOP

     v_comando := 'DROP ' || cur.object_type  || ' "'||cur.object_name||'"';
     htp.p(v_comando);
     EXECUTE IMMEDIATE v_comando;

END LOOP;

--Comando para dropar as jobs (scheduler jobs)
FOR cur IN (SELECT * FROM USER_scheduler_jobs ) LOOP

     v_comando := 'DBMS_SCHEDULER.drop_job(job_name =>''' ||cur.job_name||''')';
     htp.p(v_comando);
     DBMS_SCHEDULER.drop_job(job_name => cur.job_name);

END LOOP;

--Observação: Caso tenha jobs do tipo JOBS que estão deprecated, consulte a documentação de como dropá-las.

END;

------FIM  SCRIPT-----

Observação: Caso esteja utilizando colunas em tabela do tipo LOB, os objetos do tipo LOB continuarão ocupando espaço em disco. Caso queira liberar o espaço, a tabela que contém o objeto do tipo LOB deve ser "purgada" da lixeira (RECYCLE BIN) com o comando:

PURGE TABLE <<nome tabela>>

Passo 3) Clique no botão "Executar"

Passo 4) Confira o resultado da execução no painel de Resultados localizado na parte inferior. Caso tenha ocorrido algum problema, tente identificar o motivo.

Observação: Normalmente o feedback final do Apex é de "Tabela eliminada.". Esse feedback é incorreto, pois o Comandos SQL do Apex informa apenas o resultado do primeiro comando executado.

Espero que tenha gostado!
Qualquer dúvida ou, sugestão, deixe no comentário!

Até a próxima!