Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS and CBO

RE: DBMS_STATS and CBO

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 18 Sep 2003 09:54:47 -0800
Message-ID: <F001.005D064D.20030918095447@fatcity.com>


Rafiq,

SunOS 5.8

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Thursday, September 18, 2003 1:40 PM To: Multiple recipients of list ORACLE-L

Tom,

Your observation on which platform? On HPUX 11.0 I think it allocates full given physical size of tempfile at the time of creation and it was 8.1.6.2 when I created it 2 years back. I remember that I created 6 files of 501 MB each and it occupied disk space of 3GB+.

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Thu, 18 Sep 2003 08:19:40 -0800

Stephen,

I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files.

The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got.

Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did.

So, yeah, I think you understand what's happening.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L

(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: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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). _________________________________________________________________ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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 Thu Sep 18 2003 - 12:54:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US