RE: ORA-1555 error with AUM already enabled
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "janine@xxxxxxxxxx" <janine@xxxxxxxxxx>, Jared Still <jkstill@xxxxxxxxx>
- Date: Tue, 25 May 2010 19:31:20 -0400
No platform/Oracle Edition/Oracle version information! Bad DBA! Bad Oracle-L
poster! :)
Seriously, though....platform/edition/version/etc?
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Janine Sisk
Sent: Tuesday, May 25, 2010 7:19 PM
To: Jared Still
Cc: oracle-l L
Subject: Re: ORA-1555 error with AUM already enabled
SQL> select * from dba_data_files where tablespace_name like '%UNDO%';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/export2/oradata/afghan/undotbs01.dbf
3 UNDOTBS1 251658240 30720
AVAILABLE
3 YES 3.4360E+10 4194302 640 250609664
30592
ONLINE
I can certainly make it larger, and increase the retention time, but I guess I
am confused about what is actually being automatically managed? I got the
impression from something I read (can't find it now) that the setting of
undo_retention is just a starting point and that Oracle would adjust both the
retention time and the amount of space allocated based on real-world
conditions. Clearly, from the advice y'all are giving that is not the case. :)
So what does this feature actually do?
I should mention that the export file that results from this is only 14GB -
tiny for Oracle. So it's not like I'm pushing the boundaries in any
significant way.
janine
On May 25, 2010, at 3:30 PM, Jared Still wrote:
On Tue, May 25, 2010 at 3:22 PM, Janine Sisk
<janine@xxxxxxxxxx<mailto:janine@xxxxxxxxxx>> wrote:
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 1 with name
"_SYSSMU1_3780397527$" too small
I checked the parameters that control automatic undo management and found them
to be set correctly:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
Janine,
The undo_retention parameter is the amount of time in seconds that oracle
will try to maintain undo to rebuild queries.
You can still run out of space.
What does this query show:
select * from dba_data_files where tablespace_name like '%UNDO%'
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com<http://jkstill.blogspot.com/>
Home Page: http://jaredstill.com<http://jaredstill.com/>
---
Janine Sisk
President/CEO of furfly, LLC
503-693-6407
Other related posts:
