Hi everyone...
Okay - I've got a strange one for you. A friend asked me
Does anyone have any nifty tool or suggestion that helps provide visibility into how Oracle tempfiles really look from the Unix OS standpoint. I'm particularly concerned with the fact that, on Solaris at least, when using either the "df" or "du" utilities, what apparently shows up is the current "real" size of the tempfile, regardless of the number of bytes it was defined to occupy when it was created with a "create temporary tablespace ... tempfile ..." statement.
I, of course, said to myself... that's not true on AIX. But I have found some strange results. (all sizes are in MEG)
Code:
Size of TEMP
ALL Files ls -l du -m TEMP size
in Dir. temp01.dbf temp01.dbf filesize Used
---------- ---------- ---------- --------- ---------
SYS1 9,682.47 9,504.02 9,504.12 9,504.00 7,168.02
SYS2 176.96 10.01 10.01 10.00 2.31
SYS3 102.73 100.01 2.83 100.00 1.00
SYS1 is 8i, SYS 2 & 3 are 9i. All are on AIX 5.2
WHY?? would du return only "used" bytes? And why on only 1 of the systems. I'm at a loss.
Originally posted by Axr2 I don't know. I see the same phenomenon. I saw an article on sparse files on Metalink. Am not sure if it's relevant.
Do a "file" on the tempfile on SYS3. Does it show up as ascii text?
PS : Humor me - is your segment space management set to 'AUTO' on SYS3 and MANUAL on SYS1, SYS2?
Hi Axr2!
A "file" on the tempfile is ascii on all 3 systems. And Space Management is set to MANUAL (Uniform) on SYS2 & SYS3. SYS1 is still on 8i and is set to Dictionary Managed...yuk.
--------------------------------------------------------------------------------
Note:
On some operating systems, Oracle does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation.
--------------------------------------------------------------------------------
Note:
On some operating systems, Oracle does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation.
dbtoo - Well, at least I know it's a feature, not a bug. Still curious why the systems behave differently though.
Space Usage inconsistent for Temp (Unix)
Reply With Quote
Bookmarks