Re: how to disable Stored procedure

From: Ban Spam <ban-spam_at_operamail.com>
Date: Sat, 25 Jan 2003 02:08:03 GMT
Message-ID: <Xns930DB8C4DE0FBSunnySD@68.6.19.6>

"Rangesh Kona" <rkona_at_cadence.com> wrote in news:3e315b3a$1_at_news.cadence.com:

> Actually I am trying to take a backup of all stored procedures but
> some of them are  in invalid state and some are in valid state.
> I want to take a backup of these stored procedures as is and restore
> it after I do some DBA work.
> Thanks,
> Rangesh
> 
> 
> "Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> wrote in message
> news:4f7d504c.0301240209.514942c2_at_posting.google.com...

>> Rangesh,
>> I don't think it can be done easily.
>> If procedures are accessing tables in another schema,
>> then revoking access to these tables should do.
>> But if they are accessing tables in the same schema
>> you have to find something they depend on and
>> somehow damage these objects...
>>
>> In any case, Oracle tries to re-compile invalid objects
>> "on the fly", so even if you can find a way to invalidate
>> all objects in a schema, an attempt to use any of them
>> will automatically lead to re-compillation. If all objects
>> a procedure depends on have not been changed and all privileges
>> are still in place, the procedure will be re-compilled.
>>
>> What exactly are you trying to achieve?

> 
> 
> 

One thing I've done in the past is to make a "dataless" schema export (ROWS=NO INDEXES=NO). I still don't understand what you are doing or thinking you'll accomplish.
How/why having "inavlid/disabled" stored procedures benefit you while you "do some DBA work"?

What problem are you REALLY trying to solve? Received on Fri Jan 24 2003 - 20:08:03 CST