Re: Quick way to drop all objects in a schema

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Fri, 20 Nov 2009 06:18:18 +1100
Message-ID: <d282b3ab0911191118k369fff86i80b778a87fa4a5cd_at_mail.gmail.com>

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_at_environment-agency.gov.uk> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 19 2009 - 13:18:18 CST