Re: Recompile schema

I guess the other question that hasn't been asked yet is: "What else is happening on the database while you do the recompile?" If other transactions are hitting the rollback sections you are going to hit the snapshot too old problem quicker.

This just re-inforces the earlier advice but *could* mean that an alternative is to choose a time when there is no other activity on the instance (n.b. instance, not schema).

Len

>What "snapshot too old" means is that the rbs that opened a cursor is being
>overwritten. Not that it is too small.
>
>The problem can usually be fixed by adding more segments.
>
>Every time you start a new transaction, you switch Rollback segments.
>Oracle opens a cursor in the system tablespace against the objects tables to
>get the names of all of the procedures that might need to be re-compiled.
>Every time one successfully compiles, it moves to a new rbs, implicit
>commit. If you contact Oracle about the problem, they will usually tell you
>to change your code. I would say you want to contact them and ask them to
>fix their code.
>
>The reason why you are writing to to the rbs is that everytime one
>successfully compiles, it must update the table that holds the objects, to
>change it from invalid to valid.
>
>The concise version is: Open cursor against object table. Recompile , and
>switch rbs, (also origional cursor area is marked as overwriteable due to a
>commit being issued). You now stand the chance that changes will overwrite
>your origional cursor consistent read area... You do and get the snapshot
>too old error.
>
>the very concise version: add more rollback segments, it most likely won't
>work, but try. If it doesn't work you are SOL. Call Oracle Support.
>
>Good luck,
>
>--
>Bob Fazio
>Remove no.spam from my email to reply
>
>
>Richard Platt <rick.platt_at_btinternet.com> wrote in message
>news:7t7f8m$bo8$1_at_plutonium.btinternet.com...
>> Hi
>>
>> I have been using the dbms_utility.recompile_schema procedure, but keep
>> getting errors about 'snapshot too old' in one of my rollback segments,
>even
>> tough I set transaction at the start of the script. (Why is it using
>> rollback anyway ?)
>>
>> Have anybody had this problem, what are the workrounds ?
>>
>> Any help appreciated.
>>
>> --
>> Regards Richard Platt
>> rick.platt_at_btinternet.com
>>
>>
>
>
Received on Sat Oct 09 1999 - 06:10:34 CDT