Yesteday, I had a call from client, saying db was very slow.
Thu Feb 25 14:50:31 2010
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
Time: 25-FEB-2010 14:50:31
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=202.157.69.223)(PORT=62300))
ORA-01555 caused by SQL statement
Are you observing any errors for undo tablespace or are you just concerned with the growth of the undo tablespace. Please note that unless you get ora-1555 or ora -30036 you need not increase the undo tablespace.
Run this query when undo tablespace is full
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
ACTIVE - Undo Extent is Active, Used by a transaction.
EXPIRED - Undo Extent is expired (Exceeded the Undo Retention).
UNEXPIRED - Undo Extent will be required to honour UNDO_RETENTION.
in case you see lot of expired extents, then no need to increase undo tablespace as the extents will be reused.
