Temporary Tablespace. ORA-1652: unable to extend temp segment by 128 in tablespace and more.

===============================================
General
===============================================

Oracle Temporary Tablespace issues.

===============================================
ORA-1652: unable to extend temp segment by 128 in tablespace
===============================================

Steps in investigation and resolution

Quick Fix for ORA-1652

1. Identify temporary datafile details :
COL FILE_NAME FOR A60
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)

SELECT  A.tablespace_name tablespace, 
        D.mb_total,
        SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used
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 would be like

TABLESPACE MB_TOTAL MB_USED
---------- -------- -------
TEMP           2548    2536

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

3.  Add more space to TEMPORARY tablespace

Option A) Resize temporary file
ALTER DATABASE TEMPFILE '/some/path/tofile/temp01.dbf' 
AUTOEXTEND ON MAXSIZE 6000M;

or
ALTER DATABASE TEMPFILE '/some/path/tofile/temp01.dbf' 
RESIZE 6000M;


Option B) Add temp datafile to temporary tablespace as
ALTER TABLESPACE TEMP 
ADD TEMPFILE '/u01/oradata/VIS11i/temp02.dbf'
SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;


===============================================
Example of moving Temporary datafile to another disk
===============================================

Current mount point is 100% full, and temporary datafile cannot be extended.

my_user@my_server:/oracle_db/db1>% df -hP

Filesystem                     Size  Used Avail Use% Mounted on

/dev/mapper/Volume00-LogVol14  7.9G  6.6G   1.3G  84% /software/oracle

/dev/mapper/Volume00-Backup     31M  4.5M    27M  15% /backup

/dev/vx/dsk/OraDg1/db1          80G   80G    55M 100% /oracle_db/db1

/dev/vx/dsk/OraDg2/Ora_Online  160G   81G    80G  51% /backup/ora_online

/dev/vx/dsk/OraDg2/Ora_Exp     100G   59G    42G  59% /backup/ora_exp

/dev/vx/dsk/OraDg3/db2         200G   2.5G  197G   2% /oracle_db/db2

/oracle_db/db1 mount point is 100% full.
Temporary tablespace cannot grow.
Need to move temporary datafile to another location.

See current status:

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_TEMP_FILES;

TABLESPACE_NAME       FILE_NAME
--------------------- ----------------------------------------------
TEMPORARY             /oracle_db/db1/db_igt/ora_temporary_01.dbf

Add new tempfile, on another disk:

ALTER TABLESPACE TEMPORARY 
ADD TEMPFILE '/oracle_db/db2/datafiles/ora_temporary_02.dbf' 
size 8192M AUTOEXTEND ON MAXSIZE 16384M;

Tablespace altered.


Check new status:
col FILE_NAME for A50

SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_TEMP_FILES;

TABLESPACE_NAME     FILE_NAME
------------------- -------------------------------------
TEMPORARY           /oracle_db/db1/db_igt/ora_temporary_01.dbf
TEMPORARY           /oracle_db/db2/datafiles/ora_temporary_02.dbf

SELECT FILE_NAME, STATUS FROM DBA_TEMP_FILES;

FILE_NAME                                          STATUS
-------------------------------------------------- ----------
/oracle_db/db1/db_igt/ora_temporary_01.dbf         AVAILABLE
/oracle_db/db2/datafiles/ora_temporary_02.dbf      AVAILABLE

SQL> !ls -l /oracle_db/db1/db_igt/ora_temporary_01.dbf
-rw-r----- 1 oracle dba 5976891392 Dec 27 06:18 /oracle_db/db1/db_igt/ora_temporary_01.dbf

SQL> !ls -l /oracle_db/db2/datafiles/ora_temporary_02.dbf
-rw-r----- 1 oracle dba 8589942784 Dec 27 07:28 /oracle_db/db2/datafiles/ora_temporary_02.dbf

Move the old tempfile offline:

ALTER DATABASE TEMPFILE 
'/oracle_db/db1/db_igt/ora_temporary_01.dbf' OFFLINE;

Database altered.

Note, that the DBA_TEMP_FILES does not show the actual online status of a file.

Need to check in V$TEMPFILE.

SELECT FILE_NAME, STATUS FROM DBA_TEMP_FILES;

FILE_NAME                                          STATUS

-------------------------------------------------- ----------

/oracle_db/db1/db_igt/ora_temporary_01.dbf         AVAILABLE

