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

Comments

Post a Comment

Popular posts from this blog

ORA-01110: data file 1: '/oradata/datafiles/system01.dbf'

-
ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' Error occur while restoring the database from the backup of RMAN RMAN> alter database open resetlogs; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 09/03/2017 16:05:14 ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oradata/datafiles/system01.dbf' To resolve this error fallow the below procedure – Hopefully you will succeed If you’re primary database is flashback & it has the standby – Fallow below procedure RMAN> run { 2> recover database; 3> } ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter datab
Read more

Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4)

-
Expdp from Higher version 12c (12.1.0.2) to lower version 11g (11.2.0.4) Export on 12c (12.1.0.2)   – Tested in our env : 10.20.0.82 ==================== SQL> select instance_name,version from v$instance; INSTANCE_NAME    VERSION ---------------- ----------------- ora12c           12.1.0.2.0 SQL> conn sys/sys as sysdba Connected. SQL> conn hassan/( password ); Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST SQL> select * from test; COLUMN1              NAME -------------------- -------------------- 1                    abc 2                    xyz [oracle@ora12c u02]$ mkdir dump [oracle@ora12c u02]$ chmod -R 777 dump/ [oracle@ora12c u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 25 16:22:46 2017 Copyright (c) 1982, 2014, Oracle.  All
Read more

Solutions: error while starting of Oracle Single instance ASM Database

-
1 st – Starting ASM [root@asmdb ~]#su - grid [grid@asmdb ~]$ asmcmd Connected to an idle instance ASMCMD> ls ASMCMD-08102: no connection to ASM; command requires ASM to run ASMCMD> [grid@asmdb ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 23 17:44:30 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Automatic Storage Management option SQL> conn / as sysasm Connected to an idle instance SQL> startup ORA-01078: failure in processing system parameters ORA-29701: unable to connect to Cluster Synchronization Service SQL> exit Disconnected 2nd : Starting DB [oracle@asmdb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 23 17:15:15 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Gl
Read more