Solved

ORA-01552:

Posted on 2007-03-29
1
1,267 Views
Last Modified: 2010-05-18
when I try to create a new table i got below error... i was looking at the action statement..but i am quite sure how to do this... can you show me the step by step instruction?


SQL Error: ORA-01552: cannot use system rollback segment for non-system tablespace 'GTABLES'
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.
0
Comment
Question by:jung1975
1 Comment
 
LVL 7

Accepted Solution

by:
gattu007 earned 500 total points
Comment Utility
You received the ORA-1552 error because you are trying to do something that
needs an ONLINE rollback segment other than SYSTEM's rollback segment.

The solution is to bring another rollback segment ONLINE. If there is no other
rollback segment than the SYSTEM rollback segment you will need to create an
additional one and bring it ONLINE.



A. Review the status of rollback segments with the following query:


       SQL> SELECT segment_name, status
            FROM dba_rollback_segs;

   This will give you all of the rollback segments and the status, offline or
   online. You will obtain something like this:

   SEGMENT_NAME            STATUS
   ----------------------  ----------
   SYSTEM                  ONLINE
   R01                     OFFLINE
   R02                     OFFLINE



B. To alter the status of a rollback segment:

       SQL> ALTER ROLLBACK SEGMENT <segment name> ONLINE [OFFLINE];

   For example to bring the R01 rollback segment ONLINE:

       SQL> ALTER ROLLBACK SEGMENT R01 ONLINE;



C. Review the rollback_segments parameter in the init.ora file

    rollback_segments  = (r01,r02)

   The private rollback segments indicated here will be ONLINE the next time
   the database is started.



D. Retry the operation.


                             -- o --    



SPECIAL SCENARIOS
-----------------

1. ORA-1552 TRYING TO CREATE AN OBJECT OUTSIDE SYSTEM TABLESPACE

  An attempt to create a new rollback segment outside the SYSTEM tablespace
  fails with ORA-01552.  

  See Note 1005227.6  ORA-1552 TRYING TO CREATE AN OBJECT OUTSIDE SYSTEM
                        TABLESPACE
 

                              -- o --
 

2. DEPRECIATION IS FAILING WITH APP-00988 ORA-1552 IN FADSCF.

  See Note 1164029.101  DEPRECIATION IS FAILING WITH APP-00988 ORA-1552
                          IN FADSCF


                              -- o --
 

3. ORA-00604, ORA-01552, ORA-02002: WHEN RUNNING MIGRATION UTILITY.

   See Note 1034830.6  ORA-00604, ORA-01552, ORA-02002: WHEN RUNNING
                         MIGRATION UTILITY


                              -- o --


4. ORA-01552: WHEN RUNNING ORACLE8 MIGRATION UTILITY.

  See Note 1060795.6  ORA-01552: WHEN RUNNING ORACLE8 MIGRATION UTILITY


                              -- o --


5. ORA-00604 AND ORA-1552 WHEN TRYING TO OPEN THE DATABASE

  Setting the event:
 
      event= "1552 trace name errorstack forever"
   or
      event= "1552 trace name errorstack level 3"

  Your current SQL statement for this session:
  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,)

6. CREATING ROLLBACK SEGMENTS IN A LOCALLY-MANAGED TABLESPACE REQUIRES
   A DUMMY ONE.

  See Note 112479.1  ORA-01552 Error Creating a Rollback Segment in a
                       Locally-Managed Tablespace


                              -- o --


7. FRM-40735 UNLOCKED TRIGGER RAISED UNHANDLED EXCEPTION ORA-01552
   USING ORACLE INVENTORY.

                              -- o --
RELATED DOCUMENTS
Note 62005.1  Creating, Optimizing, and Understanding Rollback Segments
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now