/oracle_db/db2/datafiles/ora_temporary_02.dbf      AVAILABLE

SELECT DBA_TEMP_FILES.file_name, V$TEMPFILE.status 

FROM DBA_TEMP_FILES, V$TEMPFILE

WHERE DBA_TEMP_FILES.file_id= V$TEMPFILE.file#;

FILE_NAME                                          STATUS

-------------------------------------------------- --------

/oracle_db/db1/db_igt/ora_temporary_01.dbf         OFFLINE

/oracle_db/db2/datafiles/ora_temporary_02.dbf      ONLINE

ALTER TABLESPACE TEMPORARY DROP TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf';

Tablespace altered.

SELECT DBA_TEMP_FILES.file_name, V$TEMPFILE.status

  FROM DBA_TEMP_FILES, 
       V$TEMPFILE

 WHERE DBA_TEMP_FILES.file_id = V$TEMPFILE.file#; 

FILE_NAME                                          STATUS

-------------------------------------------------- -------

/oracle_db/db2/datafiles/ora_temporary_02.dbf      ONLINE

SQL> !ls -l /oracle_db/db1/db_igt/ora_temporary_01.dbf

ls: /oracle_db/db1/db_igt/ora_temporary_01.dbf: No such file or directory

SQL> !df -hP

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/Volume00-LogVol00  2.0G  1.5G  459M  77% /

/dev/sda1             927M  288M  592M  33% /boot

tmpfs                 4.0G  1.4G  2.7G  33% /dev/shm

/dev/mapper/Volume00-LogVol05  992M   34M  949M   4% /home

/dev/mapper/Volume00-LogVol10  2.5G  946M  1.5G  39% /kits

/dev/mapper/Volume00-LogVol04  3.0G 1007M  1.9G  35% /opt

/dev/mapper/Volume00-LogVol13   31M  4.5M   27M  15% /oracle_db

/dev/mapper/Volume00-LogVol12  3.9G  1.4G  2.5G  36% /software

/dev/mapper/Volume00-LogVol14  7.9G  6.6G  1.3G  85% /software/oracle

/dev/mapper/Volume00-LogVol11   49G   21G   28G  43% /starhome

/dev/mapper/Volume00-LogVol03  2.0G  194M  1.8G  10% /tmp

/dev/mapper/Volume00-LogVol01  5.9G  4.3G  1.5G  75% /usr

/dev/mapper/Volume00-LogVol02  2.0G  449M  1.5G  23% /var

/dev/mapper/Volume00-Backup   31M  4.5M   27M  15% /backup

/dev/vx/dsk/SthDg2/ShVol2   15G  2.4G   12G  17% /starhome/igate/gbr-one-pmm/data

/dev/vx/dsk/OraDg1/db1   80G   75G  5.5G  94% /oracle_db/db1

/dev/vx/dsk/OraDg2/Ora_Online  160G   84G   77G  53% /backup/ora_online

/dev/vx/dsk/OraDg2/Ora_Exp  100G   56G   44G  57% /backup/ora_exp

/dev/vx/dsk/OraDg3/db2  200G  4.3G  195G   3% /oracle_db/db2

Issue is fixed.

===========================
Move Temporary File to new location
===========================

COL FILE_NAME FOR A60

COL STATUS FOR A10

SELECT TEMPFILE.FILE#, 
       DBA_FILE.file_name, 
       TEMPFILE.STATUS

  FROM V$TEMPFILE TEMPFILE, 
       DBA_TEMP_FILES 
       DBA_FILE

WHERE DBA_FILE.file_id = TEMPFILE.FILE#;

     FILE# FILE_NAME                                          STATUS

---------- -------------------------------------------------- ----------

         1 /oracle_db/db1/db_igt/data/ora_temporary_01.dbf    OFFLINE

         2 /oracle_db/db2/db_igt/data/ora_temporary_01.dbf    ONLINE

SQL> !cp -p /oracle_db/db1/db_igt/ora_temporary_01.dbf /oracle_db/db2/db_igt/data/ora_temporary_02.dbf

SQL> ALTER DATABASE RENAME FILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' TO '/oracle_db/db2/db_igt/data/ora_temporary_02.dbf';

COL file_name FOR A60

SELECT TEMPFILE.FILE#, 

       DBA_FILE.file_name file_name, 

       TEMPFILE.STATUS

FROM V$TEMPFILE TEMPFILE, DBA_TEMP_FILES DBA_FILE

