• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3157
  • Last Modified:

unable to extend TEMP tablespace error

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
Naveen Kumar
Asked:
Naveen Kumar
1 Solution
 
SujithData ArchitectCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerAuthor Commented:
I am not getting any output when i run the above query when my create table stmt was executing.
0
 
gattu007Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
gattu007Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerAuthor Commented:
Don't have time to wait for other experts comments on this question. So closed it and given the points.
0
 
rajeshprasathCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now