Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

temp.dbf - 3GB in size!

SOLVED
Go to solution
Lisa Mauer
Regular Advisor

temp.dbf - 3GB in size!

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
11 REPLIES
Steven E. Protter
Exalted Contributor

Re: temp.dbf - 3GB in size!

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
RAC_1
Honored Contributor

Re: temp.dbf - 3GB in size!

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
There is no substitute to HARDWORK
Lisa Mauer
Regular Advisor

Re: temp.dbf - 3GB in size!

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
Honored Contributor

Re: temp.dbf - 3GB in size!

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.
--
Jeff Traigle
Volker Borowski
Honored Contributor

Re: temp.dbf - 3GB in size!

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
Honored Contributor

Re: temp.dbf - 3GB in size!

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.

Remember, wherever you go, there you are...
Brian Crabtree
Honored Contributor

Re: temp.dbf - 3GB in size!

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
Honored Contributor

Re: temp.dbf - 3GB in size!

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
Honored Contributor
Solution

Re: temp.dbf - 3GB in size!

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







Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: temp.dbf - 3GB in size!

hi lisa,

Please allow me to add the following:

The dynamic performance views V$SORT_USAGE and V$SORT_SEGMENT for more information regarding the usage of space within these temporary segments. V$SORT_USAGE will tell you who's using what.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Lisa Mauer
Regular Advisor

Re: temp.dbf - 3GB in size!

Thank you so much for all the information. The DBA has since trimmed the file through Oracle and I created a job to monitor that file, the next time it increases we will be able to research what is actually creating the space (using the temp). We had enough disk space allocated that we didn't notice the increase until the processing had already ended and there wasn't anything left to tell us what actually had done the processing. Here is the information you requested and we'll be watching for it to grow again....
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production
CORE 9.2.0.2.0 Production
TNS for HPUX: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production


TABLESPACE_NAME AUT MAXBYTES
------------------------------ --- ----------
TEMP NO 0


dbname > select tablespace_name,autoextensible,maxbytes from dba_data_files where tablespace_name = 'TEMP';

no rows selected

On HP 11i rp8400.
Thanks again and will post more when this file grows. It is set at a minimum of 200MB to grow in 10MB chucks with a max size of 5 GB, autoextend reuse. Seems as though our clients aren't experiencing this so we are assumming at this point it was run away development code.