Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: ORA-01555: snapshot too old: rollback segment too small

    Setup: Oracle 9iDB on ??? (W2K I think)

    I am trying to select about a million records (dated during the 3rd quarter) from a view of 2 tables. At first I was trying something like

    select col1, col2, col3
    from (select col1, col2, col3 from view where date between 7/1/04 and 9/30/04)
    order by dbms_random.value;

    That failed so I then tried:
    create global temporary table table
    as select col1, col2, col3 from view where date between 7/1/04 and 9/30/04

    thinking that that might help (in hind sight that was a silly thought, please stop laughing now).

    The developer in me says give me a bigger rollback segment but I remember the disdain of past DBAs as they would say "You need a better query".

    My question is given the simplicity of the query is there much that can be done there?

    I am new to this customer, but it does not appear that there is an index on the date field (which is actually a number field). Would that help?

    Thanks for your ideas or references to further reading.
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    > I am new to this customer, but it does not appear that there is an index on the date field (which is actually a number field). Would that help?

    Maybe - if the proportion of rows matching your date criteria is small.

    Perhaps you should explain more about what you are doing here:
    1) Why are you randomly ordering the million records, which will take time?
    2) What are you doing with the records after you select them?
    3) How long does the query (alone) take?
    4) What does AUTOTRACE or TKPROF show?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why are you returning them in random order? This will cause no records to be returned until the entire query is completed. Are you inserting them into a table, otherwise the select is fairly worthless, and if you are, the order by is worthless. Is the date column in the base table indexed? Also your select should be

    select col1, col2, col3 from view where date between to_date('7/1/04','mm/dd/rr') and to_date(9/30/04,'mm/dd/rr')
    order by dbms_random.value;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Tony & Bill,

    The "date" column is indexed and the table was analyzed Monday. However the query still failed due to blown rollback.

    The requirement is to select 1000 randomly selected records that is why I am doing so. I negelected to add the "and rownum < 1001" clause in my example.

    I am running the query via Quest's Sql Navigator. When the result set is returned (if ever :-) ) I store the result in an Excel file.

    I have not run the query standalone. I am doing so now.

    I do not have permissions to do run tracing. I will look into getting them.

    Thanks, I will keep you posted.
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com
    do a keyword search on: ORA-01555

    Keep in mind the SELECT is NOT the cause of the error. It is the "victim" of changes made by "others".

    A SELECT, in and of itself, will never cause the ORA-01555 exception.

    Oracle GUARENTEES a read consistent view of the database from the time a SELECT starts.
    When Oracle can no longer provide the read consistent view, the ORA-01555 is raised.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •