Recreating the Temporary segment in a TEMPORARY Tablespace

In earlier versions of Oracle with the "TEMP" tablespace being Dictionary-Managed but
Contents=TEMPORARY, I could "recreate" or "shrink" the temporary segment without
an Instance restart by executing an "ALTER TABLESPACE TEMP DEFAULT STORAGE (PCTINCREASE 0)"
-- ie any ALTER command for the storage parameters would result in the temporary segment
being dropped [if not in use, immediately, else, after the last session using it exits].
Why would I do it ? So as to be able to resize the datafiles in the TEMP tablespace.
This could become necessary after a "runaway" query [a cartesian product] or some large index builds had extended the tablespace datafiles significantly. I would
be able to reclaim the disk space.

Now, with the TEMP tablespace a Locally-Managed, I find that I cannot "recreate" or "shrink"
the temporary segment as the ALTER TABLESPACE TEMP DEFAULT STORAGE command is not valid.

What other commands could I use ?
Creating another TEMPORARY TABLESPACE, changing user's TEMPORARY_TABLESPACE and then dropping the first tablespace is an option but one that I would not prefer.

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com Received on Mon Jul 14 2003 - 08:38:02 CDT