Re: ORA-1555 error with AUM already enabled
- From: Janine Sisk <janine@xxxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Tue, 25 May 2010 16:18:30 -0700
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> 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
> Home Page: http://jaredstill.com
>
---
Janine Sisk
President/CEO of furfly, LLC
503-693-6407
Other related posts:
