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 [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>
> 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:[EMAIL PROTECTED]
>>> 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" <[EMAIL PROTECTED]>
>>> 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: [EMAIL PROTECTED]
>>>> 
>>>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>>> 
>>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>> 
>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Reply via email to