{"id":1275,"date":"2009-07-27T07:32:13","date_gmt":"2009-07-27T11:32:13","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2009\/07\/27\/ora-1652-unable-to-extend-temp-segment-by-128-in-tablespace-temp\/"},"modified":"2017-03-24T09:42:02","modified_gmt":"2017-03-24T13:42:02","slug":"ora-1652-unable-to-extend-temp-segment-by-128-in-tablespace-temp","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2009\/07\/27\/ora-1652-unable-to-extend-temp-segment-by-128-in-tablespace-temp\/","title":{"rendered":"ORA-1652: unable to extend temp segment by 128 in tablespace TEMP"},"content":{"rendered":"

This post covers quick fix for temp tablespace issue<\/strong> (ORA-1652<\/strong>) which is to add temporary datafile<\/strong> and then find root cause<\/strong> of temp tablespace issue.<\/p>\n

Extent<\/strong> – An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks<\/strong>. One or more extents make up segment.<\/p>\n

Segment<\/strong> – 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<\/a><\/p>\n

Temporary Tablespace<\/strong> – contains transient data that persists only for the duration of the session. To know more\u00a0 click here<\/a><\/p>\n

.
\nQuick Fix for ORA-1652<\/strong><\/u><\/p>\n

1.<\/strong> Identify temporary datafile details :
\nSQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;<\/span>
\n—–
\n\/u01\/oradata\/VIS11i\/temp01.dbf\u00a0\u00a0 TEMP<\/p>\n

2.<\/strong> Check if there is any space available in temporary tablespace (segment)<\/p>\n

SQL> SELECT\u00a0\u00a0 A.tablespace_name tablespace, D.mb_total,
\nSUM (A.used_blocks * D.block_size) \/ 1024 \/ 1024 mb_used,
\nD.mb_total – SUM (A.used_blocks * D.block_size) \/ 1024 \/ 1024 mb_free
\nFROM\u00a0\u00a0\u00a0\u00a0 v$sort_segment A,
\n(
\nSELECT\u00a0\u00a0 B.name, C.block_size, SUM (C.bytes) \/ 1024 \/ 1024 mb_total
\nFROM\u00a0\u00a0\u00a0\u00a0 v$tablespace B, v$tempfile C
\nWHERE\u00a0\u00a0\u00a0 B.ts#= C.ts#
\nGROUP BY B.name, C.block_size
\n) D
\nWHERE\u00a0\u00a0\u00a0 A.tablespace_name = D.name
\nGROUP by A.tablespace_name, D.mb_total;<\/span>
\n<\/strong>
\n—-
\noutput like<\/p>\n

TABLESPACE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MB_TOTAL\u00a0\u00a0\u00a0 MB_USED\u00a0\u00a0\u00a0 MB_FREE
\n——————————- ———- ———- ———-
\nTEMP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2548\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2536\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12<\/p>\n

(in above case out of 2 GB only 12 MB is free)<\/p>\n

.<\/p>\n

3.<\/strong>\u00a0 Temporary fix<\/strong><\/u><\/p>\n

a) Resize temporary file as <\/strong>
\nSQL> ALTER DATABASE TEMPFILE ‘\/u01\/oradata\/VIS11i\/temp01.dbf’ RESIZE 3072M;<\/span><\/p>\n

or<\/strong><\/p>\n

b) Add temp datafile to temporary tablespace as<\/strong><\/p>\n

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘\/u01\/oradata\/VIS11i\/temp02.dbf’
\nSIZE 1024M REUSE AUTOEXTEND ON NEXT 50M\u00a0 MAXSIZE 1024M;<\/span><\/p>\n

.<\/p>\n

Root Cause Analysis<\/strong><\/u><\/p>\n

1.\u00a0<\/strong> Identify temp segment usages per session<\/strong><\/p>\n

—
\n— Temp segment usage per session.
\n—
\nSQL> SELECT\u00a0\u00a0 S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
\nP.program, SUM (T.blocks) * TBS.block_size \/ 1024 \/ 1024 mb_used, T.tablespace,
\nCOUNT(*) statements
\nFROM\u00a0\u00a0\u00a0\u00a0 v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
\nWHERE\u00a0\u00a0\u00a0 T.session_addr = S.saddr
\nAND\u00a0\u00a0\u00a0\u00a0\u00a0 S.paddr = P.addr
\nAND\u00a0\u00a0\u00a0\u00a0\u00a0 T.tablespace = TBS.tablespace_name
\nGROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
\nP.program, TBS.block_size, T.tablespace
\nORDER BY mb_used;<\/span><\/p>\n

2.<\/strong> Identify temp segment usages per statement<\/strong>
\n—
\n— Temp segment usage per statement.
\n—
\nSQL> SELECT\u00a0 S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
\nT.blocks * TBS.block_size \/ 1024 \/ 1024 mb_used, T.tablespace
\nFROM\u00a0\u00a0\u00a0 v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
\nWHERE\u00a0\u00a0 T.session_addr = S.saddr
\nAND\u00a0\u00a0\u00a0\u00a0 T.sqladdr = Q.address
\nAND\u00a0\u00a0\u00a0\u00a0 T.tablespace = TBS.tablespace_name
\nORDER BY mb_used;<\/span>
\nDepending on outcome of temp segment usage per session and per statement focus on problematic session\/statement.<\/p>\n

.
\nReferences\/Related<\/strong><\/u><\/p>\n