How To Resolve TNS-12535 or TNS-00505 Operation Timed Out Errors

This post covers quick fix for temp tablespace issue (ORA-1652) which is to add temporary datafile and then find root cause of temp tablespace issue.

Extent – An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents make up segment.

Segment – A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. To know more click here

Temporary Tablespace – contains transient data that persists only for the duration of the session. To know more  click here

.
Quick Fix for ORA-1652

1. Identify temporary datafile details :
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;
—–
/u01/oradata/VIS11i/temp01.dbf   TEMP

2. Check if there is any space available in temporary tablespace (segment)

SQL> SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

—-
output like

TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
——————————- ———- ———- ———-
TEMP                                  2548         2536       12

(in above case out of 2 GB only 12 MB is free)

.

3.  Temporary fix

a) Resize temporary file as 
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;

or

b) Add temp datafile to temporary tablespace as

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/VIS11i/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 1024M;

.

Root Cause Analysis

1.  Identify temp segment usages per session

–
– Temp segment usage per session.
–
SQL> SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

2. Identify temp segment usages per statement
–
– Temp segment usage per statement.
–
SQL> SELECT  S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
Depending on outcome of temp segment usage per session and per statement focus on problematic session/statement.

.
References/Related

• 793380.1  ORA-1652 Error Troubleshooting
• 258941.1  RAC databases and ORA-1652: Unable To Extend Temp Segment By %s In Tablespace TEMP

Question:  I have been getting the ORA-1652 errors, and I have no more disk to allocate to my TEMP tablespace:

I have waited for SMON to clean out the un-used TEMP segments, and re-starting the instance did not stop the ORA-1652 errors.  It's an emergency.  How do I remove the temp segments?

Answer:  Normally, you would just add disk to TEMP to avoid the ORA-1652 error, but you can also wait for SMON to clean-up the TEMP segment.

You can check for held TEMP segments with this query:

select 
   srt.tablespace, 
   srt.segfile#, 
   srt.segblk#, 
   srt.blocks, 
   a.sid, 
   a.serial#, 
   a.username, 
   a.osuser, 
   a.status 
from 
   v$session    a,
   v$sort_usage srt 
where 
   a.saddr = srt.session_addr 
order by 
   srt.tablespace, srt.segfile#, srt.segblk#, 
   srt.blocks;

MOSC has a very detailed and informative article concerning ORA-01652 and RAC.  There is some troubleshooting required with ORA-01652 in RAC because there are two common causes in this area. 

First ORA-01652 may occur because there is simply no space available in the temp tablespace of which is being used.  The second cause of ORA-01652 may have to do with the local temp segment not being able to extent space even though there is space in other instances. 

To trouble shoot for ORA-01652,  and find out which of the above scenarios are causing ORA-01652 use this query offered by MOSC:

select sum(free_blocks)
from gv$sort_segment
where tablespace_name = ''

You will know that the first scenario is causing ORA-01652 to be thrown if the free block reads '0' because it signifies that there is no free space.

If there is a good amount of space, you know that there is another cause for ORA-01652, and it is probably the second scenario.  It is important to note that in a non-RAC environment, local instances are not able to extend the temp segments, so in the RAC environment, ORA-01652 has to be handled differently.  If you are experiencing ORA-01652 in a non-RA environment, be aware that every SQL making use of the tablespace can fail. 

In RAC, more sort segment space can be used from other instances, which can help resolve ORA-01652 more easily.  Try using the query below:

select inst_id, tablespace_name, total_blocks, used_blocks,free_blocks
from gv$sort_segment;

Basically, you can then find out how much temp segment space can be used for each instance by viewing the total_blocks, and the used_blocks can reveal the space which has been used so far, and the free_blocks gives the amount of space allocated to this particular instance.    This being, to resolve ORA-01652, you can check out that used_blocks = total_blocks and free_blocks = 0 will probably show for the instance, and ORA-01652 will be shown multiple times within the alert log.

This basically means that free space from other instances is being requested, and typically signifies that there is instance contention.  Instance contention within the temporary space can make the instance take more time to process.

In sever cases, a slowdown may occur, in which you might want try one of the following work-arounds:

1. Increase size of the temp tablespace
2. Increase sort_area_size and/or pga_aggregate_target

However, remember to not use the RAC feature of DEFAULT temp space.

If ORA-01652 is causing the slowdown, SMON will probably not be able to process the sort segment requests, you should try to diagnose the contention:

• Output from the following query periodically during the problem:
  select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
  from gv$sort_segment;
• Global hanganalyze and systemstate dumps

Here is another example of ORA-01652 from an article regarding ORA-12801.

Question:

I understand ORA-01652 is usually caused by running out of space When I run Oracle parallel query, I keep receiving ORA-01652, why?

Answer:

In this case, there was a sort in the parallel query which continues to cause ORA-01652 to be thrown.  Remember, the parallel query coordinator has receives the returned results from the parallel processes as a last step of the OPQ sort.  This being, you should be able to resolve ORA-01652 by increasing TEMP, and perhaps also the sort_area_size.  For advice on this, refer to the statement below:

 If this job is running batch, you can do this with an alter session command, as this this case, to one gig:

alter session set sort_area_size = 1,048,576,000