Oracle Point, Oracle Life.

In versions before Oracle 10g, statspack is useful utility to monitor database performance and workload issues. I just came across a shell script which can automate snapshot report create and mail to DBA.

The script is re-posted as below without change.

[oracle@www1 sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!

BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac

/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc<<!
define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!

mail -s “perfstat report” ddd@eee.fff < /home/oracle/sql/report/sp`date +%m%d`_ac.lst

[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >> /home/oracle/sql/backup/perf.lst 2>&1

Note: The snapshot report is created from 8:00 AM to 8:00 PM daily and the job of crontab is called at 9:00 AM.

Popularity: 23% [?]

Oracle SQL Developer is a free and fully supported graphical toll for database development. Its latest version 1.5 offers multi-databases support, such as Oracle, SQL Server, Access and MySQL. Also, Timesten is default to be supported with this new version.

Similar to famous IDE Eclipse, oracle now provides an “extension development kit” to have more third party involved in extension development. In page of “oracle SQL Developer - Extensions Exchange”, you can find a list of available extensions for Oracle SQL Developer in different versions. On that list, there is only one extension available for the latest version of Oracle SQL Developer. That is OrindaBuild, which generate Java source code to run your PL/SQL procedures and functions or any SQL statement you give it.

Besides of this extension, I also tried out the following three extensions below, which is not noted to be available for the latest version of Oracle SQL Developer. The experimental result is satisfied and they all work with the latest version of Oracle SQL Developer.

1. Insider for SQL Developer
2. Error Lookup for SQL Developer
3. Oracle Data Miner PL/SQL Code Generator

(more…)

Popularity: 11% [?]