This discussion is archived
9 Replies Latest reply: Nov 9, 2012 12:28 AM by 846231 RSS

Direct Path SQL Loader get error ORA-01555

846231 Newbie
Currently Being Moderated
Hi All,

11.2.0.1 DB

Has anyone here used sqlloader direct path option?
Is there special setting I need for the Undo TS?

I am testing my sqlloader direct path but I got error : ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small

I am only testing 100 records and I shutdown the database. And I am only the one using it. :(

Does dropping the Undo TS and creating new one help resolve my issue?

Thanks a lot,

Kinz

Edited by: KinsaKaUy? on 07-Nov-2012 03:33
  • 1. Re: Direct Path SQL Loader get error ORA-01555
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Please check MOS Notes about this error :
    ORA 01555 "snapshot too old: rollback segment number %s with name \"%s\" too small" [ID 1555.1]
    ORA-604 & ORA-1555 Rollback Segment 0 With Name "System" Too small [ID 862469.1]
  • 2. Re: Direct Path SQL Loader get error ORA-01555
    846231 Newbie
    Currently Being Moderated
    Hi Osama,

    The 1st note said I need to apply a patch for 11.2.0.1? I can not apply a patch now cause I need to pass to "system change" committee. Is there another workaround?

    The 2nd note is for 10g only so not applicable to me :(


    Is it okay to drop and recreate my UNDO TS?

    Thanks
  • 3. Re: Direct Path SQL Loader get error ORA-01555
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi Osama,

    The 1st note said I need to apply a patch for 11.2.0.1? I can not apply a patch now cause I need to pass to "system change" committee. Is there another workaround?

    The 2nd note is for 10g only so not applicable to me :(


    Is it okay to drop and recreate my UNDO TS?

    Thanks
    its not the perfect solution Check One of these solution
    - UNDO_RETENTION Value is Good .
    -Lot of commit can cause this error
    -
  • 4. Re: Direct Path SQL Loader get error ORA-01555
    846231 Newbie
    Currently Being Moderated
    My Undo is 2Gb..... and even 1 row only I can not load it to my table. I am the only one using the database huhuhhuh :( What is happening here.


    Record 1: Rejected - Error on table EMPLOYEE.
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_1770146741$" too small
  • 5. Re: Direct Path SQL Loader get error ORA-01555
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Please post
    SELECT segment_name, status FROM dba_rollback_segs;
  • 6. Re: Direct Path SQL Loader get error ORA-01555
    846231 Newbie
    Currently Being Moderated
    I drop my UNDOTS1 and recreate it to resolve if there are framentation in the TS itself , but to no avail, the same error persist :(


    Record 1: Rejected - Error on table EMPLOYEE.
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01555: snapshot too old: rollback segment number with name "" too small
    SYSTEM                         ONLINE
    _SYSSMU87_2780657351$          ONLINE
    _SYSSMU88_120427686$           ONLINE
    _SYSSMU89_1588874193$          ONLINE
    _SYSSMU90_2571046414$          ONLINE
    _SYSSMU91_1803654754$          ONLINE
    _SYSSMU92_2477693864$          ONLINE
    _SYSSMU93_1908993412$          ONLINE
    _SYSSMU94_985867735$           ONLINE
    _SYSSMU95_4165893730$          ONLINE
    _SYSSMU96_1502488804$          ONLINE
    _SYSSMU97_3533816217$          ONLINE
    _SYSSMU98_3954380786$          ONLINE
    _SYSSMU99_1314687851$          ONLINE
    _SYSSMU100_3542148152$         ONLINE
    _SYSSMU101_1249002234$         ONLINE
    _SYSSMU102_1520886654$         ONLINE
    _SYSSMU103_2785416951$         ONLINE
    _SYSSMU104_1530388055$         ONLINE
    _SYSSMU105_1995830672$         ONLINE
    _SYSSMU106_4584952$            ONLINE
    _SYSSMU107_1177768351$         ONLINE
    _SYSSMU108_3896986945$         ONLINE
    _SYSSMU109_2576315174$         ONLINE
    _SYSSMU110_2786589320$         ONLINE
    _SYSSMU111_1195961439$         ONLINE
    _SYSSMU112_2612035115$         ONLINE
    _SYSSMU113_1697198479$         ONLINE
    _SYSSMU114_3939726644$         ONLINE
    _SYSSMU115_467875145$          ONLINE
    _SYSSMU116_2826382876$         ONLINE
    _SYSSMU117_3650068864$         ONLINE
    _SYSSMU118_92260707$           ONLINE
    _SYSSMU119_2322108460$         ONLINE
    _SYSSMU120_2583709550$         ONLINE
    _SYSSMU121_1279604730$         ONLINE
    _SYSSMU122_2128414833$         ONLINE
    _SYSSMU123_1365970622$         ONLINE
    _SYSSMU124_1855929876$         ONLINE
    _SYSSMU125_1511578664$         ONLINE
    _SYSSMU126_3219352797$         ONLINE
    _SYSSMU127_2412556110$         ONLINE
    _SYSSMU128_2102547636$         ONLINE
    _SYSSMU129_447164998$          ONLINE
    _SYSSMU130_3851265156$         ONLINE
    _SYSSMU131_3046352603$         ONLINE
    _SYSSMU132_2987406815$         ONLINE
    _SYSSMU133_983809304$          ONLINE
    _SYSSMU134_928979873$          ONLINE
    _SYSSMU135_3248183819$         ONLINE
    _SYSSMU136_811112856$          ONLINE
    _SYSSMU137_1958351427$         ONLINE
    _SYSSMU138_2222543$            ONLINE
    _SYSSMU139_3962620689$         ONLINE
    _SYSSMU140_2711665463$         ONLINE
    _SYSSMU141_3724825495$         ONLINE
    _SYSSMU142_1818253355$         ONLINE
    _SYSSMU143_1370485269$         ONLINE
    _SYSSMU144_2442636386$         ONLINE
    
    59 rows selected.
    
    SQL>
    Is there something missing in my sqlloader parameter? but if I use "ordinary" path it will load successfully. But not with "direct" path :( Any ideas please
  • 7. Re: Direct Path SQL Loader get error ORA-01555
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Read this
    http://myoracleguide.com/msa/snaphot%20too%20old%20in%20VLDSa.htm

    and post sqlloader script that you are using
  • 8. Re: Direct Path SQL Loader get error ORA-01555
    846231 Newbie
    Currently Being Moderated
    The Note is not applicable to me because I do not have any contentions or long queries, as I am alone and no other jobs running. :(


    sqlldr script:

    c:\> load1.bat csv_file control_files log_dir scott/tiger@dev 20 60


    l

    Edited by: KinsaKaUy? on 07-Nov-2012 19:02
  • 9. Re: Direct Path SQL Loader get error ORA-01555
    846231 Newbie
    Currently Being Moderated
    Hi All,

    Issue resolved :)

    It is a misleading error. The actual error is the 32G limit for TS size for a 32 bit windows database server.


    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points