# Bug 14383007 workaround

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
# 3 - connect string
#
#
# 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