Drop시 걸리시 시간 Check

The following command takes very long to complete:

SQL> drop tablespace <tablespace name> including contents and datafiles;

Dropping the tablespace only with contents completes much faster.
But whenever datafile clause is included in the drop tablespace statement it takes hours to complete.

Cause
SQL trace on the drop tablespace command would show the query below is consuming most of the time:

select distinct ind.bo#, ind.type#
from ind$ ind, indcompart$ icp1, indsubpart$ isp1
where isp1.ts#=:1
and isp1.pobj#=icp1.obj#
and icp1.bo#=ind.obj#
and ind.type# <> 8
and exists
(select *
from indcompart$ icp2, indsubpart$ isp2
where icp2.bo# = icp1.bo#
and icp2.obj#=isp2.pobj#
and isp2.ts# <> :1)

You are frequently creating a lot of new partitions and dropping old partitions in the tablespace.
You are not gathering optimizer statistics for SYS schema.

The lack of statistics on SYS schema is the cause for this slowness.

Solution
Gathering statistics on SYS schema will help optimizer to select a good execution plan for the above mentioned recursive query.

You can gather statistics for the SYS schema using the following procedure:

SQL> conn / as sysdba
SQL> execute dbms_stats.gather_schema_stats('SYS');