Re: ORA-4031

On Sun, 15 Aug 2004 20:21:50 -0400, DK wrote:

> Questions;
>
> 1. The dba on duty said that he could not get into the database. Will a
> 4031 cause a database to lock up?

Not for a user with SYSDBA privilege. The DBA on duty probably has that privilege. Nothing should prevent user from getting into the database. Shared pool shortage will prevent users from logging in if there is after logon database trigger of fine grain access control, based on sys_context, because user will not be able to execute the PL/SQL procedure necessary to log in. Log in trigger failure will NOT prevent user with administer database trigger privilege from logging in. Even if your DBA on duty doesn't have SYSDBA privilege, he most certainly has administer database trigger privilege, which would allow him to log in seamlessly. I believe that ORA-4031 is just an indicator of some other problem. Looking into the alert log file, accompanying trace files and opening level 1 TAR (database down) would be the right way to go.

> 2. We run an analyze nightly. I'm I buying anything by flushing the shared
> pool?

Nothing really. By running analyze, you are changing statistics and invalidating all SQL in the database. In other words, you're doing a sort of flush one way or another.

-- 
A city is a large community where people are lonesome together.