RE: Recreating TEMP

Simon,

You only use the TEMP tablespace when performing a GROUP BY or a sort.

In your case, this will not be happening to the SYS account during the drop and recreate of the tablespace.

PS - change your drop tablespace command to:

SQL>DROP tablespace TEMP

   3>and DATAFILES;

There are no contents to drop - its a temporary space.

Also, consider using Locally Managed TBS for the TEMP space - it will make your life much more pleasant. You will not need to do this again. Balance (or match) the extent size of the TBS with the SORT_AREA_SIZE init parameter so that everything is used efficiently.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, June 07, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L

Hi all ,
I have 2 TEMPORARY tablespaces which I want to drop and recreate with better storage options. My worries:
1)If I connect as sysdba, what TEMP talespace will sysdba's session be using given that a select from dba_users shows SYS & SYSTEM users having TEMP as their TEMPORARY tablespace. Won't the dropping of TEMP leave sysdba in suspense over which TEMP to use ? Or as long as no sort operations, no need for TEMP ?
Waiting for your comments before:
SQL>DROP tablespace TEMP

   2>INCLUDING CONTENTS
   3>and DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE TEMP
> ................................;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  INET: waibals_at_mtn.co.ug

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).