Re: sessions and locks on procedures

fabienne hadkova wrote:

> hello
>
> first, since I am fairly new to oracle, I'ld be glad for links to
> information, so as not to constently bother all of you with
> newbie-questions.
> now, my problem (excuse me if it turns out long, I don't know how to explain
> it fast):
> my company built an application for another company, including some stored
> procedures. the procedures need to compile at every start of the application
> (why this is would be too long to explain).
> we have had the problem many times that, on the side of our client, the
> start-skript for compiling the procedures and running them has been started
> simultaneously by several people, resulting in a lock and bringing the
> application to a time out.
> now, I am writing a script checking all sorts of environment-variables and
> settings which can cause problems, so I need a query which checks the locks
> and finds out who started compiling and when. The problem is that I don't
> know how to do it as a regular user, all I have tried needs sys or
> system-privileges, which we don't have. is there another way to query this?
> I hope I could explain my problem clearly enough and thanks in advance
> fabienne

I think what you need to do is beat some heads together for ever allowing a situation like this to exist in the first place.

Scripts, if I am understanding what you are doing, should only be run by one person and that is the DBA.

But one easy way is to create a semaphore. So, for example, the first thing the script does is see if some object exists in the database. If it does it exits immediately. If it doesn't it builds it. The script continues and the last bit of DDL drops the object.

But if you have a script doing this that needs to be run more than one time at installation something is horribly designed. You should never be running DDL creating objects on the fly in a production database.

Daniel Morgan Received on Wed Jun 05 2002 - 11:41:30 CDT