1、 Phenomenon

When migrating data to a new database, this error will be reported if a large save is performed

2、 Cause

Reasons for this error:

(1) SQL takes a long time to execute and needs to be optimized

(2) Rollback segment too small

(3) Undo save time is too short

3、 Specific inspection and recovery

1. Check the SQL execution plan to see if it can be optimized. Because this phenomenon occurs after the database migration, the old database can be executed successfully, but the new database can’t. this phenomenon is more common, so we don’t carry out specific SQL optimization first, but check the other two reasons first

2. Check rollback segment size:

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       (D.TOT_GROOTTE_MB/1024) "表空间大小(G)",
       ((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/1024) "已使用空间(G)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/D.TOT_GROOTTE_MB * 100, 2),'990.99') "使用比",
       (F.TOTAL_BYTES/1024) "空闲空间(G)",
       (F.MAX_BYTES/1024) "最大块(G)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES)/(1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES)/(1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES)/(1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 and f.TABLESPACE_NAME like 'UNDO%'
 ORDER BY 4 DESC;

 

The rollback segment is large enough and the utilization rate is not high, so it is not a problem of rollback segment

2. View undo_Retention parameter

In the command window of PL/SQL, enter:   show parameter undo_

Undo_Retention: refers to the storage time of undo data. It is a “target expected value”. The unit is seconds. The default is 900s

After the user sets this value, Oracle will try its best to keep the undo data beyond undo_Retention sets the time. However, if undo uses tense and no additional methods, this time period cannot be guaranteed. If this time is set to be small and the data will be overwritten soon, the error ora-01555 will be reported when the query statement is executed for a long time

I checked the parameter of the old library, which is 2700. After adjusting the parameters of the new library to be the same as those of the old library, it is saved and no error is reported

 

 alter  system set undo_retention=7200 ；--Requires relatively high privileges to modify system parameters (implemented by KIID)