Re: drop tablespace takes forever

In article <92c2ob$n5f$1_at_nnrp1.deja.com>,   susana73_at_hotmail.com wrote:
> Hi,
>
> I am trying to drop a tablespace by 'drop tablespace ts including
> contents' and it hangs for almost 10 hours now. I did not drop the
 user
> because drop user also takes forever(I ctrl-C it). The tablespace
> contains some object which has many extents which is not good. Does
 it
> affect? Does 'drop tablespace' actually performs a SQL DELETE or it
> DROP objects one by one?? Any ideas?
>
> The tablespace is about 5GB and about 75% filled. I don't notice any
> performance problem on this instance until I see this.
>
> Thanks,
> Susan
>
> Sent via Deja.com
> http://www.deja.com/
>

As was stated in the other responses, the problem is the drop table command. You are basically issuing a 'delete from' command which is issuing redo logs, and presumably archive logs, etc. Issuing a 'truncate' command does not cost you the redo logs and should speed up the process immensely. 5G will still take some time but not 10 hours.

As stated in the manual:

drop ... table ...

table is the name of the table, object table, or index-organized table to be dropped. Oracle automatically performs the following operations: () Removes all rows from the table (as if the rows were deleted). () Drops all the table’s indexes and domain indexes, regardless of who created them or whose schema contains them. () If you drop a range-partitioned or hash-partitioned table, all the table partitions are also dropped. If you drop a composite-partitioned table, all the partitions and subpartitions are also dropped. () For a domain index, this statement invokes the appropriate drop routines.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/