9 Replies Latest reply on Jan 3, 2018 8:18 AM by AndrewSayer

    ORA-01555 error

    DjZg

      Hello,

       

      I have problem in few databases with this error (2 types). I searched already but i couldn't find what is causing this error.

      First type, in alert log:

       

      "ORA-01555 caused by SQL statement below (SQL ID: dkgv704h0jdxa, SCN: 0x0000.30b3aef6):

      table_4_d_1684d_0_0_0"

       

      First, i cannot find sql with this id in v$sql and there is no in Statspack tables. That means this sql is not executed?

      And what is "table_4_d_1684d_0_0_0"?

       

      Second type, alert log:

       

      ORA-01555 caused by SQL statement below (SQL ID: c1fuwad46gasq, SCN: 0x0000.77917a17):

      table_4_d_13145_0_0_0

      Errors in file /u01/app/oracle/diag/rdbms/***/***/trace/***_ora_8454.trc:

      ORA-01555: snapshot too old: rollback segment number 10 with name "???" too small

       

      Still cannot find this query "c1fuwad46gasq". How can i found this segment?

       

      Information (this are few databases):

       

      Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

      PL/SQL Release 11.2.0.4.0 - Production

      CORE    11.2.0.4.0    Production

      TNS for Linux: Version 11.2.0.4.0 - Production

      NLSRTL Version 11.2.0.4.0 - Production

       

      Database size - its not big, less then 50 GB

       

      Undo tablespace - max. 1 Gb used, 32 max.size

      undo_retention parameter - 900

       

      And advice?

      Thank you

        • 1. Re: ORA-01555 error
          Mustafa KALAYCI

          my first advise is to google it. this is probably one of most known error, also it is solutions.:

           

          https://www.google.de/search?q=ORA-01555&ie=utf-8&oe=utf-8&client=firefox-b-ab&gfe_rd=cr&dcr=0&ei=-BpGWqKGFaGE8Qf55Yb4BQ

           

          use automatic undo management, set your undo files autoextend on etc etc etc.

          • 2. Re: ORA-01555 error
            AndrewSayer

            DjZg wrote:

             

            Hello,

             

            I have problem in few databases with this error (2 types). I searched already but i couldn't find what is causing this error.

            First type, in alert log:

             

            "ORA-01555 caused by SQL statement below (SQL ID: dkgv704h0jdxa, SCN: 0x0000.30b3aef6):

            table_4_d_1684d_0_0_0"

             

            First, i cannot find sql with this id in v$sql and there is no in Statspack tables. That means this sql is not executed?

            And what is "table_4_d_1684d_0_0_0"?

             

            Second type, alert log:

             

            ORA-01555 caused by SQL statement below (SQL ID: c1fuwad46gasq, SCN: 0x0000.77917a17):

            table_4_d_13145_0_0_0

            Errors in file /u01/app/oracle/diag/rdbms/***/***/trace/***_ora_8454.trc:

            ORA-01555: snapshot too old: rollback segment number 10 with name "???" too small

             

            Still cannot find this query "c1fuwad46gasq". How can i found this segment?

             

            Information (this are few databases):

             

            Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

            PL/SQL Release 11.2.0.4.0 - Production

            CORE 11.2.0.4.0 Production

            TNS for Linux: Version 11.2.0.4.0 - Production

            NLSRTL Version 11.2.0.4.0 - Production

             

            Database size - its not big, less then 50 GB

             

            Undo tablespace - max. 1 Gb used, 32 max.size

            undo_retention parameter - 900

             

            And advice?

            Thank you

            table_4.. sounds like accessing a lob to me, have a read of https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3311300131016

             

            Consistency for lobs is not achieved the same way as normal data, so the ORA-1555 may have nothing to do with UNDO tablespace.

             

            What exactly is the process that raised these errors doing? How far back is it trying to look? What are you doing with the lobs in the mean time?

            1 person found this helpful
            • 3. Re: ORA-01555 error
              DjZg

              Thank you for your help.

              Problem is I don't know which process raised these errors. It's some query in my application, which I cannot find with these sql_id in alert log. Error is happening every few days.

              How can i find out which query or/and lob is the problem?

              • 4. Re: ORA-01555 error
                John Thorton

                can you enable SQL_TRACE  for the session that uses this table?

                • 5. Re: ORA-01555 error
                  Evgeni Gelfand

                  If you can't find the sql and not the tables (which is kind of weird for me) you can setup event 1555 to generate trace once the error is occurred, something like: alter system set events '1555 trace name errorstack level 12'

                  The trace file will provide you almost all information you may need for review.

                   

                  Evgeni

                  • 6. Re: ORA-01555 error
                    Mike Kutz

                    DjZg wrote:

                     

                    Thank you for your help.

                    Problem is I don't know which process raised these errors. It's some query in my application, which I cannot find with these sql_id in alert log. Error is happening every few days.

                    How can i find out which query or/and lob is the problem?

                    AndrewSayer "Snapshot too old" for lobs has a different error number.  I forgot the number but there was a post about it.

                     

                    TABLE_4_6_1684D_0_0_0

                    At first, I thought this was the temp table name format used in STAR-TRANSFORMATION queries.  Luckily, I was wrong. ( https://jonathanlewis.wordpress.com/2011/04/22/star-transformation/  )

                     

                    If you can't find this table via DBA_TABLES, then I suspect that this table is being created and dropped by an application or script.  Make sure you turn on and check AUDIT for CREATE TABLE.  This might help you find the culprit.

                     

                    From what I understand of ORA-1555, these are mostly caused by applications that loop over a SELECT statement, do a DML, and call commit every X rows.

                    Your nightmare situation : this is done at the application level, not within a Cursor FOR loop.  (the odd table name hints that this could be the case)

                     

                    My $0.02

                     

                    MK

                    • 7. Re: ORA-01555 error
                      AndrewSayer

                      Mike Kutz wrote:

                       

                      DjZg wrote:

                       

                      Thank you for your help.

                      Problem is I don't know which process raised these errors. It's some query in my application, which I cannot find with these sql_id in alert log. Error is happening every few days.

                      How can i find out which query or/and lob is the problem?

                      Andrew Sayer "Snapshot too old" for lobs has a different error number. I forgot the number but there was a post about it.

                       

                      TABLE_4_6_1684D_0_0_0

                      At first, I thought this was the temp table name format used in STAR-TRANSFORMATION queries. Luckily, I was wrong. ( https://jonathanlewis.wordpress.com/2011/04/22/star-transformation/ )

                       

                      If you can't find this table via DBA_TABLES, then I suspect that this table is being created and dropped by an application or script. Make sure you turn on and check AUDIT for CREATE TABLE. This might help you find the culprit.

                       

                      From what I understand of ORA-1555, these are mostly caused by applications that loop over a SELECT statement, do a DML, and call commit every X rows.

                      Your nightmare situation : this is done at the application level, not within a Cursor FOR loop. (the odd table name hints that this could be the case)

                       

                      My $0.02

                       

                      MK

                      I'm not sure if you have access (I'm not sure if I should have access tbh), but google found me ORA-1555 - Do you know how to resolve this issue?  which suggests a lob read could experience either the ORA-1555 or the ORA-22924.

                       

                      If I'm correct, then

                      select * from dba_objects where object_id = to_number('1684D','XXXXXXX');

                      Will return a table that has a lob column

                      1 person found this helpful
                      • 8. Re: ORA-01555 error
                        DjZg

                        Yes, you are correct Thank you.

                        Now I can investigate further

                        • 9. Re: ORA-01555 error
                          AndrewSayer

                          DjZg wrote:

                           

                          Yes, you are correct Thank you.

                          Now I can investigate further

                          Lobs can be interesting beasts, make sure you know if you’re using securefile lobs and all the storage characteristics. Jonathan Lewis has A LOT of write up on them and you’d probably benefit going through a few of his blog notes at least.

                           

                          Of course, the simple solution would be to stop modifying the lob so much or stop requiring historic  of the lob (could be down to long running statements or flashback query or grabbing the lob contents much later than the start of the query execution). The bandaid could be to allow more storage for the previous versions of the lob.

                           

                          If the error Is not common then it might be tempting to say that its fine. It might be likely that whatever process errored before will succeed on a second try.