- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Script problem with SQL
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 07:50 PM
тАО09-14-2004 07:50 PM
-------------------------------------
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 08:14 PM
тАО09-14-2004 08:14 PM
Re: Script problem with SQL
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 08:18 PM
тАО09-14-2004 08:18 PM
Re: Script problem with SQL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 08:27 PM
тАО09-14-2004 08:27 PM
Re: Script problem with SQL
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 08:27 PM
тАО09-14-2004 08:27 PM
Re: Script problem with SQL
SQL> spool file
SLQ> selec .....
SQL> spool off
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 08:29 PM
тАО09-14-2004 08:29 PM
Solutionone 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 08:41 PM
тАО09-14-2004 08:41 PM
Re: Script problem with SQL
sqlplus -s / >> sgstat.out <
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.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 10:25 PM
тАО09-14-2004 10:25 PM
Re: Script problem with SQL
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-14-2004 10:29 PM
тАО09-14-2004 10:29 PM
Re: Script problem with SQL
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