Solved

ORA-01552:

Posted on 2007-03-29
1
1,290 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

728 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