Total Pageviews

Tuesday 11 September 2012

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

1. Identify temporary datafile details :

SQL> select file_name,TABLESPACE_NAME from DBA_TEMP_FILES;

2. Check if there is any space available in temporary tablespace (segment)


SELECT A.tablespace_name tablespace, D.mb_total,
SUM(A.used_blocks * D.block_size)/1024/1024 mb_used
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM(C.bytes)/1024/1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

SQL> select
         ((blocks*8192)+8192)/1024/1024 tblks ,
        s.username||' ('|| sid||','|| serial#||')' userinfo,
                t.piece, t.sql_text
    from v$session s, v$sort_usage u, v$sqltext t
    where s.saddr=u.session_addr
    and s.sql_id(+)=t.sql_id
    order by 1,2,3
    ;

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;


3.Fix:

a) Resize temporary file as
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/mydb/temp01.dbf’ RESIZE 3072M;

or

b) Add temp datafile to temporary tablespace as

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/mydb/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;

Root Cause Analysis

1. Identify temp segment usages:



SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;



Get 10 sessions with largest temp usage

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

1 comment:

  1. Jadwal Tarung Ayam SV388 17 Februari 2019 di Situs Judi Sabung Ayam Online Melalui Agen Resmi Taruhan Sabung Ayam Live Asli Thailand.

    Bandar Sabung Ayam - Minggu, 17 Februari 2019 – Pada Hari Tersebut Akan Di Laksanakan Berbagai Pertandingan Sabung Ayam Secara Live di Arena Sabung Ayam Thailand.

    Untuk Info Lebih Lanjut Bisa Hub kami Di :
    wechat : bolavita
    line : cs_bolavita
    whatsapp : +628122222995
    BBM: BOLAVITA

    ReplyDelete