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
Solved

ORA-01552:

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL SQL Search Across Columns 4 52
Where Does Time Value Come From for Database Insert or Update 4 33
ORA-02288: invalid OPEN mode 2 56
date show only hh:mm 2 25
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

856 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