Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

How to place command line statements into a sql script?

SOLVED
Go to solution
Gino Castoldi_2
Honored Contributor

How to place command line statements into a sql script?

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

5 REPLIES
Steven E. Protter
Exalted Contributor
Solution

Re: How to place command line statements into a sql script?

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Yogeeraj_1
Honored Contributor

Re: How to place command line statements into a sql script?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: How to place command line statements into a sql script?

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
Honored Contributor

Re: How to place command line statements into a sql script?

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




Never give up, Keep Trying
Steven E. Protter
Exalted Contributor

Re: How to place command line statements into a sql script?

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com