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)
Similar Posts:
- [Solved] Insufficient Oracle table space: ora-01653: unable to extend table
- Resolution of ora-00600 Issues [4194]
- Database Start Error after Recovery: ORA-01092: ORA-30012: undo tablespace
- Ora-01653: table a cannot be extended by 1024 (in table space ABC)
- How to Solve Rac ORA-01102 error: cannot mount database in EXCLUSIVE mode
- [Solved] Record an error of expdp export ora-01555 caused by lob damage
- ORA-01033:ORACLE initialization or shutdown in …
- Oracle startup error ora-03113 solution
- [Solved] xtrabackup: error: xb_load_tablespaces() failed with error code 57
- An error is reported when sqoop imports data into MySQL database: error tool. Exporttool: error during export: export job failed!