WHERE DBA_FILE.file_id = TEMPFILE.FILE#;

     FILE# FILE_NAME                                          STATUS

---------- -------------------------------------------------- ----------

         1 /oracle_db/db2/db_igt/data/ora_temporary_02.dbf    OFFLINE

         2 /oracle_db/db2/db_igt/data/ora_temporary_01.dbf    ONLINE

cannot drop a datafile from used temp tablespace!!

SQL> ALTER TABLESPACE TEMPORARY DROP TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' ;

ALTER TABLESPACE TEMPORARY DROP TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf'

ERROR at line 1:

ORA-25152: TEMPFILE cannot be dropped at this time

===========================
Create a new TEMPORARY Tablespace, and drop the old one.
===========================
Datafile cannot be dropped from a tablespace!!

To drop The datafile, need to craete a new TEMPORARY Tablespace, and drop the old one using DROP TABLESPACE TEMPORARY INCLUDING CONTENTS AND DATAFILES syntax.

----------------------------------------------
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/oracle_db/db2/db_igt/data/ora_temporary_12.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 16000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE TEMPORARY INCLUDING CONTENTS AND DATAFILES;
----------------------------------------------
CREATE TEMPORARY TABLESPACE TEMPORARY TEMPFILE '/oracle_db/db2/db_igt/data/ora_temporary_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 16000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPORARY;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
----------------------------------------------

It might be that sessions are still use the old Temporary Tablespace, and it cannot be dropped.

SQL> DROP TABLESPACE TEMPORARY INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE TEMPORARY INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-60100: dropping temporary tablespace with tablespace ID number (tsn) 3 is blocked due to sort segments

In this case, find out the sessions that use old temporary tablespace, kill them, and then drop the old temporary tablespace.


CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 16000M;


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

SELECT * FROM DATABASE_PROPERTIES 
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME            PROPERTY_VALUE DESCRIPTION
------------------------ -------------- --------------------------
DEFAULT_TEMP_TABLESPACE  TEMP           Name of default temporary tablespace

SELECT TEMPFILE.FILE#, DBA_FILE.file_name, TEMPFILE.STATUS
  FROM V$TEMPFILE TEMPFILE, DBA_TEMP_FILES DBA_FILE
 WHERE DBA_FILE.file_id = TEMPFILE.FILE#;

FILE# FILE_NAME                                   STATUS
----- ------------------------------------------ -------
    1 /oracle_db/db1/db_igt/ora_temporary_01.dbf  ONLINE

    2 /oracle_db/db1/db_igt/ora_temporary_02.dbf  ONLINE

DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
--This step will take time, as there are sessions which still use old TEMP TBS

To see which sessions use old TEMP Tablespace:
SQL> SELECT username, tablespace FROM V$SORT_USAGE;

USERNAME                       TABLESPACE
------------------------------ ----------
FJI_VODAF_IPNQQ                TEMP2
LKA_DILOG_IPNQQ                TEMP2
KYG_NURTE_IPNQQ                TEMP2
NGA_GLOMO_IPNQQ                TEMP2
BEN_MOOVB_IPNQQ                TEMP2
NAM_TELCO_IPNQQ                TEMP2
GNQ_HITSE_IPNQQ                TEMP2
CIV_MTNCI_IPNQQ                TEMP2
LSO_ECONE_IPNQQ                TEMP2
NZL_SPARK_IPNQQ                TEMP2
CRC_ICEQQ_IPNQQ                TEMP2
COL_TIGOC_IPNQQ                TEMP2
BFA_TELEC_IPNQQ                TEMP2
BWA_BTCQQ_IPNQQ                TEMP2
TWN_STARQ_IPNQQ                TEMP2
GHA_GLOMO_IPNQQ                TEMP2
LBY_LIBYA_IPNQQ                TEMP2
BHS_NEWCO_IPNQQ                TEMP2
GHA_TIGOG_IPNQQ                TEMP2
CYP_MTNQQ_IPNQQ                TEMP2
NGA_9MOBI_IPNQQ                TEMP2
DRC_VODAF_IPNQQ                TEMP2

22 rows selected

col username for A30

col program  for A30

col kill_cmd for A30

SELECT username, 

       program,

       'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' ;' as kill_cmd

FROM V$SESSION 
WHERE saddr IN (SELECT session_addr FROM V$SORT_USAGE);

