Daniel
How can i use the v$undostat.maxquerylen value to configuure the undo
tablespace?
Also, how can I tell what the longest transaction is actually doing?, i.e
what sql is being run?
John
-Original Message-
Sent: 05 January 2004 17:54
To: Multiple recipients of list ORACLE-L
]
Sent: Tue 1/6/2004 3:54 AM
To: Multiple recipients of list ORACLE-L
Cc:
Subject:RE: undotbs01.dbf just keeps growing
Daniel
How can i use the v$undostat.maxquerylen value to configuure the undo
tablespace?
Also, how can I tell what the longest transaction is actually doing
I had the same thing happen in a DB I inherited. Turns out the PCTINCREASE
was set to 50%! I set optimal and tried to shrink, but extents were located
throughout. The only thing I could do was drop the tablespace and recreate
it and all the rollback (undo) segments.
HTH
Mike
-Original
Jared,
It sure is nice to be missed. I'll make sure my secretary calls you about my
future vacation plans...:)
You've nailed the problem. Autoextend, automatic undo and high undo retention is
a recipe for high disk usage. The aum algorithm is such that preference is given
to extending over reuse
Thanks Jared
You should probably investigate why it continues to grow so large
Whats the best way to go about identifying any large transactions?
John
-Original Message-
Sent: 31 December 2003 04:34
To: Multiple recipients of list ORACLE-L
The data file(s) for your undo tablespace
Whats the best way to go about identifying any large transactions?
Ask the developers and users.
As for the size of the UNDO TBS, check and modify your
retention times as suggested by Anjo, and control the
autoextending of the datafiles.
Jared
On Wed, 2003-12-31 at 01:39, John Dunn wrote:
Reduce your undo_retention time/period.
Anjo.
-Original Message-
John Dunn
Sent: Tuesday, December 30, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L
I have recently installed Standard Engine 9.2 on AIX 5.2 and notice that
the undotbs01.dbf file just keeps on growing. It is now
The data file(s) for your undo tablespace is likely set
as autoextend with an unlimited size.
Run the attached script to check it.
If so, you can use this to put a limit on it:
alter database datafile 'your file name' autoextend on next 200m
maxsize 2000m;
Adjust the numbers for your system.
