Joan a 17:00, 26 abr 2011

2011-04-26T17:00:52Z

Pàgina nova

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
<pre>
select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;
</pre>

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:
<pre>
select * from user_objects
select * from dba_objects where owner='ASI2A01';
</pre>
<pre>
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__';
</pre>

Pràctica ASI-C6-ASGBD: Scripts amb Oracle - Historial de revisió

Joan a 17:00, 26 abr 2011