Il arrive parfois d’avoir besoin de supprimer tous les objets d’un schéma, mais, par facilité ou pour différentes raisons, sans supprimer l’utilisateur. Par défaut la base de données Oracle ne fournit pas d’ordre SQL simple pour effectuer une telle tache. Je vous propose ainsi le petit script suivant pour exécuter cette opération.

Le code assez simple à exécuter en temps que SYS est le suivant :
[sourcecode language= »sql »]
declare
cursor c_get_objects is
select owner, object_type,’"’||object_name||’"’||decode(object_type,’TABLE’ ,’ cascade constraints purge’,null) obj_name
from dba_objects
where object_type in (‘TABLE’,’VIEW’,’PACKAGE’,’SEQUENCE’,’PROCEDURE’,’FUNCTION’, ‘SYNONYM’, ‘MATERIALIZED VIEW’)
and owner in (‘MYUSER’);
cursor c_get_objects_type is
select owner, object_type, ‘"’||object_name||’"’ obj_name
from dba_objects
where object_type in (‘TYPE’)
and owner in (‘MYUSER’) ;
begin
for object_rec in c_get_objects
loop
execute immediate (‘drop ‘ || object_rec.object_type || ‘ ‘ || object_rec.owner || ‘.’ ||object_rec.obj_name);
end loop;
for object_rec in c_get_objects_type
loop
begin
execute immediate (‘drop ‘||object_rec.object_type || ‘ ‘ || object_rec.owner ||’.’ || object_rec.obj_name);
end;
end loop;
end;
/
[/sourcecode]
Et voila un beau schéma tout neuf.
Il se peut que des segments LOB restent coincés. Il faut alors se connecter avec l’utilisateur et purger la corbeille :
[sourcecode language= »sql »]
purge recyclebin ;
[/sourcecode]
Merci à http://arjudba.blogspot.com/2008/09/dropping-all-objects-in-schema.html