Pràctica ASI-C6-ASGBD: Scripts amb Oracle

script per eliminar tots els objectes d'un usuari. Això és interessant per eliminar tots els objectes d'un usuari, encara que el més pràctic és eliminar un usuari i tornar-lo a crear.

• http://jmatrix.net/dao/case/case.jsp?case=7F000001-E22F2B-10CCCCB2924-862

select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;

Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:

purge recyclebin;

This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects
select * from dba_objects where owner='ASI2A01';

select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from dba_objects where owner='ASI2A01';

select 'drop '||object_type||' '|| owner || '.' || object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from dba_objects where owner LIKE 'ASI2A__';