Re: Quick way to drop all objects in a schema
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx
- Date: Fri, 20 Nov 2009 06:18:18 +1100
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:
