If you want to get the same effect entirely from the "SQL>" prompt (i.e.
without having to issue operating-system copy commands), you can first
create a tablespace (named DUMMY_TEMP?) with datafiles of the name and size
you want for your TEMP tablespace. Then drop the DUMMY_TEMP tablespace and
create the TEMP tablespace over the same files, now as tempfiles. Donšt
forget the REUSE clause...
on 9/18/03 11:34 AM, Tanel Poder at tanel.poder.003_at_mail.ee wrote:
> Hm, it's strange.
> When you create a temp datafile there is some data written to it despite you
> actually store anything there or not (headers or whatever control structs).
> So, the tempfile is being used from beginning. The mechanism of creating
> sparse files is quite simple: just forward seek command is issued on the
> file, over the current end of file and then one byte (maybe block in Oracle)
> is written at the end position. That way, if OS & filesystem support sparse
> files, they don't actually allocate space for the empty part. Space is
> allocated only when something is written to these parts (when reading empty
> space in sparse file, nulls are retrieved).
>
> To avoid any sparse file issues in future, I recommend you to copy the
> tempfiles to another name using cp, then rename back, that way the file
> isn't sparse anymore (well, until the tempfile autoextends, then file gets
> sparse again). And compare file sizes periodically with ls -l and ls -ls.
>
> Tanel.
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, September 18, 2003 8:04 PM
>
>
>>
>> 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).
>>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
INET: tim_at_sagelogix.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).
Received on Sat Sep 20 2003 - 00:29:43 CDT
