Re: prevent sql invalidations after grants on objects

From: Yong Huang <>
Date: Tue, 9 Sep 2008 14:28:44 -0700 (PDT)
Message-ID: <>

> Date: Mon, 8 Sep 2008 20:40:18 -0700
> From: "Arul Ramachandran" <>
> Subject: prevent sql invalidations after grants on objects
> On an extremely busy 24x7 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


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       

Received on Tue Sep 09 2008 - 16:28:44 CDT

Original text of this message