Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3151
  • 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
 
sujith80Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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