RE: DBMS_STATS and CBO

At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since it remained unused, it didn't grab any significant disk space. It's possible, that at some time, the free space in the file system fell below 5 Gig. When I tried to create the index, the error occurred immediately. So, the file never tried to grow at all ... ever. The big question is why not? The OS recorded no I/O errors at the time, and we have had no I/O errors on any of the other stuff using the file system.

What I am wondering is if there is some IMPLIED disk address assignment that occurs when the tempfile (a "sparse" file, I assume) is created, and can this assignment be overwritten or screwed up in some way if the tempfile remains unused so that when the tempfile finally wants its space, the filesystem has changed from the time the file was created, and now tempfile can't grab anything. Or do we have some other kind of weirdness going on here?

Is this making sense?

> -----Original Message-----
> From: Tanel Poder [mailto:tanel.poder.003_at_mail.ee]
> Sent: Thursday, September 18, 2003 11:35 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: DBMS_STATS and CBO
>
>
> Btw, how much free space do you have in OS where your tempfiles are?
>
> Tanel.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, September 18, 2003 6:49 PM
>
>
> > (Resending)
> >
> > Any comments on the following??
> >
> > When creating index, got
> > ORA-00603: ORACLE server session terminated by fatal error
> >
> > apparently caused by
> >
> > ksedmp: internal or fatal error
> > ORA-01114: IO error writing block to file 121 (block # 149)
> > ORA-27063: skgfospo: number of bytes read/written is incorrect
> > Additional information: 16384
> > Additional information: 49152
> >
> > which I determined was caused by attempted write to temp
> tablespace using
> a
> > tempfile. The tablespace was dropped and recreated, and
> all was well
> again.
> >
> > What I think MIGHT have happened is the tablespace created
> weeks ago, but
> > not used. So it didn't grab any actual storage. In the
> mean time, some
> of
> > the storage might have been used by something else, but storage was
> > released. Now tempfile goes to grab some space, but
> filesystem is all
> > screwed up about what storage the tempfile should be grabbing.
> >
> > Does this sound plausible?
> > Is there something else going on here?
> > Is this another one of those spiffy cool things in Oracle
> that are just
> > something else to go wrong? There seems to be no way of
> creating a LMT
> > tempfile so that it pre-grabs the disk space.
> >
> > Note that the index create blew up immediately, so the
> original tempfile
> > never grabbed any space. So, I think I can say that is
> definitely didn't
> > run out of space; but maybe somebody walked across the
> space the tempfile
> > thought it was going to get in the future.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Stephen Lee
> > INET: Stephen.Lee_at_DTAG.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).
>

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