We appear to be hitting this bug on our production Exadata RAC system:
Bug 14383007 – Sort runs out of temp space in RAC even when temp space is available
One particular job errors out with ORA-01652 errors on the TEMP tablespace even though there is tons of free space in the sort segments. So, I got the idea of building a work around to have this job only login to the node that has the most free temp space. Normally space just gets reallocated from the node that has it to the node that needs it. But, I guess the bug is that in certain cases this doesn’t happen and you get the ORA-01652.
Here is my example unix script (actually this is run on an HP-UX box, don’t ask me why).
# This script demonstrates how to login to the instance # of a RAC database that has the most free space in # the TEMP tablespace. It takes three arguments # which are the connection information for the RAC # database: # 1 - Oracle database username # 2 - password # 3 - connect string # # in sqlplus this would be like connecting as username/password@connectstring # # Step one - login to the RAC database and extract a connect string that # connects to the node with the most free temp space. # This has several assumptions: # 1 - temporary tablespace is named TEMP # 2 - all of the temp space is allocated to sort segments # 3 - the port number is 1521 NEW_CONNECT_STRING=`sqlplus -s /nolog<<EOF connect $1/$2@$3 set heading off set feedback off set linesize 32000 set trimspool on select '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = '|| host_name|| ')(Port = 1521))) (CONNECT_DATA = (SID = '|| INSTANCE_NAME || ')))' from gv\\$instance where inst_id= (select inst_id from gv\\$sort_segment where TABLESPACE_NAME='TEMP' and FREE_BLOCKS = (select max(FREE_BLOCKS) from gv\\$sort_segment where TABLESPACE_NAME='TEMP')); exit EOF ` #trim the leading space from the connect string NEW_CONNECT_STRING=`echo $NEW_CONNECT_STRING|sed 's/^ //'` echo connect string = $1/$2@$NEW_CONNECT_STRING # Now connect to the node using this connect string just to show # that it works and query the sort segment view to show that this # really is the instance with the most free temp space. sqlplus /nolog<<EOF set echo on set termout on set pagesize 1000 connect $1/$2@$NEW_CONNECT_STRING -- show current instance select instance_name from v\$instance; -- list instances in descending order -- of free blocks. current instance -- should be listed first. select inst_id,free_blocks from gv\$sort_segment where TABLESPACE_NAME='TEMP' order by free_blocks desc; EOF
You wouldn’t want to use this except for a special case like this where you need a workaround for the bug. Otherwise you would just end up running on one node and all the temp space would get allocated to it. But, if you are hitting bug 14383007 this may be helpful.
– Bobby
