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).
