9 Replies Latest reply: Jun 20, 2008 6:03 PM by jgarry RSS

    01555 error in alert log file

    607104
      Hi,

      Ora-01555 is written to alert log 7 times with same SQL ID but different duration and different SCN. I was wondering is this a single statement or 7 statements for which it returned 7 times.

      ORA-01555 caused by SQL statement below (SQL ID: b30mttd1aty3j, Query Duration=7838 sec, SCN: 0x0007.2539135b):
      Thu Jun 19 20:18:55 2008
      UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1
      Thu Jun 19 20:18:55 2008
      ORA-01555 caused by SQL statement below (SQL ID: b30mttd1aty3j, Query Duration=7686 sec, SCN: 0x0007.2539485a):
      Thu Jun 19 20:18:55 2008
      UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1
      Thu Jun 19 20:18:55 2008
      ORA-01555 caused by SQL statement below (SQL ID: b30mttd1aty3j, Query Duration=7331 sec, SCN: 0x0007.2539f856):
      Thu Jun 19 20:18:55 2008
      UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1
      Thu Jun 19 20:18:55 2008
      ORA-01555 caused by SQL statement below (SQL ID: b30mttd1aty3j, Query Duration=6515 sec, SCN: 0x0007.253b96fb):
      Thu Jun 19 20:18:55 2008
      UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1
      Thu Jun 19 20:18:55 2008
      ORA-01555 caused by SQL statement below (SQL ID: b30mttd1aty3j, Query Duration=5187 sec, SCN: 0x0007.253e0e9c):
      Thu Jun 19 20:18:55 2008
      UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1
      Thu Jun 19 20:18:56 2008
      ORA-01555 caused by SQL statement below (SQL ID: b30mttd1aty3j, Query Duration=4526 sec, SCN: 0x0007.253ef3e3):
      Thu Jun 19 20:18:56 2008
      UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1
      Thu Jun 19 20:18:56 2008
      ORA-01555 caused by SQL statement below (SQL ID: b30mttd1aty3j, Query Duration=3906 sec, SCN: 0x0007.253f97f2):
      Thu Jun 19 20:18:56 2008
      UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1


      Thanks.
        • 1. Re: 01555 error in alert log file
          Madrid
          This ORA-01555 means there is not enough space at the undo (or rollback? you didn't specify your DB version) segments to build consistent images in this environment. If you are on 9iR1, 9iR2, 10gR1 or 10gR2, or 11gR1, (just statistically guessing the release) then take a look at the undo advisor to find out how much undo_retention time you should configure in your undo mechanism.

          You can see from the kind of queries that exhausted the limit, those queries have been running for more than 7000 sec. (about 2 hrs). What's the current value of UNDO_RETENTION?


          ~ Madrid
          http://hrivera99.blogspot.com/
          • 2. Re: 01555 error in alert log file
            646155
            if u get that error.. it seems that there is no space in undo use the undo advisor to resize the undo table space...
            • 3. Re: 01555 error in alert log file
              607104
              Database is 10.1.0.5 on windows 2000. Now the undo_retention is 1800 secs.

              My question is how many queries were running? because for all all queries it is same SQLID

              Thanks
              • 4. Re: 01555 error in alert log file
                607104
                This is the statement which was shown in alertlog:

                UPDATE CONTAINER_ITEMS CI SET CI.LOAD_FLAG = PKG_CONTAINERSTANDARD.LOADFLAG(CI.LAST_UPDATED_TS, CI.ITEM_DATA_STATE, DECODE(CI.PK_ITEM, NULL, 0, 0, 0, DECODE(CI.SERIES_ID, NULL, 0, 0, 0, 1)), :B3 ), CI.VIEW_CLASS = DECODE(:B2 , '', CI.VIEW_CLASS, :B2 ) WHERE PK_CONTAINER = :B1

                How to know the values of the bind variables. Are there any views to know?

                Thanks
                • 5. Re: 01555 error in alert log file
                  153119
                  All datadictionary views are described in the SQL reference manual for your unknown version. You could just search for 'bind' in that manual.
                  The content of that manual also existst in the DICT table,
                  select * from dict where table_name like '%BIND%'
                  would have told your the answer.

                  Then again, this is a question which has already been asked several times, so you could also have use the 'Search Forum' box in the upper hand corner of the page, so as to avoid further 'noise' in this forum.
                  But alas, you belong to the majority of users, who never does anything to resolve their own problems, expecting some volunteer will help them out for free.

                  --
                  Sybrand Bakker
                  Senior Oracle DBA
                  • 6. Re: 01555 error in alert log file
                    607104
                    Hi Sybrand,

                    I will do search on web and try to solve problems. If I cannot get the result from web then I will post. May be sometimes I will post in flow without researching. That is not intentional but out of curiosity.

                    Don't get me wrong.

                    Thanks.
                    • 7. Re: 01555 error in alert log file
                      607104
                      Hi,

                      A sql statement uses 4 bind variable's. I used the below statement to get the sql_id:

                      select sql_id from v$sql where hash_value = '3456164252';

                      . Using the sql id if I query:

                      select name, datatype_string, value_string
                      from v$sql_bind_capture
                      where sql_id='awgatvb701scw'

                      It is only showing 3 bind variables and value of only 1 bind variable out of those 3.

                      Can you please tell whats wrong in this.

                      Thanks
                      • 8. Re: 01555 error in alert log file
                        Madrid
                        Take a look at the transactional activity at the base tables referred in the queries. You may will have to increase the value of the undo retention parameter and probably defer launching the queries at a later time when there is not too much transactional activity in the table, so consistent images are not generated this big. A SQL tuning task is highly advised at this time so the query doesn't require too much resources and you avoid, in case you find them, meaningful full table scans.



                        ~ Madrid

                        http://hrivera99.blogspot.com/
                        • 9. Re: 01555 error in alert log file
                          jgarry
                          Database is 10.1.0.5 on windows 2000. Now the
                          undo_retention is 1800 secs.

                          My question is how many queries were running? because
                          for all all queries it is same SQLID

                          Thanks
                          You use bind variables so identical queries from different sessions don't have to be reparsed, as hard parsing a unique query is expensive. You can see they have different System Change Numbers.

                          Is it possible they all erred about the same time because they all started about the same time?

                          Go to asktom.oracle.com and search on ora-1555 for some clarifying information.

                          It is entirely possible that only one other statement, not listed, was the cause of this. Then all the queries that had the problem were having to go back to undo to get a read consistent version of the data, and that part of undo finally got reused by some other unrelated statement. The real problem statement could have come from some unknown time before all these statements started. Making the undo much larger may eliminate the problem, or you may have to eliminate all sessions older than some arbitrary amount, if you have an app or users that tend to leave sessions open. Some apps will do this if users leave clients by X'ing out a window, rather than leaving cleanly. Oracle patiently keeps the undo as long as it can.

                          Also see Retention Guarantee in the docs.