Customer Support Language
 
HomeKnowledge BaseCampaignMaxOracle returns an ORA-01555 error during long query
Information
Article ID140
Created On2/21/2006
Modified9/11/2008
Share With Others
Oracle returns an ORA-01555 error during long query
One of the causes of this error is the information in rollback segments being overwritten before the query has finished. The UNDO_RETENTION parameter controls how long data is kept in the rollback segments. The default install is setting this value to 10 seconds which may be too little for some sites.

The Oracle docs recommend 900 seconds (15 minutes).

To change the value, login as the SYSTEM user and issue these commands:

select value from v$parameter where name = 'undo_retention';
---> value was 10

alter system set undo_retention = 900 scope = both;
---> changed per oracle documentation because this is the default value (represents about 15 minutes)

Another possible cause of this problem is the undo tablespace may not be large enough. To enlarge this:

alter database
datafile 'e:\douglas\oracle\oradata\orcl\undotbs01.dbf'
autoextend on maxsize 200m;