ORA-01552: cannot use system rollback segment for non-system tablespace “string”

I got ” ORA-01552: cannot use system rollback segment for non-system tablespace “string” ”  error in Oracle database.

ORA-01552: cannot use system rollback segment for non-system tablespace “string”

Details of error are as follows.

ORA-01552: cannot use system rollback segment for non-system tablespace "string"

Cause: Tried to use the system rollback segment for operations involving non-system tablespace.
 If this is a clone database then this will happen when attempting any data modification 
outside of the system tablespace. Only the system rollback segment can be online in a clone database.

Action: Create one or more private/public segment(s), shutdown and then startup again.
 May need to modify the INIT.ORA parameter rollback_segments to acquire private rollback 
segment. If this is a clone database being used for tablspace point in time recovery then
 this operation is not allowed. If the non-system tablespace has AUTO segment space
 management, then create an undo tablespace.

cannot use system rollback segment for non-system tablespace “string”

This ORA-01552 error is related with the system rollback segment for operations involving non-system tablespace.
If this is a clone database then this will happen when attempting any data modification outside of the system tablespace. Only the system rollback segment can be online in a clone database.

Create one or more private/public segment(s), shutdown and then startup again. May need to modify the INIT.ORA parameter rollback_segments to acquire private rollback segment. If this is a clone database being used for tablspace point in time recovery then this operation is not allowed. If the non-system tablespace has AUTO segment space
management, then create an undo tablespace.

This Error can only be Raised if UNDO_MANAGEMENT=manual , while executing any DML or DDL statements for tablespaces other than the SYSTEM tablespace.

The Most Frequent Cause is having only one online rollback segment exists in the database which is the SYSTEM rollback segment , Which can be Confirmed from the following :

SQL> select segment_name, status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU11_508543045$           OFFLINE          UNDOTBS2
_SYSSMU12_1326925045$          OFFLINE          UNDOTBS2
_SYSSMU13_882757287$           OFFLINE          UNDOTBS2
_SYSSMU14_3664063346$          OFFLINE          UNDOTBS2
_SYSSMU15_1230784178$          OFFLINE          UNDOTBS2
_SYSSMU16_3220246718$          OFFLINE          UNDOTBS2
_SYSSMU17_3340471171$          OFFLINE          UNDOTBS2
_SYSSMU18_1158896232$          OFFLINE          UNDOTBS2
_SYSSMU19_1570777968$          OFFLINE          UNDOTBS2
_SYSSMU20_226770654$           OFFLINE          UNDOTBS2

In This Case The issue can be fixed by one of the following solutions :

1) Switch to Automatic Undo Management

1- SQL> alter system set undo_management=auto scope=spfile;

System altered.

2- Restart the database.

SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

or

2) Create new Rollback segment

SQL> Create Rollback segment <RBS_name> Tablespace <TBS_name> ;

Tablespaces Applicable for creating new rollback segments can be Identified using :

SQL> select TABLESPACE_NAME,CONTENTS from dba_tablespaces where SEGMENT_SPACE_MANAGEMENT='MANUAL' and CONTENTS <> 'TEMPORARY' and TABLESPACE_NAME <> 'SYSTEM';

or

3) online existing Rollback segment

SQL> alter rollback segment "RBS_name" online;

2- using standby Database opened in read only

The Other Frequent Cause for Error ORA-01552 is Trying to Execute DML or DDL statements while being connected to standby Database opened in read only mode ,

The read only mode prevents the Use of DML or DDL statements , only Querying the Data are allowed .

This can be confirmed using :

SQL> select NAME,DATABASE_ROLE,OPEN_MODE from v$database;

The Solution in This Case is to Open the standby in the read-write mode , or Do Not use DML or DDL statements in standby Database in read only mode.

3- User Defined Triggers

User Defined Triggers Are usually used for Auditing or other certain purpose , which are implemented by the Application or the DBA ,

If The error are in the Form of :

ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace '<Tablespace_Name>'

ORA-00604 is an indication that the root cause of the ORA-01552 is a recursive statement issued by by a trigger on the database or the application level .

