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

Thread: Set Transaction and Analyze Table

Hybrid View

  1. #1
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    Working with Oracle 8.1.7.3 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?
    Doug

  2. #2
    Join Date
    Aug 2001
    Posts
    184
    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
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  3. #3
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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
    Posts
    9
    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.
    or
    - try to create more rbs or increase the size of rbs to reduce the chance that the rbs is reused before the analyze finish.

    david

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