10 Replies Latest reply: Jun 17, 2013 4:57 AM by Rash_ RSS

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

    Vivek

      Oracle Version:-10.2.0.5.0

       

      I have undo tablespace, but still getting below error.

       

       

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

       

       

      Also find below output.

       

      09:23:01 kalkulus_158 >select segment_name,status from dba_rollback_segs;

       

      SEGMENT_NAME                   STATUS

      ------------------------------ --------

      SYSTEM                         ONLINE

      _SYSSMU16960$                  ONLINE

      _SYSSMU16961$                  ONLINE

      _SYSSMU16962$                  ONLINE

      _SYSSMU16963$                  ONLINE

      _SYSSMU16964$                  ONLINE

      _SYSSMU16965$                  ONLINE

      _SYSSMU16966$                  ONLINE

      _SYSSMU16967$                  ONLINE

      _SYSSMU16968$                  ONLINE

      _SYSSMU16969$                  ONLINE

      _SYSSMU16970$                  ONLINE

      _SYSSMU16971$                  ONLINE

      _SYSSMU16972$                  ONLINE

      _SYSSMU16973$                  ONLINE

      _SYSSMU16974$                  ONLINE

      _SYSSMU16975$                  ONLINE

      _SYSSMU16976$                  OFFLINE

      _SYSSMU16977$                  OFFLINE

      _SYSSMU16978$                  OFFLINE

      _SYSSMU16979$                  OFFLINE

      _SYSSMU16980$                  OFFLINE

      _SYSSMU16981$                  OFFLINE

      _SYSSMU16982$                  OFFLINE

      _SYSSMU16983$                  OFFLINE

      _SYSSMU16984$                  OFFLINE

      _SYSSMU16985$                  OFFLINE

      _SYSSMU16986$                  OFFLINE

      _SYSSMU16987$                  OFFLINE

      _SYSSMU16988$                  OFFLINE

      _SYSSMU16989$                  OFFLINE

      _SYSSMU16990$                  OFFLINE

      _SYSSMU16991$                  OFFLINE

      _SYSSMU16992$                  OFFLINE

      _SYSSMU16993$                  OFFLINE

      _SYSSMU16994$                  OFFLINE

      _SYSSMU16995$                  OFFLINE

      _SYSSMU16996$                  OFFLINE

      _SYSSMU16997$                  OFFLINE

      _SYSSMU16998$                  OFFLINE

      _SYSSMU16999$                  OFFLINE

      _SYSSMU17000$                  OFFLINE

      _SYSSMU17001$                  OFFLINE

      _SYSSMU17002$                  OFFLINE

      _SYSSMU17003$                  OFFLINE

      _SYSSMU17004$                  OFFLINE

      _SYSSMU17005$                  OFFLINE

      _SYSSMU17006$                  OFFLINE

      _SYSSMU17007$                  OFFLINE

      _SYSSMU17008$                  OFFLINE

      _SYSSMU17009$                  OFFLINE

       

       

       

      51 rows selected.

        • 1. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
          sb92075

          [oracle@localhost ~]$ oerr ora 1552

          01552, 00000, "cannot use system rollback segment for non-system tablespace '%s'"

          // *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.

          • 2. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
            Dizwell

            What is the status of the undo tablespace?

             

            You can, for example, ask to take the undo tablespace offline. If there are pending transactions, then the tablespace will go into "pending offline" mode, but the undo segments it contains will remain online until those pending transactions complete. For any new transactions that begin, however, the 'pending offline' status will mean that they are not allowed to use the auto-managed undo segments. Without them, they are forced to use the system rollback segment... but, as the error message says, that's not allowed to be used for transactions in non-system tablespace.

             

            So my diagnostic question to you would be: what's the status of your undo tablespace? Online, offline or pending offline?

            • 3. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
              Alvaro

              Things to check:

               

              1. Status of your UNDO tablespace. Should be online.

              2. Your spfile/init: Check if Automatic Undo Management is ON.

              3. Check if your instance is set to use the correct UNDO tablespace through the UNDO_TABLESPACE parameter.

               

              In my experience, this error usually occurs when the instance is set to use a non-existant UNDO tablespace or the parameter is incorretly set/typo. This leads to Oracle trying to use the System rollback segments for user transactions, which raises the aforementioned error.

              • 4. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
                Vivek

                Please Check below.

                 

                1.kalkulus_158 >show parameter undo

                 

                NAME                                    TYPE                                VALUE

                ------------------------------------ -------------------------------- ------------------------------

                undo_management                  string                              AUTO

                undo_retention                       integer                              900

                undo_tablespace                     string                             UNDOTBS01

                 

                 

                 

                2.kalkulus_158 >select STATUS,TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME like '%UNDO%';

                 

                STATUS   TABLESPACE_NAME

                -------- ------------------------------

                ONLINE   UNDOTBS01

                 

                1 row selected.

                 

                3. kalkulus_158 >select segment_name,tablespace_name,status from dba_rollback_segs;

                 

                 

                SEGMENT_NAME           TABLESPACE_NAME                STATUS

                ------------------------------ ------------------------------ --------

                SYSTEM                             SYSTEM                              ONLINE

                _SYSSMU16960$                  UNDOTBS01                      ONLINE

                _SYSSMU16961$                  UNDOTBS01                      ONLINE

                _SYSSMU16962$                  UNDOTBS01                      ONLINE

                _SYSSMU16963$                  UNDOTBS01                      ONLINE

                _SYSSMU16964$                  UNDOTBS01                      ONLINE

                _SYSSMU16965$                  UNDOTBS01                      ONLINE

                _SYSSMU16966$                  UNDOTBS01                      ONLINE

                _SYSSMU16967$                  UNDOTBS01                      ONLINE

                _SYSSMU16968$                  UNDOTBS01                      ONLINE

                _SYSSMU16969$                  UNDOTBS01                      ONLINE

                _SYSSMU16970$                  UNDOTBS01                      ONLINE

                _SYSSMU16971$                  UNDOTBS01                      ONLINE

                _SYSSMU16972$                  UNDOTBS01                      ONLINE

                _SYSSMU16973$                  UNDOTBS01                      ONLINE

                _SYSSMU16974$                  UNDOTBS01                      ONLINE

                _SYSSMU16975$                  UNDOTBS01                      ONLINE

                _SYSSMU16976$                  UNDOTBS01                      OFFLINE

                _SYSSMU16977$                  UNDOTBS01                      OFFLINE

                _SYSSMU16978$                  UNDOTBS01                      OFFLINE

                _SYSSMU16979$                  UNDOTBS01                      OFFLINE

                _SYSSMU16980$                  UNDOTBS01                      OFFLINE

                _SYSSMU16981$                  UNDOTBS01                      OFFLINE

                _SYSSMU16982$                  UNDOTBS01                      OFFLINE

                _SYSSMU16983$                  UNDOTBS01                      OFFLINE

                _SYSSMU16984$                  UNDOTBS01                      OFFLINE

                _SYSSMU16985$                  UNDOTBS01                      OFFLINE

                _SYSSMU16986$                  UNDOTBS01                      OFFLINE

                _SYSSMU16987$                  UNDOTBS01                      OFFLINE

                _SYSSMU16988$                  UNDOTBS01                      OFFLINE

                _SYSSMU16989$                  UNDOTBS01                      OFFLINE

                _SYSSMU16990$                  UNDOTBS01                      OFFLINE

                _SYSSMU16991$                  UNDOTBS01                      OFFLINE

                _SYSSMU16992$                  UNDOTBS01                      OFFLINE

                _SYSSMU16993$                  UNDOTBS01                      OFFLINE

                _SYSSMU16994$                  UNDOTBS01                      OFFLINE

                _SYSSMU16995$                  UNDOTBS01                      OFFLINE

                _SYSSMU16996$                  UNDOTBS01                      OFFLINE

                _SYSSMU16997$                  UNDOTBS01                      OFFLINE

                _SYSSMU16998$                  UNDOTBS01                      OFFLINE

                _SYSSMU16999$                  UNDOTBS01                      OFFLINE

                _SYSSMU17000$                  UNDOTBS01                      OFFLINE

                _SYSSMU17001$                  UNDOTBS01                      OFFLINE

                _SYSSMU17002$                  UNDOTBS01                      OFFLINE

                _SYSSMU17003$                  UNDOTBS01                      OFFLINE

                _SYSSMU17004$                  UNDOTBS01                      OFFLINE

                _SYSSMU17005$                  UNDOTBS01                      OFFLINE

                _SYSSMU17006$                  UNDOTBS01                      OFFLINE

                _SYSSMU17007$                  UNDOTBS01                      OFFLINE

                _SYSSMU17008$                  UNDOTBS01                      OFFLINE

                _SYSSMU17009$                  UNDOTBS01                      OFFLINE

                 

                51 rows selected.

                • 5. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
                  Vivek

                  Also find below information.

                   

                   

                   

                   

                   

                  kalkulus_158 >select BEGIN_TIME,END_TIME,UNDOBLKS,MAXQUERYLEN,NOSPACEERRCNT,ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS from v$undostat where begin_time like '17-06-13 06:33%' and end_time like '17-06-13 06:43%';

                   

                  BEGIN_TIME         END_TIME                  UNDOBLKS   MAXQUERYLEN     NOSPACEERRCNT     ACTIVEBLKS      UNEXPIREDBLKS     EXPIREDBLKS

                  ----------------- -------    ---------- ---------- ----      ------- -------------       -----------

                  17-06-13 06:33:18    17-06-13 06:43:18        25835                    863                       7                             928                     50784                         640

                   

                  1 row selected.

                  • 6. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
                    Alvaro

                    Hmm, at first glance, It would seem you either have unusually high DML load on the system or your undo is under-sized. I'd say a count of 7 for NO SPACER ERROR COUNT in a 10 minute interval is a bit high, almost one query per minute was failing due to not being able to allocate space in the UNDO tablespace.

                     

                    Have you ran UNDO sizing advisor?

                    • 7. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
                      Vivek

                      Hie,

                       

                      1.I have executed the UNDO Sizing Advisor. Please find below information.

                       

                       

                      kalkulus_158 >SELECT OWNER,TASK_NAME,TYPE,impact_type FROM DBA_ADVISOR_FINDINGS where task_id='42933';

                       

                      OWNER                          TASK_NAME                      TYPE                                 IMPACT_TYPE

                      ------------------------------------------------------------------------------------------------------------------------------------

                      SYS                            TASK_42933                     PROBLEM                          Undo tablespace is OK.

                       

                       

                      2.Also find sizing of undo tablespace

                       

                      12:37:35 kalkulus_158 >select file_id,file_name,(bytes/1024/1024)MB,AUTOEXTENSIBLE, MAXBYTES/1024/1024 "MAX size Mb" from dba_data_files where tablespace_name='UNDOTBS01';

                       

                         FILE_ID FILE_NAME                                                                                         MB        AUTOEXTENSIBLE                         AUT MAX size Mb

                      ---------- ---------------------------------------------------------------------- ---------- --- -----------

                              27 G:\ORACLE\KALKULUS\UNDOTBS01_01.DBF                                         539              YES                                          32767.9844

                              28 H:\ORACLE\KALKULUS\UNDOTBS01_02.DBF                                        1220             YES                                          32767.9844

                              29 E:\ORACLE\ORADATA\KALKULUS\UNDOTBS01_03.DBF                         578             YES                                          32767.9844

                              30 H:\ORACLE\KALKULUS\UNDOTBS01_04.DBF                                         541              YES                                           2048

                              31 H:\ORACLE\KALKULUS\UNDOTBS01_05.DBF                                         498              YES                                          32767.9844

                      • 8. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
                        Vivek

                        Basing on the information provided, can you please revert.

                        • 9. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
                          Hemant K Chitale

                          Why report V$UNDOSTAT only for 10minutes ?  Look over the V$UNDOSTAT statistics for a few hours.  Look at UNDOBLKS, ACTIVEBLKS, UNEXPIREDBLKS, EXPIREDBLKS, TUNED_UNDORETENTION and MAXQUERYLEN ?

                           

                           

                          Hemant K Chitale


                          • 10. Re: ORA-01552: cannot use system rollback segment for non-system tablespace
                            Rash_

                            post undo datafile status, query causes this error ,query user and sho parameter rollback_seg