sqlplus seems the the easiest option. It tends to be installed with
Oracle everywhere I have worked.
Perl modules - considered it, but these would need more installation
and packaging and testing etc. This is just a simple script for a
couple of queries, and I don't want to have to arrange for Perl
modules to magically be present.
It would be nice if Oracle shipped with some Perl modules.

Anyway - thanks everyone for the answers. Was just checking I hadn't
missed something obvious really..

Neil:

# Yes, I am doing it that way now (building a script file, then
running it).

Hey nice to see that you are now using a 20/25 year old Oracle
technique!

I asked earlier ... can you explain what it is exactly you are trying
to get done in Oracle land? Where are you going or why?

In my environment I do have a fair amount of custom monitoring and
scripting to accompany and double checking some ( judiciously
selected ) OEM monitoring capabilities. So do lots of other people.
Lots of capable 3rd party tools also from vendors like Quest etc.

This is just some simple scheduled Oracle monitoring script to check
it is in a generally accessible and healthy state.
This is some extra confidence check to compliment OEM.
If this has been done already please feel free to share your script.

Neil:

# This is just some simple scheduled Oracle monitoring script to check
it is in a generally accessible and healthy state.

Dude this has been done and redone so many times in the last 25 years
that unless you know exactly what you are doing and why you are doing
it ... using something pre-written and off the shelf will at least
initially probably be a whole lot better than what you are currently
doing.

Not willing to share my specific scripts ( work rule ) but four
specific suggestions I would toss out for consideration.

1) Have some kind of file based switch that will turn on and off
whether the script should run. Like look for if a file exists and it
does exist then maybe it is ok to continue.

In other words an easy way to turn off your monitoring script(s) from
executing is to create and/or remove a file.

2) Have the script then look for a specific process to check if the
database instances is really up ( like look for the instance specific
smon process ).

No point in trying to execute the sqlplus part if the database is up.

3) When your script executes spool out the results into a file and
develop some other logic that validates the output and decides if
things are ok or problematic. I tend to write some very specific
parsing scripts that examine the lines of output.

For example after driving an rman disk backup I have a validation
script that looks at all the output and decides if every single
different multiple step worked successfully or not.

4) Decide on some specific rules for what the email subject lines
should go out with ( ERROR on $hostname for reason blah blah blah
WARNING on ... SUCCESS on ... FAILURE on ).

Initially email out all the good results as well as all the bad ones.
After a while when you are sure everything is peachy then you "might"
decide to rip some of the stuff out.

All in all ... it might be a whole lot easier for you to either find
some good prewritten stuff ( google things like "oracle health check
script" maybe beware of the references to the site run by you know
who ) or pay someone to do some remote monitoring and get them to
deploy a solution for you.

Another possible option is Toad and the built in health check scripts
that are available. Think there are a whole bunch of ways to turn on
and off various portions of that checking.

Not to be rude but if you are struggling just to be able to in a
script issue sqlplus commands against v$database it seems like you
have a pretty steep learning curve in your immediate future.

It is very unlikely that scripts written 25 years ago will work now.
There wasn't even Perl then!
Off the shelf code won't cater for the non-Oracle parts of the scripts
I write - they do a whole lot more than just Oracle-related stuff.


Errr .. that's what I'm doing. This is the internet right here :-)

I am not struggling.
This thread was really just a simple question about the $ signs.
Noone has presented any good reason why they are there (they've been
there for 25 years - perhaps that was long enough to fix this design
no-no?)
I know shell is not ideal for Oracle programming in general - but you
do have to consider the bigger picture beyond Oracle.
Perhaps I do know what I am doing and I was just asking a simple
question..

On May 18, 1:00*am, neilsolent <n... (AT) solenttechnology (DOT) co.uk> wrote:
Much as I'd like to think not, usenet ain't the whole intertube.
Perhaps if you told exactly what you are trying to accomplish, we
could suggest or direct.

