Hi we are using version 11.2.0.4 of oracle Exadata. We are frequently seeing Ora-01628 error("ORA-01628: max # extents (32765) reached for rollback segment _SYSSMUXXX_XXX$") even if we have sufficient UNDO tablespace left. Then digging more into it we found certain matching behaviour which is described in below Oracle Doc, i.e the UNDO segment which got errored out found to be using lot of small extents(having size <=64K). When it failed we saw ~28k extents in the same segment were all having size 64k or less out of total 32k limit.
Troubleshooting ORA-1628 - max # extents (32765) reached for rollback segment <SEGMENT_NAME> (Doc ID 1580182.1)
Now as one of the quick solution given in above doc, we were trying to shrink the top segments manually, so we are doing that. But at same time we are trying to get hold of the sessions/transaction which were responsible for creating those large number of small extents in that segments and if we can fix the underlying code someway. In V$transaction i can only see one column related to extent i.e. "START_UEXT" which states the "Start extent number" and i am tring to JOIN this with "extent_id" of dba_undo_extents(with size <64k), but as a segment holds ~thousands of extents so this below query seems wrong one to locate the sessions responsible for creation of large extents. Is there any otherway to get this information fetched, so that we will get a clear picture of the session/sql which is actually making oracle to use/create those very small extents?
Edited:-
Or is it true that there can not be direct relation established between transaction and extents? Its always through segments and that rollback segment can hold data for multiple sessions/sqls/transactions, so is it that oracle doesn't maintain those session to extent relation in any of the underlying views?
select v.inst_id
, v.sid,(select substr(sql_text,1,50) from gv$sqlarea where sql_id= v.sql_id and rownum<2) sql_text,v.sql_id
, v.username
, t.used_ublk*8192/1024/1024/1024 as undo_meg
, r.segment_idSTART_UEXT
, r.segment_name as undo_seg_name, t.START_UEXT
from gv$session v , gv$transaction t , dba_rollback_segs r
where v.saddr = t.ses_addr
and v.inst_id = t.inst_id
and t.xidusn = r.segment_id
and t. in (select extent_id from dba_undo_extents de where tablespace_name like 'UNDO%' and de.segment_name= r.segment_name and bytes<=65536 and r.tablespace_name= de.tablespace_name )
order by undo_meg desc