Re: TEMP Tablespace problem

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Wed, 01 Oct 2003 08:24:28 -0800
Message-ID: <F001.005D1B21.20031001082428@fatcity.com>

I don't think that you can reduce temp datafile much below bytes_used value in v$temp_space_header. I could reduce the file few kilobytes, but not much compared to it's size. In order to reduce bytes_used, you need to bounce instance (if there isn't any nifty tricks for releasing temp segment otherwise).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, October 01, 2003 6:34 PM

> Yes you can.
> On Wed, 2003-10-01 at 11:29, Teresita Castro wrote:
> > I have Oracle 9.2i
> > I already run the query and the tablespace TEMP have
> > CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL
> > Can I run this line to fix the size of my tablespace?
> >
> > SQL> alter database tempfile '/data/oradata/system/temp01.dbf' resize
> > 128M;
> >
> > I mean is the same or I have to change something?
> >
> > >>> mgogala_at_adelphia.net 09/30/03 10:04PM >>>
> > Teresita, spelling the word "administrator" with the capital letter
> > is a good thing. Furthermore, if you have version 9i, you can do the
> > following, little known, thing:
> >
> > SQL> alter database tempfile '/data/oradata/system/temp01.dbf' resize
> > 128M;
> >
> > Database altered.
> >
> >
> > If it's Oracle 8i, you can actually have a permanent, dictionary
> > based
> > tablespace acting as a temporary tablespace. In version 9, however,
> > you
> > can't. Here is the query to find out what do you have:
> > 1 select tablespace_name,contents,extent_management
> > 2 from dba_tablespaces
> > 3* order by contents
> > SQL> /
> >
> > TABLESPACE_NAME CONTENTS EXTENT_MAN
> > ------------------------------ --------- ----------
> > SYSTEM PERMANENT LOCAL
> > DRSYS PERMANENT LOCAL
> > EXAMPLE PERMANENT LOCAL
> > INDX PERMANENT LOCAL
> > USERS PERMANENT LOCAL
> > XDB PERMANENT LOCAL
> > TOOLS PERMANENT LOCAL
> > TEMP TEMPORARY LOCAL
> > UNDOTBS1 UNDO LOCAL
> >
> > 9 rows selected.
> >
> > SQL>
> >
> > If the column contents reads "TEMPORARY" for the given tablespace, you
> > can
> > drop it and recreate it without any harm. Note that in 8i you don't
> > have
> > "UNDO" tablespaces and your system tablespace cannot be LMT.
> >
> >
> > On 2003.09.30 22:24, Teresita Castro wrote:
> > > Hi!!
> > > I am trying to change the size of my tablespace TEMP, I am not an
> > > Administrator but we really need to make this tablespace smaller.
> > > Already the size is 13214 Mgs, and this tablaspace is on a disk that
> > is
> > > full,
> > > so if we can not make it smaller we are going to be in a serius
> > trouble
> > > ( our
> > > Administrator is not here until Monday).
> > >
> >
> > --
> > Mladen Gogala
> > Oracle DBA
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Mladen Gogala
> > INET: mgogala_at_adelphia.net
> >
> > 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: 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).
> >

>
>
>
>

> Note:
> This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
> Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

>

> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.com

>


> 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: 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.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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

Received on Wed Oct 01 2003 - 11:24:28 CDT