ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP_NEW’
Posted by Mir Sayeed Hassan on January 1st, 2020
ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP_NEW’
I faced the above error while creating the explain plan for one of the table in database as shown below
Create an explain plan for analysing to create an index duration & size
SQL> explain plan for create index idx_tpay_recon_C_OCCURDATE on PAYMENT_NEW.T_REPORT(C_OCCURDATE); explain plan for create index idx_tpay_recon_C_OCCURDATE on PAYMENT_NEW.T_REPORT(C_OCCURDATE) * 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_NEW'
Check the status of undo tablespace
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1
SQL> alter system set undo_management=auto scope=spfile; System altered.
Shutdown database
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Startup database
SQL> startup ORACLE instance started. Total System Global Area 2.7793E+10 bytes Fixed Size 2266504 bytes Variable Size 1.2751E+10 bytes Database Buffers 1.5032E+10 bytes Redo Buffers 7307264 bytes Database mounted. Database opened.
Verify the undo tablespace
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
SQL> Select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE TEST_NEW ONLINE 6 rows selected.
Create an explain plan
SQL> explain plan for create index idx_tpay_recon_C_OCCURDATE on PAYMENT_NEW.T_REPORT(C_OCCURDATE); Explained.
Check the status of explain plan
SQL> set linesize 500 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2371824411 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | CREATE INDEX STATEMENT | | 5042K| 52M| 61440 (1)| 00:12:18 | | 1 | INDEX BUILD NON UNIQUE| IDX_TPAY_RECON_C_OCCURDATE | | | | | | 2 | SORT CREATE INDEX | | 5042K| 52M| | | | 3 | TABLE ACCESS FULL | T_REPORT | 5042K| 52M| 58888 (1)| 00:11:47 | ----------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Note ----- - estimated index size: 125M bytes 14 rows selected.
=====Hence tested & verified in our test env=====