How to place command line statements into a sql script?

[Gino Castoldi_2]

Hi,

Oracle 8.1.7.4, HP-UX 11.0

I am a newbie to Oracle so please forgive my
basic question.
I have a six lines of svrmgrl statements that I would like to convert into a sql script.
We want to run this from a cron job.

SVRMGR> connect internal
Connected.
SVRMGR> select name,
2> trunc(sgasize/(1024*1024))"Allocated (M)",
3> trunc(bytes/1024) "Free (K)",
4> round(bytes/sgasize*100,2) "% Free"
5> from (select sum(bytes) sgasize from sys.v_$sgastat) s,sys.v_$sgastat f
6> where f.name = 'free memory';

10 points to any good answer.
TIA, Gino

[Steven E. Protter · Accepted solution]

svrmgr << EOF

connect internal
trunc(sgasize/(1024*1024))"Allocated (M)",
trunc(bytes/1024) "Free (K)",
round(bytes/sgasize*100,2) "% Free"
from (select sum(bytes) sgasize from sys.v_$sgastat) s,sys.v_$sgastat f
where f.name = 'free memory';

EOF

some adjustment may be required.

SEP

[Yogeeraj_1]

hi,

If you are doing some sort of monitoring, you may also wish to schedule STATSPACK snaps at reqular intervals. Then run the spreport.sql script to generate the desired reports.

regards
Yogeeraj

[Hein van den Heuvel]

Inlining the script as SEP suggest is fine.

Yogeeraj is quite correct in observing that if you choose to gather Oracle stats similar to your example then you may want to aim higher and get it all: Statspack.
Mind you, generating the snaps (automatically) is trivial. Generating the actual reports a little more tricky due to the interactive nature of spreport and the variable snap numbers.

Anyway... my personal prefence in using scripted sql reports is to stick them into a .sql file and feed that to sqlplus. The main advantages I see for this is
- able to use same script interactively if/when desired
- easier testing/debugging/expanding of script through interactve sqlplus session
- easier to change later, leaving job in place just making it execute better code.

So the job you would actually run might be:
su - oracle -c sqlplus -s "x/y@z" @daily

or

cat daily.sql | sqlplus ....

or sqlplus .... < daily.sql > daily.log

hth,
Hein.

[Indira Aramandla]

Hi Gino,

If you wanted to run as a crn job then you just save the above line as a script with the line at the top and bottom as Steven specified.

svrmgrl <
connect internal
@
exit
EOF

And as Yogeeraj mentioned, for monitoring, if you are trying to generate the Statspack
Performance Report you need to run spreport.sql($OH/rdbms/admin) and choose the Begin and End snapshot ID's, between which the report will be generated.

Note: But, when the script spreport.sql is run, the column SnapId is displayed as
#####. So change the column formatting options for the snap_id column in the
spreport.sql script to a higher value.

Indira A

[Steven E. Protter]

If you pursue my answer, don't put a lot of effort into doing a lot of scripts.

Oracle yanked svrmgr with Oracle 9i. You have to use sqlplus and they yanked out sqlplus internal with 9i as well.

Still the technique works just fine.

I for one would not mind seeing an example or elaboration on the second post to this thread.

Good Luck,

Steve