10 g Temp Tablespace always shows full in OEM.

15 pts.
Tags:
Oracle 10g
Oracle Tablespaces
have a Oracle 10g database that is with its temporary tablespace full. Is there any way to clean this tablespace? I tried to restart the database, but i've no effect.Every day I increase the size of temp tablespace.Night we run some procedures and morning temp tablespace shows Full.
ASKED: January 18, 2008  2:15 PM
UPDATED: January 22, 2008  1:57 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

TEMP tablespace is usually used for Sort operations. If it’s full and u’ve already increase the size as a result but to no avail, chances are it’s the procedures that you’re running that’s probably requiring huge sort segments that is too big to fit into the Sort Memory, hence resulting in the subsequent sorting by using the TEMP tablespace instead.

Besides increasing the size of TEMP tablespace, u can always run this command to clean-up the tablespace and recover those empty spaces between segments that are occupying space:

SQL> Alter tablespace TEMP coalesce;

This does not resolve the root of the problem, however. Ultimately, you might want to determine if you’re indeed having too much sort operations and those sort operations are being performed out from the TEMP tablespace (resulting in high disk I/O) instead of within memory. The following SQL will prove useful :-

select a.value “No. of Memory Sorts”, b.value “No of Disk Sorts”, (b.value/a.value) * 100 “% of Disk-Mem Sorts”
from v$sysstat a, v$sysstat b
where a.name = ‘sorts (memory)’
and b.name = ‘sorts (disk)’;

In an OLTP environment, the percentage of the ratio of Disk Sorts to Memory Sorts should be < 5%.

As a rule of thumb, we generally try to ensure sort operations are confined within memory for optimum performance, however by doing so, it does have it’s trade-offs as it eats into system memory. Of course, this shouldn’t have an issue, if your server has lots of memory.

To increase the sort memory, you can increase this parameter SORT_AREA_SIZE to a higher value, however do take note that if this parameter is increased, you should jolly well increase the PGA_AGGGREGATE_TARGET parameter as well since the latter defines the PGA memory space that encompasses the Sort Memory space too. This is true if you’re using Dedicated Server connections to the Database whereby PGA memory will hold your User Session Information, Stack Space and Sort Memory space.

Best Rgds
BK

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DAC
    [...] &ampgt IT Answers &ampgt oracle &ampgt 10 g temp tablespace always shows full in OEM... Help ...http://itknowledgeexchange.techtarget.com/itanswers/10-g-temp-tablespace-always-shows-full-in-oem/Forum OpenACS Q&ampA: Oracle temp tablespace is huge!I just noticed that my oracle temp tablespace [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following