Also enabling errorstack trace for ORA-1552 will show that the current SQL statement is Different than the statement returns the ORA-01552 when Run , for example :

SQL> CREATE ROLLBACK SEGMENT rbs_upgrade TABLESPACE UNDO
STORAGE ( INITIAL 100M NEXT 100M MAXEXTENTS UNLIMITED );
CREATE ROLLBACK SEGMENT rbs_upgrade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

While enabling errorstack trace for ORA-1552 shows :

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
Current SQL statement for this session:
INSERT INTO XXX.AUDIT_INFO VALUES (USER, SYSDATE, :B3 ,ORA_SYSEVENT,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_NAME,:B2 ,:B1 )

This statement is the action of the Trigger when it fires.

Also the Call stack will contain one of the following functions which indicates the use of Triggers :

kktextrg
kkttrex
kktexeevt0
kktfrddltrg
kkxtexe
kxtex1

To Identify which Trigger is causing this You can use the following :

The Column TRIGGER_BODY in DBA_TRIGGERS Table is LONG Datatype , which will result in error ORA-00932 when trying to use it in Like clause ,

SQL> select OWNER,TRIGGER_NAME from DBA_TRIGGERS where STATUS='ENABLED' and TRIGGER_BODY like upper('%AUDIT_INFO%');
select OWNER,TRIGGER_NAME from DBA_TRIGGERS where STATUS='ENABLED' and TRIGGER_BODY like upper('%AUDIT_INFO%')
                                                                       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

so We need first to create a function to be able to use this column in like clause :

create or replace function TRIG_BODY( p_owner in varchar2,p_trigger_name in varchar2) return varchar2
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor,
'select trigger_body from DBA_TRIGGERS where owner = :x and trigger_name=:y',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_owner );
dbms_sql.bind_variable( l_cursor, ':y', p_trigger_name );

dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end TRIG_BODY;
/

create public synonym TRIG_BODY for TRIG_BODY;
grant execute on TRIG_BODY to public;

select owner,trigger_name,TRIGGER_TYPE,TRIGGERING_EVENT from DBA_TRIGGERS where TRIG_BODY(owner,trigger_name) like '%AUDIT_INFO%' and status='ENABLED';

The Trigger can be disabled by :

SQL> alter trigger <OWNER>.<TRIGGER_NAME> disable;

4- hidden parameter “_rollback_segment_count”

The issue can also be caused from hidden parameter “_rollback_segment_count” , you can check the current value by :

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
a.KSPPDESC "Describtion"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like '%_rollback_segment_count%';

You can see a similar following messages in the alert log file :

Please remove the line for “_rollback_segment_count” from the initialization parameter ,

or execute :

SQL> alter system reset "_rollback_segment_count" ;

then restart the database .

Other Exceptional Cases where the ORA-01552 can occur

There are some Other Exceptional Cases where the ORA-01552 can occur :

1- While creating undo tablespace or creating Rollback segment

The original Undo tablespace was offline dropped and the database was open after a fake recovery , also the CDC (Change Data Capture) trigger is enabled.

10046 trace or Error stack trace will show that the create undo tablespace is failing due to CDC trigger.

The Solution is to Disable the CDC trigger and then create the undo tablespace .

To disable the CDC trigger follow the below steps :

Once the undo tablespace is created successfully CDC triggers can be enabled and the system should be restarted with “_system_trig_enabled”=true

2- Running Oracle8 Migration Utility

The following error occurs when trying to run the Oracle8 Migration Utility:

It is also possible that the ORA-2002 may not be present, however, it is the same problem.

The errors occur when the “mig” executable attempts to run the “#^connect (migrate/migrate)” callout in “$ORACLE_HOME/rdbms/admin/migrate.bsq” file.

The errors occur only when the initialization parameter AUDIT_TRAIL=DB or if AUDIT_TRAIL=TRUE, and the SYS.AUD$ table (the database audit trail) is located in a tablespace other than SYSTEM.

The Migration Utility takes all non-SYSTEM rollback segments OFFLINE when it runs but in order to update a table outside the SYSTEM tablespace, a non-SYSTEM rollback segment must be used.

NOTE: Moving the “SYS.AUD”$ table to a non-SYSTEM tablespace is NOT supported.

