Re: prevent sql invalidations after grants on objects

> Date: Mon, 8 Sep 2008 20:40:18 -0700
> From: "Arul Ramachandran" <contactarul_at_gmail.com>
> Subject: prevent sql invalidations after grants on objects
>
> On an extremely busy 24x7 10.2.0.3 db, I need to grant privileges on several
> tables to a few roles. This would invalidate the sqls (we are talking
> 100s/1000s of sqls) in the sql area that refer to the tables leading to a
> hard parse storm.
> Is there a documented/undocumented way to prevent the invalidations and the
> resulting reparses?
>
> Thanks in advance.
>
> --
> Arul

Arul,

There's no documented or undocumented way to do this. How about you temporarily create a view as select * from the table and grant select on the view to the roles? That won't invalidate any cursor using the table. You may need to add or play with synonyms to avoid changing code.

After the peak usage period, run your real grant and drop the view.

Yong Huang       

--
http://www.freelists.org/webpage/oracle-l