9 Replies Latest reply: Nov 9, 2012 2:28 AM by 846231 RSS

    Direct Path SQL Loader get error ORA-01555

    846231
      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
          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
            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
              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
                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
                  Please post
                  SELECT segment_name, status FROM dba_rollback_segs;
                  • 6. Re: Direct Path SQL Loader get error ORA-01555
                    846231
                    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
                      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
                        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
                          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