Temporarily disabling auditing will prevent attempted updates to the “SYS.AUD$” table.

You need to temporarily disable database auditing by doing the following:

1. Shutdown the database. When the Migration Utility fails, it leaves the database running.

2. Edit the “init.ora file” by setting AUDIT_TRAIL=NONE.

3. Rerun the Migration Utility.

3- While Login to Read-Only Standby Database using Oracle Application (EBS)

While SQLPLUS connection to read-only standby database are working fine without any issue.

Enabling errorstack trace for ORA-1552 shows :

Note : select for update is DML statement which is not allowed in the read-only database.

This seems to be recursive statement by a trigger on the database or the application level .

The issue can be fixed by one of the following solutions :

1-Change the application code if you can, This is not possible every time as code is not available in some cases.

OR

2-Open the standby in the read-write mode

OR

3-Do not try connect from application when standby is in read-only mode.

4- Using Clone Database

If this is a clone database then this will happen when attempting any data modification outside of the system tablespace. Only the system rollback segment can be online in a clone database.

If this cloned database is being used for tablespace point in time recovery then this operation is not allowed.

If the non-system tablespace has AUTO segment space management, then create an undo tablespace.

5- While installing Oracle Communications ASAP

This error occurs because when ASAP’s Oracle database was created, all the non system rollback segments where offline.

Note : The ASAP installation will have to be redone.

These errors are found in the ASAP installation file CreateDatabase.log:

'Loading tables/IDENTITY.i'
CHECK (opt5_type = null or opt5_type like '-_'),
*
ERROR at line 18:

