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: 0×0000.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