Oracle came out of the DEC world, where using a dollar sign was
considered a mark of he-man internals guru-dom. On the pdp machines,
$ was the identifier of the system account [1,2], on VMS machines it
signified system objects in an object name. Rather than a design no-
no that should have been fixed, it is a backward compatibility
convention that has no reason to change. Oracle was written in C to
be compatible across platforms, though real he-men programmed in
BLISS, the language of the OS. Meanwhile, unix was written in C, and
is really where you have problems with shells and other pathetically
eclectic languages doing strange stuff with with special characters
screwed up substantial fractions of a century ago, not to mention the
silliness of training generations of CS students to do application
programming in C. The blame isn't with Oracle here.

I use it all the time. I ought to use perl more, but I've been saying
that for decades too. Then again, perl code coming out of Oracle
isn't always the best code either. I've made the same complaint about
the dollar signs, but there are some things where you just have to
deal with "that's the way it works." Don't get me started on friggin'
csv files and quote characters.

John answered your simple question, but I have to wonder what you've
been doing that you aren't familiar with these common interactions.
As Sybrand noted, there may be a userenv way. John was wrong (or
joking) about Oracle versions though. Everyone knows there was no
Version 1, 'cause first versions always suck.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...rged-held-227/

Joel:

#*John was wrong (or joking) about Oracle versions though.

Think it was the mucho mocha one two guru that said something about
version 1 actually.

I have heard Larry make the V 1 joke at OOW at least 3 times. It was
funny the first time ... not so much after that.

#*Everyone knows there was no Version 1, 'cause first versions always
suck.

The punchline was more along the lines that "nobody will purchase V 1
software so it came out as V 2 " ...

I did - and I got the answer I wanted..

Oracle can choose to name their own tables as they wish.
If it was my choice I would stick to A-Z0-9_-

Let me fill you in.
What I have been doing is writing scripts (and a whole load of C++
that calls them) that caters for monitoring of Windows, Solaris,
Linux, MS SQL Server, Veritas Cluster Server, WMI, SNMP, HP Hardware,
VMWare, Active Directory, ... etc etc ... and Oracle.
So I have experience with a huge variety of interactions. This was
just a simple question to see if I could simplify writing of the
script for Oracle.
I am not an expert in Oracle, or really any of the technologies on
this list. I have no time to be an expert in any of these, and I don't
need to be.
What is more important for me is that the scripts follow a common
standard - very specific STDOUT, STDERR and return code, respect a
clearly documented set of environment variables, etc etc.

While I'm here. It's also annoying that Oracle doesn't let root have
access to the database. I see references to some security issue with
that all over the net, when this seems to be nonsense. Root has access
to wipe the database clean, or su to any user anyway.
Initially I was trying to run the script as root (like all my other
scripts) but gave up on this too as I think root has to have dba as
it's primary group to access via "/ AS SYSDBA".

I bet I've really stirred you all up now :-) ........

Neil:

# I am not an expert in Oracle, or really any of the technologies on
this list. I have no time to be an expert in any of these, and I don't
need to be.

Know about how and when to escape special characters in unix/linux
environments does not exactly come close to expert level. Somewhere
along the lines "geez is there anything special about the dollar sign
in a unix/linux script that I should be aware of?" ...

If one is indeed in charge of writing and deploying script based
monitoring solutions across so many different platforms and software
architectures some basic scripting knowledge and competency is on the
nice to have list.

# What is more important for me is that the scripts follow a common
standard - very specific STDOUT, STDERR and return code, respect a
clearly documented set of environment variables, etc etc.

One way to avoid "most" problems in querying oracle internal views/
tables and ( especially ) some of the stranger x and v etc views is to
put your code inside a sql file and executing that sql file.

No reason that you cannot use that technique probably along with
whatever other "common standards" that you are working from.

On the other hand if you are really as handy at C++ as you say then
you can also go directly at Oracle databases instead of driving file
based monitoring solutions to and from the database instances.

This is getting really silly.
Obviously I know how and when to escape special characters.
Just because you can escape them, this doesn't mean it was a good
idea to go and use them in table names.
Which bit are you not understanding exactly? I thought I had made this
clear.

Which I have, thanks very much. As I have already explained, I don't
know the intracacies of every possible application I want to monitor.
Hence the post.


Yes that has already been said and done earlier in the thread. Do keep
up.
And by the way creating files can present its own problems, if you
care about robust scripts.


Well you can. But this is code that a customer is very likely to want
to customise. So a script is the best solution.