Oracle tablespace error with 8.1.7

[Ratzie]

I have a script that monitors the tablespaces in our database. If any tablespace reaches a threshold of 80% if emails me with a warning.
Unfortuantly, it over writes itself every 30 minutes when it kicks off again.

So I got an warning stating that the TEMP tablespace reach 80.1%. I can not tell if it was a spike or a creep.
(I have modified the script to keep history)
It is still at 80.1% and has not climbed after it reached that mark.

I can not explain it, I do not know where to look. I have received infor regarding a possiblity that was stated by Oracle, about a "bug"...
I just need to know if anyone else has come across this. I have not restarted the db since it is a production db. But this is the quote...

According to Oracle, the TEMP tablespace is mainly used by "select ..... sorted by or grouped by" - basically for sorting use. In order to save the tablespace allocate and de-allocate time for SMON process, the TEMP space will NOT be restored back to TEMP tablespace when the sorting is completed by one user, instead, it will be re-used by another user. In other word, this is normally to see the TEMP tablespace at a low level or 0% free. As long as there is no error message in alert.log indicating that the TEMP has a problem, there is nothing to be done on this. The reason why the TEMP is at 100% free after rebooting the database because all TEMP tablespaces are de-allocated at the reboot time.

DOES ANYONE KNOW WHERE I SHOULD BEGIN, OR WHAT TO LOOK AT?

[Indira Aramandla · Accepted solution]

Hi LHradowy,

Let me bigin with saying that there is nothing to worry at this stage.
With temporary tablespace, oracle will begin creating and using temp segments. When a process finishes using a temp segment, it doesn't deallocate it, it simply marks it free. Then, the next process that needs temp space can grab a free temp segment, and does not need to allocate one.

This can be monitored using V$SORT_SEGMENT, V$SORT_USAGE views. To see how much temp space is really being used at any given point in time, use V$SORT_USAGE.

It will be a problem only when you encounter the error messages in the alert log like â
ORA-1652: unable to extend temp segment by 256 in tablespace TEMPâ .

Here are few areas you can look into in regards to temp tablespace usage.

1. First determing if users are using the temp tablespace.
select user, tablespace, blocks
from v$sort_usage
order by blocks;

2. Next you want to free up the space used by prior storts.
alter tablespace temp
default storage(initial 2m);

This will reset your initial extent in your sort tablespace. Change initial to your initial sort segment size in the above example.

3. Third do the following.
alter tablespace temp coalease;

This will free up the space used by all of the prior sort. This should clear up your sort segment space allowing you use your sort tablespace again.

Monitored temp segments usage using V$SORT_SEGMENT, V$SORT_USAGE views. Attached is the script.


I hope this helps.


Indi

[Ratzie]

Thank you for the great information!
One question, I do not get anything backup when I try to run a select from the v$sort_usage.

I get data back when I select from v$sort_segment but not from v$sort_usage

[Indira Aramandla]

Hi,

View "V$SORT_USAGE" was introduced with Oracle 8 , the new dictionary view shows currently active sorts for the instance. You query v$sort_usage to find out who is using sort segments.

Joining "V$SORT_USAGE" to "V$SESSION" will provide the user who is performing a sort within the sort segment. The CONTENTS column shows whether the segment is created in a temporary or permanent tablespace.

When there are no active sorts for the instance you will not see any rows in v$sort_usage.

Indira A

[Yogeeraj_1]

hi

this is what we can find in the books...

Temporary tablespaces should appear "full" after a while in a normally running database. Extents are allocated once and then managed by the system. Rather when doing the rather expensive operation of "space management" (data dictionary updates), the system will allocate an extent in TEMP and then keep it and manage it itself.

This is an expected behaviour and not an indication that you do not have any temporary space.

You should see 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 is using what.

regards
yogeeraj

[Ratzie]

Thanks