Re: Having a fast way to drop a 10G bytes tablespace ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 13 Oct 1999 22:26:41 +0100
Message-ID: <939850210.2937.0.nnrp-02.9e984b29@news.demon.co.uk>

tsk, tsk
That's the Oracle 6 method.
(Also if you drop all the segments

like that you could end up with a
huge number of free extents in
the tablespace which could make
the drop tablespace take a long time)

try something like:

declare

    cursor c1 is
    select owner, segment_name, segment_type     from dba_segments
    where tablespace_name = 'xxxxxxxx';

begin

    for r1 in c1 loop

        begin
            dbms_utility.exec_ddl_statement(
                        'drop ' ||
                        segment_type || ' ' |||
                        owner || '.' segment_name
            );
            dbms_utility.exec_ddl_statement(
                    'alter tablespace xxxxxxxx coalesce'
            );
            --  optional dbms_output.put_line for okay
        exception
                when others then
                   -- optional dbms_output.put_line for failure
                    null;
        end;

    end loop;
end;

No promises for perfection in package and function names and odd typos. It also suffers from the problem that it doesn't address dependencies, but that is left as an exercise.

(I hope 'alter tablespace coalesce' counts as ddl, otherwise
you may have to change it to use dbms_sql)

(In 8.1 you would use

        execute immediate 'constructed string' )

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Connor McDonald wrote in message <3804836A.3CE5_at_yahoo.com>...

>Make sure the tablespace is empty
>
>spool dropall.sql
>select 'drop '||segment_type||' '||segment_name||';'
>from dba_segments
>where tablespace_name = 'XXX';
>spool off
>@dropall
>

Received on Wed Oct 13 1999 - 16:26:41 CDT