DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Ora-01555

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    Ora-01555

    One of my query got failed with 01555. But I didn't see any reasons for this because ...

    undo table space is 180G
    undo retention is 2 hours

    and the above said query which is running from ETL frame work has a target commit interval for every 100000 rows.

    Thou source and target tables are more than 150M each and the whole query SLA is 19 hours still i didn't see any reason to tail with 01555 because the above undo & commit settings.

    I have attached the query for your reference and the insert query is just a plain insert.

    Please note that, this is the ONLY session running in the database during 01555.

    Oracle 10.2.0.4
    OS: HP
    ETL: Informatica

    Thanks,
    Attached Files Attached Files
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You don't understand how the 1555 error occurs in the first place. When a commit takes place, the undo segment for that transaction becomes eligible for re-use by another transaction (or even yours). It gets overwritten, boom, you lose the snapshot of what data was in the past. Self-inflicted wound. What is the value of committing every 100000 records when all you need is one commit for the entire transaction (or whatever is going on at that point)?

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Stecal,

    Thanks for the reply.

    I totally agree with you in terms of how 01555 occurs.

    Table A has 150 Mil rows.

    In my scenario, one query with 10 connections (using informatica level partitioning using MOD and ROW_NUM) selecting the data from table A and inserting in table B and committing for 100000 records (again this is also informatica level configurable parameter).

    I got 01555 on table A.

    I am sure that no other process is doing any DML on table A when my job is running.

    Could you please explain me what are the possible scenarios to get 01555 in this case?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    You can set undo_retention to a higher value (and be prepared for the increase in size for the undo tablespace). Even with that step, if you commit, you just marked that segment as being eligible for use by something/someone else. You just reduce the chance of the error, not eliminate it.

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks Stecal.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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