Apagando todos objetos de um esquema

É muito comum ter várias aplicações numa única base do Oracle organizadas por esquemas. Se for este o seu caso, você se verá com alguma frequência para migrar um esquema inteiro da base de produção para homologação, do teste para homologação e por aí vai. Exportar os objetos e importar é muito simples, tanto com utilizando o IMP e EXP comuns até a versão 9i como com o Data Pump no 10g em diante:

Com o imp e exp:

exp usuario@nome_base_origem file=dump_esquema.dmp owner=nome_esquema
imp usuario@nome_base_destino file=dump_esquema.dmp fromuser=nome_esquema touser=nome_esquema

Com o data pump:

expdp usuario@nome_base_origem dumpfile=data_pump_dir dumpfile=dump_esquema.dmp schemas=nome_esquema
impdp usuario@nome_base_destino dumpfile=data_pump_dir dumpfile=dump_esquema.dmp schemas=nome_esquema

Bom, até aí não há muito motivo para espanto, é algo trivial. O detalhe é quando você precisa apagar todos os objetos já existentes antes de importar os novos. Para isso, um pouco de SQL sempre ajuda:

SELECT 'DROP TABLE ' || owner || '.' || table_name || ' CASCADE CONSTRAINTS;'
  FROM dba_tables
  WHERE owner = 'NOME_ESQUEMA'';

SELECT 'DROP ' || object_type || ' ' || owner || '.' || object_name || ';'
  FROM dba_objects
  WHERE
    object_type != 'PACKAGE BODY' AND
    owner = 'NOME_ESQUEMA';

É claro que você poderia criar uma função para fazer tudo isso (limpar os objetos e importar o dump) numa tacada só. Mas eu diria que dois comandos SQLs não são motivo para tanto. 🙂