AUM and ORA-01555

AUM (Automatic Undo Management). In his August 30, 2006 blog entry, Laurent Schneider discussed his experience with ORA-01555 errors in past and current versions of Oracle.

I encountered this error Monday in my Oracle9i database for the first time on a specific Production system. Turns out the application, Clarity, was recently upgraded over the weekend of September 23.

Nice thing about the error message in the Alert log is the additional information it supplies such as the SQL statement and the duration of the query. Duration in this case is the time until the error was encountered.

Mon Oct 2 17:14:55 2006
ORA-01555 caused by SQL statement below (Query Duration=3332 sec, SCN: 0x0000.18673b7c):
Mon Oct 2 17:14:55 2006
update clb_temp_search_ids set is_attachment = 1 where id in
(select vi.id version_id from clb_dms_files fi, clb_dms_folders fo,
clb_dms_versions vi, odf_actionitem_v2 inst, clb_dms_folders pa
where fi.parent_folder_id = fo.id and vi.file_id = fi.id and
fo.parent_folder_id = pa.id and fo.assoc_obj_id = inst.odf_pk
and pa.assoc_obj_id = inst.odf_pk and
pa.assoc_obj_type = ‘actionitem’)

Some query tuning is in order here before I make any adjustments to the UNDO_RETENTION parameter. I ran a SELECT COUNT(*) on the sub-query to get an idea of how long it takes. I killed the query after 25 minutes and no results returned.

References
ORA-01555 Using Automatic Undo Management – Causes and Solutions
Metalink Note 269814.1

10g NEW FEATURE on AUTOMATIC UNDO RETENTION
Metalink Note 240746.1

The Do’s and Dont’sof Space & Undo Management:
Best Practices for Oracle Database 10g

Oracle OpenWorld 2004 Presentation and White Paper

Advertisements

2 Responses to AUM and ORA-01555

  1. Jhansi says:

    Hi,
    I got this error, could u pls help me to solve this issue..

    ORA-01555 caused by SQL statement below (Query Duration=18857 sec, SCN: 0x071c.b2be3d34):

  2. Jhansi says:

    ORA-01555 caused by SQL statement below (Query Duration=18857 sec, SCN: 0x071c.b2be3d34):

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: