Hi All,
I've been reading some materials to solve this issue on our database (Oracle 11.2.0.3.0) . The problem is that we have been increasing UNDO from 35G to almost 60G now. and it is still increasing in usage. But I cannot detect any transaction using the undo. If you have any insights, that will be great. Below are the information I have gathered.
Datafiles are not autoextensible.
FILE_NAME BYTES/1048576 AUT MAXBYTES/1048576
------------------------------------------------------------ ------------- --- ----------------
****/undotbs1.260.802515529 31744 NO 0
****/undotbs1.270.828813025 28672 NO 0
Some undo parameters.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS1
SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
19628
SQL> select max(undoblks) from v$undostat;
MAX(UNDOBLKS)
-------------
9706
I cannot detect any transaction using the UNDO except for a MERGE statement that has been running for almost 3 weeks but it does not show up on the UNDO except for this Toad.exe. Could it be that there were some transactions not commited on the App side?
SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo
--------------- ---------- ---------------------------------------- ------------------------- ---------------
1265,47681 ENGI Toad.exe _SYSSMU11_3724053292$ 0K
Rollback Size Undo Com- Command
Segment Kbyte Waits Extents SID Username records LOG_IO mand MODULE desciption
------------------------------ ---------- ----- ------- ----- ------------ ---------- ---------- ---- ------------ ---------------
SYSTEM 376 0 6
_SYSSMU1_2397580260$ 1,116,152 7 202
_SYSSMU2_2704140093$ 1,179,768 9 195
_SYSSMU3_2176824562$ 1,185,912 6 115
_SYSSMU4_403671272$ 1,120,376 11 185
_SYSSMU5_3141210127$ 1,121,336 9 206
_SYSSMU6_3100186656$ 1,184,760 10 203
_SYSSMU7_3622910069$ 1,250,360 7 206
_SYSSMU8_1575691382$ 2,823,288 11 220
_SYSSMU9_2782336430$ 2,796,664 56 165
_SYSSMU10_1843652744$ 3,331,184 261 580
_SYSSMU11_3724053292$ 2,797,688 641 51 1265 TARIFFENGINE 0 13 0 TOAD 11.5.0. Unknown
56
Thanks..