Re: TEMP tablespace growth mystery!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 25 Jun 1998 21:32:41 GMT
Message-ID: <3592c09d.2547212@192.86.155.100>

A copy of this was sent to stuco_at_mailcity.com (if that email address didn't require changing) On Thu, 25 Jun 1998 19:20:42 GMT, you wrote:

>I was gathering statistics on my databases like I always do this time of the
>week and noticed that my TEMP tablespace that was 40MB is now 170MB!
>

sounds like someone turned autoextend on your temporary tablespace datafiles, either when they created it or by altering it at somepoint.

>Not knowing how and when this happened, I looked at my alert log to find out.
>But I found no record of an "ALTER TABLESPACE" transaction ever taking place.
>
>I did find that two days ago, there was a message that the TEMP tablespace
>could not extend iself by a certain number of bytes, but from that point on,
>everything in my alert log look fine.
>
>Did my TEMP tablespace allocate iself more table space?? If so, how come
>there is no record of it doing so?? And, where can I go to find out when
>this happened??
>

autoextends just happen, they are not recorded in the alert file.

In 7.3 you can use:

select a.file# "File#",

        a.name "File Name",
        initcap(status) || ' ' || initcap(enabled) "Status",
        round(bytes/1024/1024,1) "Curr",
        round(create_bytes/1024/1024,1) "Orig",
        round((bytes-create_bytes)/1024/1024,1) "Diff",
        round( (b.inc*to_number(c.value))/1024/1024, 1 ) "Inc",
        trunc( (b.maxextend*to_number(c.value))/1024/1024) "Max"

from v$datafile a, sys.filext$ b, v$parameter c where a.file# = b.file# (+)
and c.name= 'db_block_size'
/

to see if this is the case (look for Diff <> 0)

In 8.x look at the autoextensible column in the view dba_data_files.
>I am so in the dark about this!
>
>Thanks for your willingness to help! As someone once wrote, we are all in
>Oracle Hell!
>
>Stuart L. Cowen
>Paladin Consulting - Dallas
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 25 1998 - 16:32:41 CDT