Set Transaction and Analyze Table Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Set Transaction and Analyze Table

Hybrid View

  1. #1
    Join Date
    Dec 1999
    Cincinnati, Ohio USA
    Working with Oracle and am getting a snapshot to old ora-1555 error.

    I tried set transaction use rollback segment, but it does not appear as if analyze uses the transactions rollback segment as I specified.

    here is my output

    23:53:58 SQL> set transaction use rollback segment rollback_statistics;

    Transaction set.

    23:53:58 SQL> analyze table pmadmin.PM_CLK_HIS compute statistics;
    analyze table pmadmin.PM_CLK_HIS compute statistics
    ERROR at line 1:
    ORA-01555: snapshot too old: rollback segment number 10 with name "RBS09" too small

    Any help or ideas on work arounds?

  2. #2
    Join Date
    Aug 2001
    ORA-1555 errors are due to inconsistent views of a table, not to space. an analyze command is a DDL statement which doesn't use rbs for writing.
    OCP DBA 8i
    When in doubt, pick 'C'.

  3. #3
    Join Date
    May 2001
    Maryland, USA
    1. Check if the table you are analyzing is being used concurrently. ALSO check for Rollback Segment corruption.

    2. Check whether the rollback segment u have set is ONLINE.

    ORA-1555 happens due to many reasons and I believe rollback segment size is definetly one of them. If there are insufficient small rollback segments and a long running transaction is being executed, this usually does pops up.

    Please do let us know what was the case with you.

    -- Dilip

  4. #4
    Join Date
    Apr 2002
    The analyze statement probably ran while the table was being inserted/updated/deleted by another transaction. When this transaction was done/commited, the rbs was released and reused before the analyze command was done.

    The error occured when the analyze command tried to read from the reused rbs .

    If this is the case, the set transaction command you set for the analyze transaction is no used.

    You should
    - run the analyze when there is no update/insert/delete on the table.
    - try to create more rbs or increase the size of rbs to reduce the chance that the rbs is reused before the analyze finish.


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