Re: Quick way to drop all objects in a schema

Hi John,

If you are using Oracle TYPE's, you need to have a few extra bits.  Here's
the script I use (we don't use DB Links) ...

declare
    l_n_del        pls_integer ;
    l_n_failed    pls_integer ;
    l_sql        varchar2 (2000) ;
begin
    for i in 1 .. 2 loop
        l_n_del := 0 ;
        l_n_failed := 0 ;
        for rec in (select * from dba_objects where owner = upper(:schema)
and object_type in ('PACKAGE', 'SEQUENCE', 'TABLE', 'TYPE', 'VIEW',
'SYNONYM', 'FUNCTION', 'PROCEDURE')) loop
            l_sql := 'drop ' || rec.object_type || ' ' || :schema || '.' ||
rec.object_name || (case rec.object_type when 'TABLE' then ' CASCADE
CONSTRAINTS' else '' end) ;
            begin
                execute immediate l_sql ;
                l_n_del := l_n_del + 1 ;
            exception
                when others then
                    dbms_output.put_line ('Failed : ' || l_sql) ;
                    dbms_output.put_line ('  ' || sqlerrm) ;
                    l_n_failed := l_n_failed + 1 ;
            end ;
        end loop ;
        dbms_output.put_line ('On pass ' || i || ' I dropped ' || l_n_del ||
' objects.  Failed to drop ' || l_n_failed || ' objects') ;
        exit when l_n_failed = 0 ;
    end loop ;
end ;

On Thu, Nov 19, 2009 at 10:03 PM, Dunbar, Norman <
norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

> Morning  John,
>
> I tend to do something like the following (typed off top of head - there
> may be typos!)
>
> begin
>  FOR x IN (SELECT table_name FROM user_tables) loop
>    execute immediate 'drop table ' || x.table_name || ' cascade
> constraints purge';
>  end loop;
> end;
> /
>
> begin
>  FOR x IN (SELECT object_type, object_name FROM user_objects
>            WHERE object_type NOT IN ('PACKAGE BODY','UNKNOWN','DATABASE
> LINK')) loop
>    execute immediate 'drop ' || x.object_type || ' ' || x.object_name;
>  end loop;
> end;
> /
>
> The reason I don't drop database links is purely because we use the
> above code to drop a test schema before refreshing it from live. We do
> not want the database links to be recreated pointing at whatever other
> production databases we use! We want to keep them pointing at test
> databases.
>
> We don't drop the schema because that messes up privs granted TO the
> schema from other schemas that we are not refreshing. Grants made FROM
> the test schema are refreshed when we import, so that's not a worry.
>
> HTH
>
> Cheers,
> Norm.
>
>
> Norman Dunbar
> Contract Oracle DBA
> CIS Engineering Services
> Internal : 7 28 2051
> External : 0113 231 2051
>
>
>

Other related posts: