Re: temp tablespace

Mitchell,
It depends on your situtation. You can check your tempspace is in use by querying dba_segment segment_type ='TEMPORARY'. If it returns no rows then you can safely offline and online.Otherwise you haveto create another tempspace and altering all your user who are assigned your old temp tablespace.
HTH,
Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Fri, 02 Feb 2001 14:31:23 -0800

Rafiq

I think I means not 'PERMANENT' but 'TEMPORARY'. in my case. After I alter tablespace mytemptablespace coalesce; it is still the same. No any extent released.
I am not sure I can take temp tablespace offline or should I add another one and off./on the old one
since we operate at 7/24.

Mitchell

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, February 02, 2001 11:45 AM

>
> Alex,
> Yes you are right if temp tablespace is 'TEMPORARY'. The situation he has
> described is pointing that temp tablespace is 'PERMANENT' and thats why
> his extents were not released/coalesced.
>
> Besides , with temp TEMPORARY tablespace , there are problems that space
is
> not completely released even after bouncing database and you have to
> offline and online temp tablespace to get release used space upto 99%.
> I am talking about 7GB+ tempspace on 7.3.4.4(1999) NCR UNIX 3.02 and that
> was my personal experience at that time.
>
> Regards
> Rafiq
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Thu, 01 Feb 2001 14:12:31 -0800
>
> I disagree. pctincrease should be 0, you do not need to coalesce
tablespace
> if it is temporary. Segments in temporary tablespace are not dropped
after
> SQL statement execution ends. But if you want to drop all segments from
> temporary tablespace the easiest way is:
>
> alter tablespace <tablespace name> pctincrease <current value of
> pctincrease>
>
> Alex Hillman
>
> -----Original Message-----
> Sent: Thursday, February 01, 2001 2:12 PM
> To: Multiple recipients of list ORACLE-L
>
>
> To resolve it now:
> alter tablespace temp coalesce;
>
> for all the time set pctincrease to 1 (if 0) of temp tablespace
>
> alter tablespace temp default storage(pctincrease 1);
>
> Regards
> Rafiq
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Thu, 01 Feb 2001 08:07:48 -0800
>
> Hi.
>
> I have my daily routine to check segmetn whose next extent will not fit
in
> the single largest free extent in the tablespace.
> This morning I found the my temporary tablespace is on the list .
>
> We have 1624M assigned for the temp tablespace. As my understatd temp
> tablespace will extend itself as necessary and drop itself when operation
is
> done.
>
> I just wonder I should increase the size or wait to see because we just
> have our application updated.
>
>
> Mitchell
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mitchell
> INET: mitchell_at_comnet.ca
>
> 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).
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
> INET: rafiq9857_at_hotmail.com
>
> 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).
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
> INET: rafiq9857_at_hotmail.com
>
> 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: Mitchell
   INET: mitchell_at_comnet.ca

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).

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

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).