ALTER SYSTEM KILL SESSION '67,7' ;
ALTER SYSTEM KILL SESSION '74,21' ;
ALTER SYSTEM KILL SESSION '2576,45' ;
ALTER SYSTEM KILL SESSION '3800,91' ;
ALTER SYSTEM KILL SESSION '3839,77' ;
ALTER SYSTEM KILL SESSION '3841,17' ;
ALTER SYSTEM KILL SESSION '3843,175' ;
ALTER SYSTEM KILL SESSION '5099,91' ;
ALTER SYSTEM KILL SESSION '6339,27' ;
ALTER SYSTEM KILL SESSION '7580,9' ;
ALTER SYSTEM KILL SESSION '7621,119' ;
ALTER SYSTEM KILL SESSION '7704,1395' ;
ALTER SYSTEM KILL SESSION '8816,79' ;
ALTER SYSTEM KILL SESSION '8821,91' ;
ALTER SYSTEM KILL SESSION '8853,83' ;
ALTER SYSTEM KILL SESSION '10086,13' ;
ALTER SYSTEM KILL SESSION '10104,117' ;
ALTER SYSTEM KILL SESSION '10105,3' ;
ALTER SYSTEM KILL SESSION '12534,101' ;
ALTER SYSTEM KILL SESSION '12574,127' ;
ALTER SYSTEM KILL SESSION '12605,63' ;
ALTER SYSTEM KILL SESSION '12712,659' ;

Execute the kill session statements.
Now the temporary tablespace can be dropped.

SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


SELECT TEMPFILE.FILE#, DBA_FILE.file_name, TEMPFILE.STATUS
  FROM V$TEMPFILE TEMPFILE, DBA_TEMP_FILES DBA_FILE
 WHERE DBA_FILE.file_id = TEMPFILE.FILE#;

FILE# FILE_NAME                                   STATUS
----- ------------------------------------------ -------
    1 /oracle_db/db1/db_igt/ora_temporary_01.dbf  ONLINE
 
 

===========================
Free Space Analysis in TEMPORARY Tablespace
===========================

SELECT * FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------

TEMPORARY                           3.4360E+10      3.4360E+10 3.4357E+10

SELECT create_bytes/1024/1024 AS CREATE_MB, 
       bytes/1024/1024 AS CURRENT_MB, 
       name 
  FROM V$TEMPFILE;

 CREATE_MB CURRENT_MB NAME
---------- ---------- ------------------------------------------
       600      32768 /oracle_db/db1/db_igt/ora_temporary_01.dbf

Current usage of TEMP Tablespace

1.  Identify temp segment usages per session

Temp segment usage per session.

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;

– Temp segment usage per statement.
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.

===========================
Corrupt temporary datafile
===========================

A temporary datafile is corrupt, is in unreadable.

The solution would be to add a new datafile, bring the old one to offline, and them drop the old datafile

ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_02.dbf'
SIZE 1024M AUTOEXTEND ON MAXSIZE 10240M;

ALTER DATABASE TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf' OFFLINE;

ALTER TABLESPACE TEMPORARY DROP TEMPFILE '/oracle_db/db1/db_igt/ora_temporary_01.dbf';

===========================
Allocate more space to TEMP Tablespace
===========================

SQL> ALTER DATABASE TEMPFILE '/oracle_db/db2/db_igt/datafiles/ora_temporary_12.dbf' AUTOEXTEND ON MAXSIZE 20000M;

Database altered.

SQL> SELECT TABLESPACE_NAME, 

            FILE_NAME, 

            ROUND(BYTES/1024/1024) AS used_mb, 

            ROUND(MAXBYTES/1024/1024) AS max_mb

     FROM DBA_TEMP_FILES;

TABLESPACE_NAME FILE_NAME                                            USED_MB MAX_MB

------- ----------------------------------------------------------------------- ----------

TEMP2           /oracle_db/db2/db_igt/datafiles/ora_temporary_12.dbf  9341    20000

===========================
SQL LOADER and TEMPORARY Tablespace
===========================
During SQL Loaded, there is increase in "physical writes direct temporary tablespace" .
This is due to loads into tables with Index / PK.
One must allocate enough free blocks in TEMPORARY tablespace 

===========================
Root Cause Analysis - History
===========================
ASH Tables by Example I - Why did TEMPORARY Tablespace grew in size?

Reference
Temporary Tablespaces

===========================
Tags
===========================
ORA-1652, ORA-01652