Occasionally, we see a series of ORA-1652 errors in the alert log during a short interval similar to the example below.
Wed May 27 08:12:41 2009 ORA-1652: unable to extend temp segment by 16 in tablespace TEMP Wed May 27 08:12:51 2009 ORA-1652: unable to extend temp segment by 16 in tablespace TEMP Wed May 27 08:13:37 2009 ORA-1652: unable to extend temp segment by 16 in tablespace TEMP Wed May 27 08:13:45 2009 ORA-1652: unable to extend temp segment by 16 in tablespace TEMP
An ORA-1652 generally indicates that the TEMP tablespace is full and cannot extend any further. As a result, the running operation using the TEMP tablespace (e.g. sort, join) will fail. We monitor ORA-1652 errors with a trigger, which is expected to capture useful information in a table such as the failed SQL statement, the user who executed the statement, and the time of failure. However, we have noticed that those details were not being stored following the repeated ORA-1652 errors, which is causing difficulty for us to identify the source of the problem. And yet, no complaints have been reported from the users, which is surprising because we expect the associated SQL statement to fail. Moreover, this has been observed in RAC environments only.
Temp Space Reallocation
In a RAC environment, the TEMP tablespace can be shared between instances on different nodes. In other words, an instance that is out of local temp space will start using another instance’s free temp space. Whenever an instance has no free temp space, Oracle uses the 1652 event to internally trigger temp space reallocation. This repeated process is transparent to the users, i.e. the client session does not see the error. Hence, the user request will continue to run and usually complete successfully if there is sufficient free temp space on other instances. Because of this feature, the ORA-1652 error being written to the alert log can be misleading. In fact, it is a known bug, which has been fixed in 10.2.0. Nevertheless, we can treat it as a warning that there is instance contention for temp space.
It is recommended that we perform the following procedure for handling the ORA-1652 errors in RAC environments. On receiving the ORA-1652 alert, immediately run the following query to see the total, used, and free temp space across all instances:
SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;
Scenario 1: If free_blocks is 0 (or approaching 0) for some instances, an instance is using other instances’ free temp space. Operations will be expected to complete successfully, however users may experience performance issues due to inter-node coordination. Keep monitoring the temp space on all instances. If we see the ORA-1652 errors keep appearing in the alert log for too long, we can still determine sessions with excessive temp space usage by using our shortcut scripts, and advise the user. It may eventually lead to the next scenario.
Scenario 2: If free_blocks is 0 (or approaching 0) for all instances, the temp space on all instances is full and cannot be extended. It is likely that the latest ORA-1652 error is real, hence the SQL statement would have failed completely and would have been captured in our table. The user would have received the error but he/she should be advised.
Metalink Doc 280578.1, 258941.1, 65973.1