Oracle ORA-01552 Tips
ORA-01552
undo_management => manual can cause ORA-01552 while executing dml sql.
SYSTEM@pdb1> explain plan for create index hong.idx_t1_object_name_object_id on hong.t1(object_name,object_id);
explain plan for create index hong.idx_t1_object_name_object_id on hong.t1(object_name,object_id)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
SYSTEM@pdb1> select segment_name, status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1_3588498444$ OFFLINE UNDOTBS1
_SYSSMU2_2971032042$ OFFLINE UNDOTBS1
_SYSSMU3_3657342154$ OFFLINE UNDOTBS1
_SYSSMU4_811969446$ OFFLINE UNDOTBS1
_SYSSMU5_3018429039$ OFFLINE UNDOTBS1
_SYSSMU6_442110264$ OFFLINE UNDOTBS1
_SYSSMU7_2728255665$ OFFLINE UNDOTBS1
_SYSSMU8_801938064$ OFFLINE UNDOTBS1
_SYSSMU9_647420285$ OFFLINE UNDOTBS1
_SYSSMU10_2262159254$ OFFLINE UNDOTBS1
11 rows selected.
SYSTEM@pdb1>
Soltion
Fix: alter system set undo_management=auto scope=spfile; and restart instance.
SYSTEM@pdb1> conn / as sysdba
Connected.
SYS@cdb1> alter system set undo_management=auto scope=spfile;
System altered.
SYS@cdb1> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@cdb1> startup
ORACLE instance started.
Total System Global Area 1577055552 bytes
Fixed Size 9135424 bytes
Variable Size 973078528 bytes
Database Buffers 587202560 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SYS@cdb1> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB2 MOUNTED
6 PDB1 READ WRITE NO
SYS@cdb1> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SYS@cdb1>
Test again and result is OK now.
HONG@pdb1> select segment_name, status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU1_3588498444$ ONLINE UNDOTBS1
_SYSSMU2_2971032042$ ONLINE UNDOTBS1
_SYSSMU3_3657342154$ ONLINE UNDOTBS1
_SYSSMU4_811969446$ ONLINE UNDOTBS1
_SYSSMU5_3018429039$ ONLINE UNDOTBS1
_SYSSMU6_442110264$ ONLINE UNDOTBS1
_SYSSMU7_2728255665$ ONLINE UNDOTBS1
_SYSSMU8_801938064$ ONLINE UNDOTBS1
_SYSSMU9_647420285$ ONLINE UNDOTBS1
_SYSSMU10_2262159254$ ONLINE UNDOTBS1
11 rows selected.
HONG@pdb1>
HONG@pdb1> explain plan for create index idx_t1_object_id_status on t1(object_id,status);
Explained.
HONG@pdb1> set linesize 300 pagesize 300
HONG@pdb1> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 3795701758
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 73350 | 787K| 426 (1)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX_T1_OBJECT_ID_STATUS | | | | |
| 2 | SORT CREATE INDEX | | 73350 | 787K| | |
| 3 | TABLE ACCESS FULL | T1 | 73350 | 787K| 388 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Note
-----
- estimated index size: 2097K bytes
14 rows selected.
HONG@pdb1>
estimated index size: 2097K bytes
Reference
ORA-01552: cannot use system rollback segment for non-system tablespace “string”
Have a good work&life! 2021/10 via LinHong