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