Re: drop temp tablespace takes forever

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 18 Oct 2006 14:33:05 -0700
Message-ID: <1161207180.747101@bubbleator.drizzle.com>

Brian Peasland wrote:

>> Temporary tablespaces are not datafiles ... they are tempfiles. Go to
>> tahiti.oracle.com and look up the correct syntax.

>
> The OP has the correct syntax. Here is an example (Oracle 9.2.0.7 on Sun
> Solaris):
>
> SQL> create temporary tablespace my_temp
> 2 tempfile '/my_host/oradata1/gasl/my_temp01.dbf' size 10m;
>
> Tablespace created.
>
> SQL> drop tablespace my_temp including contents and datafiles;
>
> Tablespace dropped.
>
> And I did verify that my_temp01.dbf was removed from the
> /my_host/oradata1/gasl directory.
>
> Note: "my_host" is not the actual value but has been modified for this
> example.
>
>
> I agree that these are tempfiles, but the DROP INCLUDING will work with
> DATAFILES specified. In fact, according to the Oracle docs, there is no
> INCLUDING CONTENTS AND TEMPFILES option.
>
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9004.htm#i2133717
>
> Cheers,
> Brian

Thanks. I'll update my information.

What made no sense to me was "INCLUDING CONTENTS" but I guess with GTT's it might be important.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Oct 18 2006 - 16:33:05 CDT