ORA-01555: rollback segment number 9 with name "_SYSSMU9$" too smal
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-01555: rollback segment number 9 with name "_SYSSMU9$" too smal

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    ORA-01555: rollback segment number 9 with name "_SYSSMU9$" too smal

    Hello Everyone,

    [B]When:[/B]
    While exporting a 30GB table getting this error

    Environment:
    I have an UNDO segment of 10GB. Retention is 7200.

    (1) Segment Details
    SQL> select SEGMENT_NAME,TABLESPACE_NAME,SEGMENT_ID from DBA_ROLLBACK_SEGS;

    SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
    ------------------------------ ------------------------------ ----------
    SYSTEM SYSTEM 0
    _SYSSMU1$ UNDOTBS 1
    _SYSSMU2$ UNDOTBS 2
    _SYSSMU3$ UNDOTBS 3
    _SYSSMU4$ UNDOTBS 4
    _SYSSMU5$ UNDOTBS 5
    _SYSSMU6$ UNDOTBS 6
    _SYSSMU7$ UNDOTBS 7
    _SYSSMU8$ UNDOTBS 8
    _SYSSMU9$ UNDOTBS 9
    _SYSSMU10$ UNDOTBS 10

    SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
    ------------------------------ ------------------------------ ----------
    _SYSSMU11$ UNDOTBS 11
    _SYSSMU12$ UNDOTBS 12
    _SYSSMU13$ UNDOTBS 13
    _SYSSMU14$ UNDOTBS 14
    _SYSSMU15$ UNDOTBS 15
    _SYSSMU16$ UNDOTBS 16
    _SYSSMU17$ UNDOTBS 17

    18 rows selected.


    (2) USAGE

    (2) SQL> select tablespace_name,sum(bytes/1024/1024) from dba_segments where tablespace_name='UNDOTBS' group by tablespace_name;

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 118.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 118.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 118.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 118.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 113.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 111.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 122.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 122.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 114.992188

    SQL> /

    TABLESPACE_NAME SUM(BYTES/1024/1024)
    ------------------------------ --------------------
    UNDOTBS 114.992188


    Question

    Why I am getting such error even when my UNDO has enough space to handle the transactions? Anything I am missing here?
    sumit

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    post the code you ran to get it, are you fetching across a commit?

  3. #3
    Join Date
    Dec 2001
    Posts
    203
    exp userid=username/pass file=filename log=logfile_name tables=tablename compress=Y consistent=Y
    sumit

  4. #4
    Join Date
    Dec 2001
    Posts
    203
    ok, I got this from alter log

    ORA-01555 caused by SQL statement below (Query Duration=11645 sec, SCN: 0x06a4.db3568f0):
    Wed Apr 19 01:05:52 2006
    SELECT /*+NESTED_TABLE_GET_REFS+*/ "schema_name"."table_name".* FROM "schema_name"."table_name"

    Wed Apr 19 01:49:15 2006

    What I say:

    My Undo Rentention is 2 hrs (7200). The above query stays for 3.23 hrs. Segment usage was maximum around 140MB.

    What cause this error to terminate the export? And most importantly DOES Oracle use UNDO while EXPORTING and table segment :(
    sumit

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    of course it does, everything uses undo

    add direct=y to the export and change undo_retention to more than the value of the longest query

  6. #6
    Join Date
    Dec 2001
    Posts
    203
    if we look into the statement (the longest query) in alter file it is

    SELECT /*+NESTED_TABLE_GET_REFS+*/ "schema_name"."table_name".* FROM "schema_name"."table_name"

    In such case do u suggest me to set the retention upto 12hrs? Exp took 4hrs to comlete 9Gb+
    sumit

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    undo retention should be set to higher then the single longest running query. Set your undo tablespace size appropriately

Posting Permissions

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


Click Here to Expand Forum to Full Width