Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Fan.

Asked: October 09, 2001 - 8:56 am UTC

Last updated: January 09, 2003 - 8:35 pm UTC

Version: 8i

Viewed 1000+ times

You Asked

Tom,

Would you please to explain:

'df -k' won't show the allocated datafile for temporary tablespace (locally managed).

Thanks,

and we said...

thats because we are using a quickie OS call that doesn't really allocate the file until you write to it. Its the way the OS handles it.

With "permanent files", we allocate and then write each block. With tempfiles we just allocate.

It is an operating system nuance, nothing we have control over.

For example (single user system, i'm the only one on)


ops$tkyte@ORA817DEV.US.ORACLE.COM> !df -k /tmp
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t1d0s0 385615 20585 326469 6% /tmp

ops$tkyte@ORA817DEV.US.ORACLE.COM> create temporary tablespace ttt tempfile '/tmp/foo.dbf' size 100m;
Tablespace created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> !df -k /tmp
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t1d0s0 385615 20585 326469 6% /tmp

no space increase yet...

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter user ops$tkyte temporary tablespace ttt;
User altered.


ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size=32767;
Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly;

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from all_objects order by object_name, object_id, created, last_ddl_time;

16907 rows selected.

Statistics
----------------------------------------------------------
7 recursive calls
20 db block gets
77510 consistent gets
254 physical reads
0 redo size
2038315 bytes sent via SQL*Net to client
125527 bytes received via SQL*Net from client
1129 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)

16907 rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> !df -k /tmp
Filesystem kbytes used avail capacity Mounted on
/dev/dsk/c0t1d0s0 385615 22609 324445 7% /tmp

no my space usage increased some, this was the tempfile space we actually used during my sort.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

more questions

Fan, October 09, 2001 - 10:08 am UTC

1) If the temp files have not been recognized by OS, can the 'false' space information introduce allocating other files into the same file system? For example, if /dbtemp has been allocated for temp datafile (taken 98%), but the usage (df -k) doesn't show that the space has been taken by Oracle. Is it possible that OS or Oracle allows the space to be used by other files?
2) It is another performance improvement for locally managed tablespace?

Options

Connor, October 09, 2001 - 1:12 pm UTC

You can get into a knot sometimes where other files use the space (that would have been there for the temp file). If you are concerned, typically copying the file somewhere and back (whilst the db isn't using it obviously) can fix the problem. Alternately (I haven't tried this), create it as a normal datafile, drop the tablespace, then create as a tempfile with reuse.

Tom Kyte
October 09, 2001 - 2:50 pm UTC

thanks for the followup Connor.

Is there some way to handle this online?

Bill Rachal, December 06, 2002 - 3:45 pm UTC

Thanks, as always, for the outstanding information, Tom. I like the suggestions that Connor has made, but is there some way to guarantee that the files get their allocated amount of space without shutting down the database? As I'm reading things, I should shutdown the database, copy the new tempfile twice, and then startup everytime I resize temp.

Tom Kyte
December 06, 2002 - 3:54 pm UTC

Or -- just allocate them on a volume with sufficient free space and let them allocate themselves as they see fit??? I just let them allocate it as they need it.

Solaris /tmp on filesystem?

Jeremy Smith, December 06, 2002 - 4:41 pm UTC

From your previous questions it looks like most of your examples you do on Solaris...is this one? (it looks like it because of the device names).

It's interesting that you (or your dba) uses an actual disk for your /tmp filesystem instead of the Solaris standard of /tmp as being a special filesystem that uses ram and/or swap space as necessary for storing data.

For example:
$df -k
Filesystem kbytes used avail capacity Mounted on
swap 870552 708408 162144 82% /tmp

Do you usually create your temporary tablespaces in /tmp? Is there a management/performance reason that you use a real filesystem /tmp instead of tmpfs? The one reason I've seen is that it prevents a file that goes nuts writing to /tmp from reducing the swap space you have to page out to. Are there other reasons? Do you back up your /tmp? Is it for clustering (create a temp tablespace that everyone can access)? If so, why not just use a different filesystem? The _nice_ thing about the way Solaris uses swap for /tmp is that if you do have oodles of ram, it doesn't ever bother to flush your writes to disk, which helps performance.

Is having /tmp on disk a practice you'd recommend for all Solaris/Oracle machines?

Tom Kyte
December 07, 2002 - 9:35 am UTC

solaris and linux.. but this was solaris and this WAS A TEST!!

I do not put Oracle files in /tmp, no -- i was demonstrating how sparse files work only.

It's called a sparse file

Angelo, December 06, 2002 - 5:16 pm UTC

Here's what I do:

Make sure that the temp tablespace in question is the default temp tablespace for the current user.

CREATE GLOBAL TEMPORARY TABLE t ( c1 char(2000) );

begin
while (1 = 1) loop
insert into t (c1) values (' ');
end loop;
end;
/

drop table t;

When you get the space error, the tablespace as been fully allocated.



Force physical allocation of space for tempfiles on unix

Jonathan Reinhart, January 09, 2003 - 8:35 pm UTC

In order to guarantee that space allocated for temporary
tablespaces in unix is actually allocated --
1. FIRST CREATE THE FILE ON THE OPERATION SYSTEM, then
2. ALLOCATE THE NEWLY CREATED FILE TO THE TEMP TBLSP, e.g.

$ dd if=/dev/zero of=/temp/oradata/prod/temp02.dbf
bs=1000k count=1000

create temporary tablespace temp2
tempfile '/temp/oradata/prod/temp02.dbf' REUSE
...

This way you can be sure that the file won't run out of space i.e. 'allocated space' will not be allocated somewhere else.
Test the procedure with du and ls.

Hope I helped..