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

The Oracle (tm) Users' Co-Operative FAQ

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.

• If the tablespace contains a lot of objects, then this is a massive recursive dictionary operation that is being undertaken. (Even if the tablespace is empty, there may still be a lot of free space entries which may need to be cleaned up)
• All of these dictionary operations need to be recorded in rollback, just in case the operation fails or is terminated in some way.

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.
etc.
(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.

Further reading: N/A