'Loading tables/alrm_cent.i'
CREATE TABLE tbl_alarm_log (
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace '<tablespace_name>'

In This Case The issue can be fixed by one of the following solutions , in this Link .

6- Bulk Load Catalog Items in Oracle Exchange

Bulk load catalog item job ends with failed status

ORA-01552: cannot use system rollback segment for non-system tablespace '<tablespace_name>'
LDR-00100: Database Error
<ERROR> SYS:LDR-00100:Database Error java.sql.SQLException: ORA-01552: cannot use system 
rollback segment for non- system tablespace '<tablespace_name>' at java.lang.Throwable.
<init>(Compiled Code) at java.lang.Exception.<init>(Compiled Code) at java.sql.SQLException.
<init>(Compiled Code) at oracle.jdbc.dbaccess.DBError.throwSqlException(Compiled Code) at
 oracle.apps.pom.loader.LoaderApplication.main(Compiled Code)

The Cause is having only one online rollback segment exists in the database which is the SYSTEM rollback segment

In This Case The issue can be fixed by one of the following solutions , in this Link .

7- Creating DBConsole with EMCA Fails With Error ORA-01552

While creating DBConsole via Enterprise Manager Configuration Assistant (EMCA), the repository creation fails with the below errors in emca_repos_config_timestamp.log

ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
ORA-06512: at "SYS.DBMS_STATS", line 13591
ORA-06512: at "SYS.DBMS_STATS", line 13898
ORA-06512: at "SYS.DBMS_STATS", line 16240
ORA-06512: at "SYS.DBMS_STATS", line 16282
ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 953
ORA-06512: at "SYS.DBMS_REGISTRY", line 561

The Cause is having only one online rollback segment exists in the database which is the SYSTEM rollback segment

In This Case The issue can be fixed by one of the following solutions , in this Link .

Then Recreate the DBConsole using command.

<ORACLE_HOME>bin/emca -config dbcontrol db -repos create

Refer Note 278100.1 How To Drop, Create And Recreate DB Control In A 10g Database

8- Error While Executing Catbundle Script

Running catbundle.sql script fails with below error:

450 DBMS_OUTPUT.PUT_LINE('Exception - patch NOT installed');
451 DBMS_OUTPUT.PUT_LINE(SQLERRM);
452 :scriptFile := :rdbmsAdminDir || 'nothing.sql'; -- We need to run something
453 END;
454 /
Current bundle installed: (0) - None
Starting bundle ID: 1
Processing components and files for bundle 1: CPUJan2012
Processing components and files for bundle 2: CPUApr2012
Processing components and files for bundle 3: CPUJul2012
Processing components and files for bundle 4: CPUOct2012
Exception - patch NOT installed
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

Entry has not been added in registry$history

UNDO_MANAGEMENT should be set to AUTO as shown below:

1. Set undo_management to AUTO.

SQL> alter system set undo_management=auto scope=spfile;

2. Bounce back the database.

SQL> startup force ;

3. Execute the catbundle script.

cd $ORACLE_HOME/rdbms/admin/

SQL> @catbundle.sql cpu apply

4. Verify registry$history and catbundle log from

 $ORACLE_HOME/cfgtoollogs/catbundle/catbundle_CPU_<dbname>_GENERATE_<timestamp>.log.

9- While logon to physical standby database

The Following error occurs While logon to physical standby database in read only mode.

ORA-604: error occurred at recursive SQL level 1
ORA-1552: cannot use system rollback segment for non-system tablespace

The issue is caused by Logon Trigger On Primary Database , which got created On the Physical Standby ,

Following error occurs when logon trigger attempt to inert data in physical standby database .

In This Case The issue can be fixed by one of the following solutions :

1- Disable the LOGON Trigger on the standby database :

SQL> alter trigger <trigger_name> disable;

OR

2- Rewrite the on-logon trigger by considering the database role so in case of physical standby logon trigger will not try execute any DML in database.

CREATE OR REPLACE TRIGGER log_audit_sess_info
AFTER LOGON on database
declare
db_role varchar2(30);
begin
select database_role
into db_role
from v$database;
If db_role <> 'PHYSICAL STANDBY' then
insert into ora_audit.log_audit_sess_info
select sid,audsid, username,nvl(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'), SYS_CONTEXT('USERENV','OS_USER')) osuser,machine,
SYS_CONTEXT('USERENV','IP_ADDRESS') terminal,program,module,logon_time
from v$session
where sid in (SELECT VS.SID
FROM V$SESSION VS, V$PROCESS VP
WHERE VS.PADDR = VP.ADDR
AND VS.USERNAME IS NOT NULL
AND VS.STATUS = 'ACTIVE'
AND VS.SID IN (SELECT SID
FROM V$MYSTAT));
end if;
end;
/

10- When Starting The Database

When trying to startup a database with a pending Transaction (2PC), startup will fail if index I_PENDING_TRANS1 for the PENDING_TRANS$ table is not located on the SYSTEM tablespace.

ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

To verify that you have encountered this issue, add the following event to your init<SID>.ora file and try to startup the database once again.

event=”1552 trace name errorstack level 10″

This event will generate a trace file to your user_dump_dest directory after startup.

The failing SQL statement in the trace file with the ORA-1552 error should be:

insert into pending_trans$ (local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id, tran_comment, state, status, heuristic_dflt,
session_vector, reco_vector, fail_time, reco_time, top_db_user, top_os_user, top_os_host, top_os_terminal, global_commit#, type#) values (:1, :2, :3, :4
:5, :6, 'P', :7, :8, :8, SYSDATE, SYSDATE, :9, :10, :11, :12,:13,:14)

Solution Description
——————–

In order to resolve the issue, the following steps are necessary.

1. Startup the DB with all rollback segments offline.

Please contact Oracle Support to get help with the syntax to accomplish this, since all rollback segments MUST BE offline for this to succeed.

2. Drop the index for PENDING_TRANS$.

SQL> drop index I_PENDING_TRANS1;

3. Recreate the index in the SYSTEM tablespace.

4. Shutdown the DB.

5. Startup the DB with all rollback segments available.

When a distributed transaction is pending, Oracle inserts a record in the PENDING_TRANS$ table.  This table does have one index, I_PENDING_TRANS1, and the index must be updated.

This operation cannot be performed when opening the database if the index is not located on the SYSTEM tablespace because of the following.

a) Only the SYSTEM rollback segment is online.

b) No operation can be performed on a non-SYSTEM tablespace using the SYSTEM rollback segment.

The root cause of this problem is the fact of having index I_PENDING_TRANS1 in a tablespace other than SYSTEM.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )