Oracle Database Discussions

Hi,

I have one issue with tremendous amount of undo generation through my global temporary table.

I have one global temporary table using “On commit preserve rows” clause.

I have been getting UNDO tablespace issues that it is getting full and oracle is unable to add extent.

I have been monitoring my undo using following sql.

Select T.START_TIME, T.USED_UBLK,(T.USED_UBLK*8)/1024 "Size in MB",S.USERNAME,R.SEGMENT_NAME,S.OSUSER,S.TERMINAL,SQ.SQL_TEXT
FROM V$TRANSACTION T, V$SESSION S, DBA_ROLLBACK_SEGS R, V$SQL SQ
WHERE T.SES_ADDR=S.SADDR
AND T.XIDUSN=R.SEGMENT_ID
AND S.SQL_ID=SQ.SQL_ID(+)


I found that maximum undo generation (4 to 6 GB) is happening when application tries to delete and update this global temporary table.

Could anyone guide here, As per my understanding Global temporary tables are not meant to generate undo?


Thanks in advance.