Solved

unable to extend TEMP tablespace error

Posted on 2007-03-30
7
3,132 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now