Hi

Hope you can assist me in this. Few days ago when a Finance Integration process was being run the temp01.dbf datafile which was initally 6Gb in physical size rose to 16Gb. Is that something normal?

When I checked from dba_data_files , the autextensibe option is 'YES' and the next increment is 80.

Is this proper setting?
How can avoid this sort of increase in size in future?

Pls advice
Thank you and have a nice day

Yours in service
dhnish

Turn the AUTOEXTEND on the TEMP tablespace OFF because it will grow again. The TEMP tablespace is for sort segments and the size you have it (6Gb) is probably OK. In fact I would recommend that you not use AUTOEXTEND for any tablespace because it is too hard to manage.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hi

Thanks for your reply.
Regarding this , I included autoextend ON for all tablespace till a maxsize of 2GB. When this datafiles reaches to certain extent I then add new datafiles.

Since I'am alone here, managing all IT related stuff is bit hard.Thats why I hoped by setting autoextend ON I dont need to monitor it everyday.

In the case of TEMP tablespace(6Gb) can I resize it back from 16GB back to 6Gb?
Later, if the autoextend is OFF for this tablespace, what happens when a users query requires high sort space. Will there be a error? How to deal with it?
Pls advice

Thank you and have a nice day

Yours in service
dhnish

Hi Dhnish,

It is advisible to turn off autoextend for all tablespaces. About adding datafile to certain extent, it is subject of strategy, you need to re-rethink it, don't hurry, because the exception of adding datafile is resizing datafile, again it is policy matter. In future, lot of datafile may create performance / management problems to you.
Don't think, database not needed monitored everyday. Otherwise, someday, you will find database crash.
You can resize your temp datafile using :
ALTER DATABASE DATAFILE 'DRIVE\PATH\TEMP01.DBF' RESIZE 6291456K;

Before that, restart database, so that all the data will cleared from temp datafile. It may take some time, so plan it well.
Later if the space is not sufficient for executing query, there will be proper message. And you may need to kill the process or restart database. But I am sure, the query which uses 6 GB datafile, is incorrect query, so better, correct it.

Bye.

-Suhas

Hi

Dear Suhas,
The process I was talking about was a Finance Integration process. That particular table had 16 million records in it.

Lets say I resize it back to 6Gb and Autoextend is off. Later when there's a problem wouldn't it corrupt the data. Sometime back when the client PC had a problem some integration data was not captured so I'am afraid of this.

Later to increase the size do I use the same command but with a higher value?
Pls advice
Thank you and have a nice day

Yours in service
dhnish

Dear Dhnish,
Finance Intergration Process uses tfgld410/418 tables. And it is normal you have 16 million record in it. If it uses full 6GB Temp., you cannot think to make it again 6GB.
About corruption of Data, I can say, Temp. datafile is used for arranging data in sequential mannner i.e. sorting during SQL run. So, that perticular report(s) will hang, nothing else. Other operation like saving documents will not affect to this, because it is stored in Datafile/Index file. Pointed corruption case may have some other problem.
Same command, you can use for decreasing/increasing size of datafile.

Bye.

-Suhas