Re: sessions and locks on procedures

fabienne hadkova wrote:

> Hello Daniel and thanks a lot for your reply.
>
> > 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.
> Well, I agree fully with you, only this is the situation I have to deal
> with. Unfortunatly...
>
> > Scripts, if I am understanding what you are doing, should only be run by
> one
> > person and that is the DBA.
> I am talking about a ksh-script (starting a sqlplus session). Basically, the
> people running the application are operators who seem to have very little
> technical understanding. Their having to deal with an application started on
> the command line on a AIX without any 'comfort' is a bit too much, this
> accounts for all kinds of mistakes. (Unfortunatly their bosses don't want to
> pay for a user-friendly GUI). So the script I write is basically a check on
> all the things that have gone wrong sofar (db and more), it writes all the
> results in a log file, which the operators can then send us so that we can
> interpretate it and help by phone or email (sofar, I had to go there each
> time, a 4 hour train-ride). This script does nothing intrusive, just 'read'
> and 'write' to a file.
>
> > 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.
> This sounds good but the user running the shell-script has no rights
> whatsoever, and the oracle-user I can use has only the ressource and connect
> roles. He can do all that in his own schema. The procedures belong to
> another user. This is why I wanted to find out how to 'see' sessions for all
> users (not beeing system or sys).
>
> > 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.
> I agree with you but the company we do this for wants it this way and I have
> been trying to explain this to them with no avail for almost a year. The
> user compiling and recompiling each time seems to be a holly cow and they
> won't discuss. Of course, it could be that they have other applications
> running for this user.
> Well, I think that it is a pretty 'stupid' problem. I thank you for your
> help.
> Fabienne

I'm not sure that anything should be written to the error log other than perhaps comments about bad management making a mess of a technology project. But here are two thoughts.

1. Use an externally authenticated account, for example OPS$, so that you can have a secure read-only account available. And give that account SELECT on V_$SESSION which will allow that account to see who else is connected.
2. Write the semaphore as a small file in the operating system.

Of course there is the third possibility ...

Find a job where management cares about doing a good job.

Daniel Morgan Received on Sun Jun 09 2002 - 00:58:57 CDT