the day before our temp tablespace was 50% full,but today it is 90% full and remained at that rate for 6 hours. i wonder why it isnt decreasing and remains at that value(90%). can i clear the temp tablespace?

Regards.

Hi Friend,

The reason of increasing temp tablespace abnormal is immoral query execution. Check Eventlogs, Trace files, baanalert.log and log.ora.sql to determine. You can clear it, by restarting database. Before down, take care no body should connect to database.

All the best.

-Suhas

cant i clear the TEMP tablespace dynamicially? if i clear the datafiles inside the TEMP tablespace,is oracle instance crashes?

Really, not understood.

-Suhas

i meant that, can i clear the contents of TEMP tablespace when the database is online, say that can i decrease the full percent of it to 0% manually?

Regards.

We are having a similar problem and I want to thank suhas-mahajan for giving me some ideas of where to look.

In our case we have isolated it to a particular client application. A reboot of the client and then issuing the command:

alter tablespace BAANTEMP default storage (pctincrease 0);

in a SQLPLus window clears the problem. A server reboot is not required.



Important Note: You may need to change the name of the temporary tablespace from BAANTEMP to match the name of you have given to your temporary tablespace.

as you know all Baan client processes derives from bshell6.1 and it produce automatically its matching oracle processes. so, i dont understand your words " In our case we have isolated it to a particular client application. A reboot of the client and then issuing the command:
alter tablespace BAANTEMP default storage (pctincrease 0);"

according to your statement can i issue the following command to clear the contents of our TEMP Tablespace ?

alter tablespace TEMP default storage (pctincrease 0);

Note:our temp tablespace is TEMP and the owner of tables in db is user BAAN.

In our case we have isolated it to a particular client application. A reboot of the client and then issuing the command:
alter tablespace TEMP default storage (pctincrease 0);

To be a little more specific: The client in question is not a BaaN client but third party data warehouse software performing data extraction and transformation directly against the Oracle database. I believe that the in-house developed Oracle view is the source of the problem but that may not be shared by the developer, a participant here on Baanboard (Hi John).

So in our case, the client is not running the BaaN client and there is no bshell involved. However, under the hood it is all just Oracle.

In your case, using SQLPlus, you should be able to issue the command:

alter tablespace TEMP default storage (pctincrease 0);

I would also recommend doing a search of the Oracle newsgroups on groups.google.com. I saw a couple of scripts for determining who is using temporary tablespace. This may help in tracking down your problem.

thanks all of you!

Originally posted by beksalur
the day before our temp tablespace was 50% full,but today it is 90% full and remained at that rate for 6 hours. i wonder why it isnt decreasing and remains at that value(90%).


Note that a full temp tablespace is normal functionality and does not constitute a problem. Temp segments are not dropped once they have been used, and will remain in place until the database is restarted.

A 100% full temp tablespace can be an indication that not enough temp space was available for a previous 'sort' - though may not imply a current problem.