Solved

unable to extend TEMP tablespace error

Posted on 2007-03-30
7
3,140 Views
Last Modified: 2009-08-21
I am getting 'ORA-01652: unable to extend temp segment by 64 in tablespace TEMP' this error when trying to create a table in my database.

The create table stmt is :

create table t1
as select a.c1, sum(a.c2)......avg(b.c3)....
from table1 a, table2 b
where .... ;

So i want to know which program/user/session is using the space allocated to TEMP tablespace currently. I want this info. so that i can kill that program/session or talk to other user about this. We are in situation where we cannot go and request for extra space in TEMP tablespace at the moment.  

I know that we need to increase the TEMP tablespace storage but that will happen in a weeks time. But i want this table to get created as this is a bit urgent.

Thanks,
0
Comment
Question by:Naveen Kumar
7 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 18822520
Try this sql:


SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace "Tablespace",
u.contents "Contents", u.extents "Extents", u.blocks*8/1024 "Used Space in MB", q.sql_text "SQL TEXT",
a.object "Object", k.bytes/1024/1024 "Temp File Size"
FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;
0
 
LVL 28

Author Comment

by:Naveen Kumar
ID: 18822748
I am not getting any output when i run the above query when my create table stmt was executing.
0
 
LVL 7

Accepted Solution

by:
gattu007 earned 500 total points
ID: 18822832
try these sql quries

SQL> select s.username, s.sid, u.tablespace, u.contents, u.segtype,
    round(u.blocks*8192/1024/1024,2) MB
    from v$session s, v$sort_usage u
    where s.saddr = u.session_addr
    and u.contents = 'TEMPORARY'
    order by MB DESC ;


sql>SELECT tablespace_name, extent_size, total_extents, used_extents,
         free_extents, max_used_size FROM v$sort_segment;  


From the output of the v$sort_segment query:
extent_size        : size of one extent, in number of Oracle blocks
total_extents      : total number of extents in the segment (free or in use)
used_extents       : total number of extents currently in use
free_extents       : total number of extents currently marked as free
max_used_size: maximum number of extents ever needed by an operation (like a sort):

sql> SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks FROM v$session s,
v$sort_usage u WHERE s.saddr=u.session_addr;


compute sum label 'Total Temp Used in MB' of size_mb on report break on report

select b.tablespace,b.segfile#,b.segblk#,round(((b.blocks*p.value)/1024/1024),2)
 size_mb    ,a.sid    ,a.serial#    ,a.username USERNAME    ,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
and a.username like upper('%&USERNAME%')
order by size_mb,b.tablespace,b.segfile#,b.segblk#,b.blocks;


Some common causes for OERR 1652 are -
  stale optimizer statistics
  a one-time anomoly
---------------------------------
If you are concerned with space pressure and there is space available, add
another datafile.


SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks
 FROM v$session s, v$sort_usage u
 WHERE s.saddr=u.session_addr;
   -> It will show you who is currently using the space and how much space
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:gattu007
ID: 18822840
These are some of the imp metalink docs related to temp issue

Note 1025288.6   How to Diagnose and Resolve ORA-01650, ORA-01652, ORA-01653,                  ORA-01654, ORA-01688 : Unable to Extend < OBJECT > by %S  in Tablespace %S

Note 1039341.6    Temporary Segments Are Not Being De-Allocated After a Sort
Note 177334.1       Overview of Temporary Segments

Note 161357.1 -- ORA-1652 Out of Space Errors in the Databases TEMPORARY Tablespace
Note 1013288.6 -- Unable to Extend Temp Tablespace Running Reports

Note 289894.1-Queries to monitor Temporary Tablespace usage
Note 102339.1 - Temporary Segments: What Happens When a Sort Occurs


### search MetaLink on:  resize tempfile

Note 273276.1  How to Shrink the datafile of Temporary Tablespace

Note 274283.1  How to resize Tempfiles if receive an ORA-03297 error Gen  RDBMS.
RD-9014  :

Note 132663.1  ORA-03296 Resizing Temporary Locally Managed Tablespace

Note 180578.1  Cannot Resize Tempfiles in Bitmapped Temporary Tablespaces

Note 161103.1  Space For a Tempfile Not Allocated In The Filesystem


### search MetaLink on:  'create temporary tablespace' tempfile

Note 160426.1  TEMPORARY Tablespaces Tempfiles or Datafiles ?

Note 97458.1  ORA-01516 When Renaming Locally-Managed TEMPFILES

Note 61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
0
 
LVL 28

Author Comment

by:Naveen Kumar
ID: 18823455
select s.username, s.sid, u.tablespace, u.contents, u.segtype,
    round(u.blocks*8192/1024/1024,2) MB
    from v$session s, v$sort_usage u
    where s.saddr = u.session_addr
    and u.contents = 'TEMPORARY'
    order by MB DESC ;

select b.tablespace,b.segfile#,b.segblk#,round(((b.blocks*p.value)/1024/1024),2)
 size_mb    ,a.sid    ,a.serial#    ,a.username USERNAME    ,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
and a.username like upper('%&USERNAME%')
order by size_mb,b.tablespace,b.segfile#,b.segblk#,b.blocks;

These two queries helped to find the info. to some extent which i wanted for my analysis.
 
Thanks
0
 
LVL 28

Author Comment

by:Naveen Kumar
ID: 18823471
Don't have time to wait for other experts comments on this question. So closed it and given the points.
0
 
LVL 2

Expert Comment

by:rajeshprasath
ID: 25153674
Hi
 
 I followed the steps mentioned in the article http://technotes.towardsjob.com/oracle/ora-01652-unable-to-extend-temp-segment-by-64-in-tablespace/ and i can able to get thorough the error. Please try and let me know if it works
 
 Thanks
 Nitin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question