Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tablespaces - datafiles

RE: Tablespaces - datafiles

From: Reardon, Bruce (CALBBAY) <>
Date: Wed, 12 Mar 2003 17:09:23 -0800
Message-ID: <>

I don't do this as I'm using Windows but how about this for a suggestion?

get the sum of max sizes from Oracle - via dba_temp_files get the sum of the current sizes from Unix get current free space from Unix of the mount point

Check that (Unix free space + Unix current sizes) - (sum Oracle max sizes) > some acceptable value

I don't know for sure its possible but it seems reasonable to me....

Bruce Reardon

-----Original Message-----

Sent: Thursday, 13 March 2003 5:24 AM

yup.. we haven't been bitten but we know the day is coming when its going to happen...

I'm still looking for a trick or rule of thumb to help avoid the problem of over allocating that mount point at 3:00AM.

So far what I have done.... only put tempfiles on the mount point not other datafile. Put a warning message file in the directory...but I still feel this is not enough logic protection against someone forgetting it.

Anybody got any other ideas of how to manage these tempfiles?


-----Original Message-----

Sent: Tuesday, March 11, 2003 3:38 PM

Use caution with tempfiles. They are wonderful, but when they create, they do not take up their specified size on the file system. For example, you create a temp tablespace with one temp file of 1000M on a 2000M filesystem. An ls -l will show the 1000M file size but a bdf will show that it is not really using that space yet. Just something to keep in mind so that no other files or temp files are put on that file system that will exceed the space that both of them need. What makes this really confusing is when a sort operation is trying to use that space and can't get it. You get errors that appear that you have run out of temp space, then look in dbastudio / oem, etc. and see that your 10 GB temporary tablespace is only 50% full. Once the sort tries to use that space in the file which is being inhibited by space, it can't get past that.

Sound like I've been bitten by this? (more than once)

<<< 3/11 2:30p >>> Jared,

Same behaviuor on HP-UX version 11 with Oracle Whatever size you define for tempfile , it is created with that size. Only observation that it is created much quicker than normal datafile of same size.


Date: Tue, 11 Mar 2003 11:42:54 -0800


Do you have that doc ref handy?

Using this SQL:

create temporary tablespace temp3 tempfile '/u01/oradata/dv03/temp3.dbf' size 500m
extent management local uniform size 1m

On both and on RH 7.2 I found that the file was immediately created full size.

Platform dependencies maybe?


"Mercadante, Thomas F" <> Sent by:
  03/11/2003 06:19 AM
  Please respond to ORACLE-L

         Subject: RE: Tablespaces - datafiles


I just struggled with this last week. You can't move Temporary Data files.
You need to drop and recreate the TEMP tablespace - creating the data files
in the correct directories.

On a side note - here is an interesting feature. When Oracle creates files
for the TEMP tablespace, it does not create the files full sized like it does for normal data files. It creates them smaller for speed purposes (it
creates the TEMP tablespace very fast) and will allow the TEMP data files to
grow as needed.

Now here is the kicker. Let's say you have a disk that is 9 gig is size. You can create 10-1 Gig Temp data files on that disk. Since Oracle does not
create the files full-sized, there is nothing to stop this from happening. Sometime later, as the TEMP tablespace gets used, the files grow until eventually the disk fills up, and a sql query crashes with an obscure disk io error. Oracle is trying to expand the TEMP datafiles to the size it's been told they should be. But there is no physical space left on disk.

Documentation in 817 does not mention this. But 92 doc's are up to date.

nice surprise, eh?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Tuesday, March 11, 2003 8:39 AM

All -

I am having a problem with the datafiles in a temporary tablespace. I need to move and rename three different datafiles in the tablespace. I am able to take them offline - no problem. I cna make the changes at the OS level. I am running on Unix. But I can't get the changes to show up in the OEM inorder to bring them back on-line.

Do I need to remove all users from this tablespace before making these changes? The tablespace is temporary so does that make a difference? Any suggestions?


Please see the official ORACLE-L FAQ:
Author: Reardon, Bruce (CALBBAY)

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message to: (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).
Received on Wed Mar 12 2003 - 19:09:23 CST

Original text of this message