Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Err msg about rollback, we're using UNDO

    We're not using rollback segments, but we received the following error today:

    insert into rpt.rpt_namet
    ( item_name, visible, tk, id_op_add, dt_tm_add )
    VALUES
    ( 'zzzzzzzzzzzzzzz', 'V', 1056 , 'schworgj' , sysdate )


    The error I get is:
    cannot use system rollback segment for non-system table space 'RPT_DATA'


    I'm not sure where to start. Metalink searches for "cannot use system rollback segment for non-system table space" all yielded discussions on rollback segments.

    Thanks,
    Oracle 10.1.0.3.0
    Chuck

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    There should be an error number to go with that, e.g. ORA-00001. 10g error messages are listed here.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you are using Oracle, then you are using Rollback segments. You have not definned non-system rollback's yet.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    show parameter undo

    NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
    ------------------------------ ----------- --------------------------
    undo_management                string      AUTO
    undo_retention                 integer     10800
    undo_suppress_errors           boolean     FALSE
    undo_tablespace                string      UNDOTBS1 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    The UNDO tablespace replaces rollback segments in 10g. Our UNDO tablespace is set to grow to 10Gb, but is currently sized at 1Gb, with only 5% of that used.

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

    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.

    Should I be opening a TAR at this point?
    -cf

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    what do your init parameters say for:

    show parameter undo
    show parameter rollback_segments
    show parameter audit_trail

    selectsegment_name, status
    from dba_rollback_segs;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    SQL> show parameter undo

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string MANUAL
    undo_retention integer 900
    undo_tablespace string UNDOTBS1


    Could it be a problem that undo_management is set to MANUAL? We've been successfully inserting records until yesterday.

    -cf

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    undo_management = AUTO

    I think you will have to bounce the DB after you change this parameter
    in the init.ora/spfile
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    That was it. Strange, it showed that it was set to MANUAL sometime last Friday, although none of the DBAs claimed they were messing with it...
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •