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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
