unable to extend temp

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Wed, 31 Jul 2019 08:56:51 +1000
Message-ID: <CAFeFPA-eB49PEQYiL-vaKvYy=aNCMCptUEKgjWLy09cBt3MJ0Q_at_mail.gmail.com>

Hi All

Oracle 12.2.0.1 RAC exadata OEL

In our test PDB we had an issue yesterday where we were getting s lot of unable to extent in tablespace temp messages.

Fair enough so I checked the sort_usage but this came back with hardly any temp being used at the time.
I have searched metalink and found a bug that was fixed in in version 11 or so..
Anyone aware of anything that could cause this

SELECT   b.TABLESPACE


       , b.segfile#

       , b.segblk#

       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb

       , a.inst_ID

       , a.SID

       , a.serial#

       , a.username

       , a.osuser

       , a.program


       , a.status


    FROM gv$session a


       , gv$sort_usage b

       , gv$process c


       , gv$parameter p


   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP2'
ORDER BY a.inst_ID , b.TABLESPACE


       , b.segfile#

       , b.segblk#


       , b.blocks;

at the time we had parameter temp_undo_enabled set to true (testing this across all our test environments)

so I also checked

select * from gv$tempundostat

which also did not return a usage consistent with running out of temp.

The bug I found led me to a couple of sql statements which do not mean a whole lot to me

select inst_id, tablespace_name,
round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)"  from gv$sort_segment
 where tablespace_name='TEMP'
 order by 1;

 select sum(bytes)/1024/1024/1024, owner from gv$temp_extent_map group by owner;

  select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;

what are they supposed to represent as the only information the document provided was that it could assist with troubleshooting

Jack van Zanen

---

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 31 2019 - 00:56:51 CEST