Solved

ORA-01552:

Posted on 2007-03-29
1
1,283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
gattu007 earned 500 total points
ID: 18821067
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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