ORA-01652 : unable to extend temp segment by 128 in tablespace TEMP
From the application perspective, this error can occur in any part of the application, depending on the database operation required.
Please refer to RSA Knowledge Base Article 000030327 Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the log files for your specific deployment.
sqlplus "/as sysdba"
SELECT b.TABLESPACE, b.segfile# , b.segblk#, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) SIZE_in_MB, a.SID , a.serial# , a.username, a.osuser , a.program , a.status FROM v$session a , v$sort_usage b , v$process c , v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr ORDER BY b.TABLESPACE, b.segfile# , b.segblk# , b.blocks;
SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0) Total_in_MB , NVL(t.bytes, 0)/1024/1024 Used_in_MB FROM sys.dba_tablespaces d, (SELECT tablespace_name, SUM(bytes) bytes, COUNT(file_id) COUNT FROM dba_temp_files GROUP BY tablespace_name) a, (SELECT ss.tablespace_name , SUM((ss.used_blocks*ts.blocksize)) bytes FROM gv$sort_segment ss, sys.ts$ ts WHERE ss.tablespace_name = ts.name GROUP BY ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name LIKE 'TEMP%';
SELECT sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';
SELECT tablespace_name, total_blocks, used_blocks, free_blocks, (total_blocks*8)/1024 as total_MB, (used_blocks*8)/1024 as used_MB, (free_blocks*8)/1024 as free_MB FROM v$sort_segment;
col hash_value for a40; col tablespace for a10; col username for a15; set linesize 132 pagesize 1000; SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, s.sql_id, u.segtype, u.contents, u.blocks FROM v$session s, v$tempseg_usage u WHERE s.saddr=u.session_addr ORDER BY u.blocks DESC;
col hash_value for 999999999999 SELECT hash_value, sorts, sql_id, rows_processed/executions FROM v$sql WHERE hash_value in (SELECT hash_value FROM v$open_cursor WHERE sid = <sid from the session with the most blocks from the query above>) AND sorts > 0 AND PARSING_SCHEMA_NAME='AVUSER' ORDER BY rows_processed/executions;
SELECT * FROM table(dbms_xplan.display_awr(sql_id=>'<sql_id from one of the queries above>'));
sqlplus "/as sysdba"
ALTER TABLESPACE "TEMP" ADD TEMPFILE '+DG01' SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; COMMIT;
ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u01/app/oracle/oradata/AVDB/tempXX.dbf' SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; COMMIT;
$ ls /u01/app/oracle/oradata/AVDB/ | grep temp
This command may take two to five minutes to complete.
On non-appliances, please contact your DBA.
WARNING: This solution is intended to be used where the original TEMP space allocated was insufficient for the normal operation of the database. If extending the TEMP tablespace does not immediately resolve the issue do not continue to allocate more TEMP tablespace. Instead, attempt to understand why the problem query is too complex to complete with the default TEMP space. Be aware that if there is a query that is running indefinitely (not completing) it may require an infinite amount of TEMP space and you cannot configure TEMP tablespace as a solution.
In general, if a customer follows the TEMP tablespace recommendations in our Installation Guide they should not need to add additional TEMP tablespace.