Re: drop tablespace including contents -- very slow

From: James Lorenzen <james_lorenzen_at_allianzlife.com>
Date: Mon, 24 Jan 2000 20:39:32 GMT
Message-ID: <86ida3$em$1@nnrp1.deja.com>

Chris

The "DROP TABLESPACE" command will drop each table in the tablespace and the drop table will use rollback segments (and by extension, the redo logs). The command can be sped up by truncating all of the tables in the tablespace prior to dropping the tablespace.

I.E.
SPOOL drop_tables.sql
SELECT 'TRUNCATE TABLE ' || owner || '.' || table_name || ' ;' FROM dba_tables WHERE tablespace_name = 'target_tablespace' SPOOL OFF
@drop_tables.sql

The above command will create a list truncate table commands for the tables in the targeted tablespace, that list is then executed.

HTH
James

In article <86i2jv$ngn$1_at_nnrp1.deja.com>, chrisoc_at_ans.net wrote:
>
>
> I am running 8.1.5 on Solaris and using raw volumes for some
> of the tablespaces.
>
> I tried "drop tablespace raw_tabsp including contents" and
> was surprised to see the command still running after 30 minutes.
> It is almost 2 GB in size.
> I didn't think that this operation would use RBSes.
> What could be taking so much time?
>
> Chris O'Connor
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Life is complex; it has real and imaginary parts.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 24 2000 - 14:39:32 CST