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

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