1748156 Members
3906 Online
108758 Solutions
New Discussion юеВ

Script problem with SQL

 
SOLVED
Go to solution
ericfjchen
Regular Advisor

Script problem with SQL

We would like to do the following,
-------------------------------------
select * from v$sgastat;
execute the SQL statement every 10 minutes and monitor it days.save the output to a file
-------------------------------------
How to write a script to do that?

Thanks


8 REPLIES 8
Eric Antunes
Honored Contributor

Re: Script problem with SQL

Hi Eric,

One way to put the data into a table (after that you can extract it to a file), as oracle user (usually oracle):

$cd scripts
$vi sgastat.sh

#you must create the table before with the columns you want....

insert into sgastat_temp (
select * from v$sgastat);

commit;

#save the file

$cd /usr/spool/cron/crontabs
$vi oracle.tmp

#insert the following line to run every hour:

20 * * * * $HOME/scripts/sgastat.sh

#save the file

$crontab oracle.tmp

Regards,

Eric Antunes
Each and every day is a good day to learn.
ericfjchen
Regular Advisor

Re: Script problem with SQL

Thanks. It is a great way to do. But if we don't want to insert to a temp table, how can we do that? Any other good script skill?

Kevin Bingham
Regular Advisor

Re: Script problem with SQL

Well, there are two parts to this... calling sqlplus with an SQL Script as input, then schedulling that via cron.

Part 1)
create a shell script similar to the following:

# Script to call sqlplus with SQL file passed in
TIMESTAMP=`date +%m-%d-%y_%H.%M.%S`
if [ -f /tmp/spoolsga.out ]; then
rm /tmp/spoolsga.out
fi
sqlplus uid/pwd@sid @/home/userid/sql/mysgascript.sql
mv /tmp/spoolsga.out /home/userid/logs/spoolsga.out.$TIMESTAMP
# end shell script

Then make /home/userid/sql/mysgascript.sql as follows:
-- start spoolsga.sql
set pages 0
set lines 132
set feedback off
spool /tmp/spoolsga.out
select * from v$sgastat;
exit
-- end spoolsga.sql


Part 2)

Scedule the shell script created in part 1) in cron to run every 10 minutes.

HTH

Regards,
Kevin
jpcast_real
Regular Advisor

Re: Script problem with SQL

You can use the sql spool command to direct the output to a file .

SQL> spool file
SLQ> selec .....
SQL> spool off

Here rests one who was not what he wanted and didn't want what he was
Jan Sladky
Trusted Contributor
Solution

Re: Script problem with SQL

hi Eric,
one solution is following:
prepare sh script launched by cron like this
(change values and number of variables according to yours)

#!/bin/bash
ORACLE_SID="SID"
TWO_TASK=SMS
ORACLE_HOME="/opt/Oracle"
PATH="/usr/bin/:$ORACLE_HOME/bin:"
export ORACLE_SID
export ORACLE_HOME
export PATH TWO_TASK

sqlplus -s / @PATH_TO_YOUR_SCRIPT/script.sql
>> sgstat.out


sql will contain:

set heading on #column's names
set term on #verbose output
select concat('v$sgastat at ', to_char(sysdate,'YYYY_MM_DD HH24:MI:SS')) from sys.dual; #info about time

select * from v$sgastat;

exit;

----------
crontab -e
0,10,20,30,40,50 * * * * /home/script.sh

Every ten minutes you should have output in sgstat.out.

Maybe you have to change syntax for login on oracle from / to user/password@connect_string
from tnsnames ora it depends on your setting


br Jan
GSM, Intelligent Networks, UNIX
Fred Ruffet
Honored Contributor

Re: Script problem with SQL

You can avoid maintaining 2 files (shell script and SQL script) by including SQL into shell using something like that :

sqlplus -s / >> sgstat.out <set heading on #column's names
set term on #verbose output
select concat('v$sgastat at ', to_char(sysdate,'YYYY_MM_DD HH24:MI:SS')) from sys.dual; #info about time

select * from v$sgastat;

exit;
EOF

text between "<
Regards,

Fred


--

"Reality is just a point of view." (P. K. D.)
Yogeeraj_1
Honored Contributor

Re: Script problem with SQL

hi,

one option would be to create a table and schedule a job (using DBMS_JOB) that will populate this table. Afterwards, you can query this table and output it to a file.

e.g.
yd@MYDB.MU> create table my_sgastat as
2 select sysdate current_date,v$sgastat.* from v$sgastat where 1=0;

Table created.

Elapsed: 00:00:00.07
yd@MYDB.MU> desc my_sgastat
Name Null? Type
------------------------------- -------- ----
CURRENT_DATE DATE
POOL VARCHAR2(11)
NAME VARCHAR2(26)
BYTES NUMBER


yd@MYDB.MU> declare
2 l_job number;
3 begin
4 dbms_job.submit( l_job,
5 'insert into my_sgastat select sysdate, v$sgastat.* from v$sgastat;',
6 sysdate,
7 'sysdate+10/24/60' );
8 end;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24

yd@MYDB.MU> select job,to_char(last_date, 'dd/mm/yyyy hh24:mi'),
2 to_char(next_date, 'dd/mm/yyyy hh24:mi'),
3 broken, failures, substr(what,1,20)
4 from dba_jobs;

JOB TO_CHAR(LAST_DAT TO_CHAR(NEXT_DAT B FAILURES SUBSTR(WHAT,1,20)
__________ ________________ ________________ _ __________ ____________________
108 15/09/2004 14:19 N insert into my_sgast

Elapsed: 00:00:00.01
yd@MYDB.MU> exec dbms_job.run(108);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
yd@MYDB.MU> select job,to_char(last_date, 'dd/mm/yyyy hh24:mi'),
2 to_char(next_date, 'dd/mm/yyyy hh24:mi'),
3 broken, failures, substr(what,1,20)
4 from dba_jobs;

JOB TO_CHAR(LAST_DAT TO_CHAR(NEXT_DAT B FAILURES SUBSTR(WHAT,1,20)
__________ ________________ ________________ _ __________ ____________________
108 15/09/2004 14:19 15/09/2004 14:29 N 0 insert into my_sgast

Elapsed: 00:00:00.01
yd@MYDB.MU>

and then query this table as you wish.

sending to file would only imply a "spool filename.txt" before the actual query to the table my_sgastat.

hope this helps!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Script problem with SQL

hi again,

compared to the cron solution provided above, this DBMS_JOB way provides you with added advantages:
1. they data collection is made only when the database is up
2. allows you to query/analyse your stats more easily (e.g. by period of dates)

Also, you should also consider runnning regular statspack reports more for stats.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)