temp.dbf - 3GB in size!

[Lisa Mauer]

Hi:
I am a Unix admin looking at an Oracle temp file grow and never give back space. Wondering if something is wrong or if this is normal... If normal - we may have a coding issue that is making this file so big.
The temp.dbf file on one of our devlopment databases has grown to 3GB. The data itself isn't even that big in size.... CAn this file be shrunk? Did something not complete? Not sure where to begin... DBA stated it looks like a sort needed temp space but is unsure if we can reclaim the space now... Anyone have more info?
Here is a look at the files in that directory:
/ora01
-rw-r--r-- 1 oracle orainst 97 Jul 25 2003 afiedt.buf
-rw-r----- 1 oracle orainst 2088960 Apr 26 10:01 control02.ctl
drwxr-xr-x 2 oracle orainst 96 Jul 23 2003 lost+found
-rw-r----- 1 oracle orainst 104858624 Apr 26 01:48 redo2a.rdo
-rw-r--r-- 1 oracle orainst 3307216896 Apr 22 09:40 temp.dbf
-rw-r----- 1 oracle orainst 94380032 Apr 26 01:48 users.dbf

Here is total size for db:
667666 /ora00/
3426323 /ora01/
411658 /ora02/
230497 /ora03/
317553 /ora04/
225377 /ora05/

Thanks!
Lisa

[Steven E. Protter]

This is a sign of possible problems in database administration.

To much data in the temp database instead of the data being assigned to proper tablespaces.

There is a procedure with oracle to after moving stuff out of the temp tablespace to reduce its space usage and consolidate extents.

Ours is much smallter, and its generally used during installations and by the application when it creates temporary tables and such. No permanent data is stored there.

Talk to Your DBA.

SEP

[RAC_1]

DBA's can't tell you that?? They need some oracle course.

fuser -u "temp.dbf" -- what processes are using it. What are these processes then.

Should these process run all the time?

If file being used is used as temporary file? why not trim it when processes no longer access it.

> tmp.dbf

Anil

[Lisa Mauer]

Thanks for the information... Here are the processes that it states are using the temp.dbf file...

fuser -u "temp.dbf"
temp.dbf: 15738o(oracle) 15732o(oracle) 15734o(oracle)

oracle 15738 1 0 01:48:42 ? 0:04 ora_smon_dbname


oracle 15732 1 4 01:48:41 ? 0:01 ora_dbw0_dbname


oracle 15734 1 4 01:48:41 ? 0:01 ora_lgwr_dbname

Maybe if I get enough knowledge on what I am looking at I can walk over and talk to them about it :) "dbname" was put in place opf the actual name :)

[Jeff_Traigle]

Well, looking at the processes holding the file wasn't really useful. Of course, the database processes are going to hold it when the database is open. :)

Don't just trim the file at the UNIX level, no matter what you do! You will render the database useless and will require recovery. Details Oracle expects about the datafiles and associated tablespaces are stored internally so modifications must be made from within Oracle.

Are you sure it wasn't created that large to begin with? The reason I ask is I thought datafiles had to be on contiguous blocks so it wouldn't be possible for Oracle to dynamically expand a datafile. (When more space is needed, a datafile can be added to a tablespace, but not increased in size... was the case with the ancient versions, 7.2.3.0.0 and 7.3.4.5.0, I worked with in my previous job at any rate.)

And you might want to have your DBA make sure the temporary tablespace is actually configured as temporary so permanent objects can't be stored there.

[Volker Borowski]

Hi Lisa,

Oracle-Datafiles can grow if the are set to autoextend. Usually, you should never allow a datafile to grow unlimited esp. if belonging to the system tablespace.

Frist check, if this file does show up in either V$DATAFILE or V$TEMPFILE. If it is in V$TEMPFILE, the tablespace which it belongs to should be of type temporary and should simply be re-creatable when the application is down.

If it is in V$DATAFILE, you need to check, if it contains permanent segments in addition to the temporary stuff. If not, it can be re-created as well, if yes, you need to reorganize the tablespace.

Hope this helps
Volker

[James A. Donovan]

The main reason the TEMP tablespace would grow would be due to Oracle needing to perform "disk sorts". Have your DBA's run several Statspack snapshots throughtout the day to isolate the offending code that is causing Oracle to do so much work. Oracle's Metalink website ( http://metalink.oracle.com ) has many good articles on tuning using Statspack and setting good values for the sort_area_size parameter. A google search should turn up just as many....

Generally, the SQL code can be tuned to minimize the impact it has on the TEMP tablespace.

[Brian Crabtree]

Lisa,

Can you do the following, and post it back?

su - oracle
sqlplus "/ as sysdba"
set lines 100 pages 1000
select * from v$version;
select tablespace_name,autoextensible,maxbytes from dba_temp_files;
(If this does not return any rows, run the following)
select tablespace_name,autoextensible,maxbytes from dba_data_files where tablespace_name = 'TEMP';

Thanks,

Brian

[twang]

Brian is right, first of all, you should tell us what is the version of oracle:
select * from v$version;
Then, take a look at the problem tempfile, whether they are autoextensible:
(Brain has provided SQL already)
You need to find out which process is allocating too much temporary segment.
You need to take a look at where processes which are currently sorting

The following query will allow you to determine who is performing sorts
(which is the major processing of temp segments):

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

[Indira Aramandla · Accepted solution]

Hi Lisa,

As others had mentioned you need to check the following.

Check if the temp tablespace (datafile) definition has the autoextensable and see if it is of type remporary or permanent. Then see of the segments defined in this TEMP tablespace are being used by the database.

If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released
until the instance is shutdown. If the TEMPORARY TABLESPACE is of type PERMANENT, then cleanup is performed by SMON (background process)after completion of the process using it.

If a PERMANENT tablespace is used for sorting, then any temporary segments
created are dropped by SMON on completion of the statement, and the space
is released for use by other objects.

The background process SMON wakes itself every 5 minutes and checks for tablespaces with default pctincrease != 0.
And the most common indicator is the SMON process consuming large amounts of CPU for a long period trying coalesc Free space in Temp tablespace.

Secondly you can ask your DBA to check to see if there are any other objects (like table, indexes) that were created by any users for testing in the temp tablespace and if they can be droped. This will release the space in TEMP tablespace.

Then you can ask your DBA to resize the TEMP tablespace as follows.

To decrease the size of a datafile,
ALTER DATABASE DATAFILE '//temp.dbf' RESIZE ?MB;

Downsizing a datafile is more complicated than increasing the size of a datafile. You cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. Check the view DBA_FREE_SPACE to see how much space is not being used in a datafile. For the above file we get:

SELECT * FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME=TEMP
ORDER BY BLOCK_ID;

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
------------------ ---------- ---------- ---------- ----------
TEMP 4 2 102400 50
TEMP 4 55 96256 47
TEMP 4 102 1890304 923

As you can see, there are two large extents at the high end of the datafile (BLOCK_ID = 55 and contains 47 blocks, BLOCK_ID=102 and contains 923 blocks). This means there are 1986560 unused bytes at the end of our datafile, almost 2MB. We want to leave some room for growth in our datafile, and depending on how the objects in that datafile allocate new extents, we could remove easily up to 1.89MB of disk space from the datafile without damaging any objects in the tablespace.


I hope this helps.
Indira A