ORA-01552: cannot use system rollback segment for non-system tablespace ‘TEMP_NEW’

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=====