ORA-1652 Temp Tablespace

If you face these Error ORA-1652

Note & Important :
                                  Don't Resize Temp tablespace and Don't add the datafile in Temp Tablespace.

1. Check Free Space Availability in Temp Tablespace.

Check:
SQL>  select file_name,file_id,tablespace_name,bytes from dba_temp_files;


FILE_NAME                         FILE_ID           TABLESPACE_NAME                     BYTES
-------------------------              ----------             ------------------------------                  ----------
/u01/slm/dev/temp01.dbf            1                      TEMP                                         20971520


 
2. Find out Who is using More Temp tablespace.

Check:

SQL>SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value,

u.segtype, u.contents, u.blocks FROM v$session s, v$tempseg_usage u

WHERE s.saddr=u.session_addr order by u.blocks;







 SID USERNAME        TABLESPACE HASH_VALUE         SEGTYPE   CONTENTS      BLOCKS

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

      2749 SLM TEMP01     0/2004054495        LOB_DATA  TEMPORARY        128

      2750 SLM TEMP01     0/1950821498        LOB_DATA  TEMPORARY        128

      6481 SLM TEMP01     0/104254066         LOB_DATA  TEMPORARY        128

      9842 SLM TEMP01     0/1950821498        LOB_DATA  TEMPORARY        128

      8470 SLM TEMP01     0/1655124149        LOB_DATA  TEMPORARY        128

      8176 SLM TEMP01     0/487817532         LOB_DATA  TEMPORARY        128

      6449 SYS  TEMP01     0/487817532         LOB_DATA  TEMPORARY        128

           

 

3. Send the mail to Application Team support,this user Using more Temp tablespace.



                                                          Thank You