The Oracle (tm) Users' Co-Operative FAQ

Dropping a tablespace seems to take a very long time - how can I speed it up ?

Author's name: Connor McDonald

Author's Email:

Date written: November 11, 2001

Oracle version(s): 7.3+

Dropping a tablespace seems to take a very long time - how can I speed it up ?

There are two reasons why dropping a tablespace takes a lot of time.

A way to avoid this is to explicitly drop the segments in the tablespace before dropping the tablespace itself. The overall operation is faster, and if the session/instance crashes, then there will not be a massive undo operation to performed. Some sample timings are shown below:

Standard 'drop' on a dictionary managed tablespace

(prelim - we stick 1000 segments in "random" order in the tablespace)

SQL> begin
  2  for i in 1 .. 500 loop
  3  execute immediate 'create table tab'||(i*2)||' ( x number ) tablespace sample_data '||
  4  'storage ( initial 16k next 16k )';
  5  end loop;
  6  for i in 1 .. 500 loop
  7  execute immediate 'create table tab'||(i*2-1)||' ( x number ) tablespace sample_data '||
  8  'storage ( initial 16k next 16k )';
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> drop tablespace sample_data including contents;

Tablespace dropped.

Elapsed: 63 seconds

Drop objects first then tablespace (dictionary managed)

(prelim as before)

SQL> drop table tab1;

Table dropped.

SQL> drop table tab2;

Table dropped.

(every 10 tables we issue)

SQL> alter table SAMPLE_DATA coalesce;

Tablespace altered.


(Total) Elapsed: 55 seconds

SQL> drop tablespace sample_data;

Tablespace dropped.

Elapsed: 00:00:00.80

NB: As pointed out by the FAQ owner, if you wish to use PL/SQL to automate this, you will not get any joy using 'alter tablespace ... coalesce' as a recursive SQL (ie within the PL/SQL routine). You would need to code

execute immediate 'alter session set events ''immediate trace name drop_segments level n''';

where 'n' is the tablespace ID plus 1.

If you are using locally managed tablespaces, then you are insulated in some respect, namely, the tablespace (including its contents) can be dropped more quickly. The same test above gave 50 seconds for a plain 'drop tablespace' and 66 seconds for a preliminary drop of the objects. There is probably still a good case for the preliminary drop to avoid the resource pain of rolling back to tablespace drop in the event of a session crash.

Back to index of questions

Further reading: N/A

Back to top

Back to index of questions