Re: drop temp tablespace takes forever

> 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

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown