Re: When / why we resize/clean the temporary tablespace -- was RE:

Hemant

Why don't you get resolve the issue of "wonderful SQL query" user is still around in the database}."
which is occupying that much tempspace? Either the query is resulting in cartesian join or tables involved have degree > 1 resulting sort type 'hash'. Check your V$sort_usage when that code runs. It is not that much difficult to fix such codes.

Just fixing degree of tables/indexes may reduce the usage of tempspace. I have seen this behaviour when one of index was having degree > 1 and temp usage was passing 4GB and making degree to 1 the temp usage went down to less than 100M.

For checking degree
select table/index_name , degree from dba_tables/indexes where degree > 1;

HTH Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Sat, 15 Nov 2003 00:49:24 -0800

Not exactly correct. It is 1 single segment, with multiple extents. The extents
are reused. The segment is never dropped-and-recreated unless you

   (a) Stop and start the database instance [it is then created on the first request
for a temporary segment]

   (b) If it is NOT a TEMPORARY Tablespace but a Tablespace .. TEMPORARY, you issue an ALTER TABLESPACE ... DEFAULT STORAGE ... command

Now why should we worry about the Temporary Segment ? Of the 60 odd databases I have there are 2 databases [on in 8.1.7 where I am not using a TEMPORARY TABLESPACE but a TABLESPACE .. TEMPORARY, and therefore, I can use the ALTER TABLESPACE .. DEFAULT STORAGE ... command and the other in 9.2 where I am using a TEMPORARY TABLESPACE with TEMPFILEs] where at least once a month the Tablespace which is usually 2GB [and 400MB used] goes to 8GB
[or, as I found in a third 9.2 database this week 24GB], the file system is full
and users may start getting errors. Now, the Unix Administrator does have a CRON job
checking all the file systems of his 200+ servers, once-an-hour, I have a CRON job checking all
the tablespaces of the 60 databases every hour but it takes much less than an
hour for some user writing a "wonderful SQL query" to take Temporary Tablespace
out from 2GB to 8GB [or 24GB !].
It is in those cases, that I need to know how to bring my Temporary Tablespace usage
down.
{Of course, I do know that if that "wonderful SQL query" user has exited, then
he has released all the 8GB or 24GB of extents but my Unix administrator asks me why I am using so much space and what he should do about the alerts that the filesystem is 90+% full. If I keep the MAXSIZE of the files too low,
it is quite likely that more than one user gets errors trying to allocate extents while the
"wonderful SQL query" user is still around in the database}.

I also issue the ALTER TABLESPACE ... DEFAULT STORAGE .. command in the daily backup job to drop the Temporary segment when it is not in use. Yes, there is a cost associated with reallocating extents but this is only once a day.
If the NEXT is appropriate sized, few user sessions would be allocating more than
one or two new extents the first time round.

Hemant

At 10:09 AM 14-11-03 -0800, you wrote:
>Stephen.Lee_at_DTAG.Com scribbled on the wall in glitter crayon:
>
> > I got the impression that the poster was thinking segments allocated
> > in temp tablespace must be released, cleaned out, blown away, etc.
> > before something else can come along and use the space.
>
>i was always under the impression that you didn't need to do anything to
>the
>temp tablespace. that the segments were just reused as needed. am i
>behind
>the times here?
>
>--
>Bill "Shrek" Thater ORACLE DBA
>"I'm going to work my ticket if I can..." -- Gilwell song
> william.thater_at_carrier.utc.com
>------------------------------------------------------------------------
>Shift to the left! Shift to the right! Pop up, push down, byte, byte,
>byte!
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Thater, William
> INET: William.Thater_at_carrier.utc.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).

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: hkchital_at_singnet.com.sg

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

_________________________________________________________________
Great deals on high-speed Internet access as low as $26.95.  
https://broadband.msn.com (Prices may vary by service area.)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: M Rafiq
  INET: rafiq9857_at_hotmail.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).