Scheduling weekly BMC gather stats using Oracle DBMS_SCHEDULER

Version 1

    BMC provides a stored procedure, BL_GATHER_SCHEMA_STATS, which they recommend be run weekly to improve performance and general database health.  If it was scheduled using the BladeLogic scheduler, then the password would need to be stored and used in some manner that would lower security.  On Oracle 10+, however, the DBMS_SCHEDULER package can be used to run it in an automated fashion without storing the password anywhere.


    Getting a job properly scheduled and working took a small amount of trial and error.  I'm posting my results here hoping that others will find this useful and save them the trouble of figuring out the scheduler.


    The file bmc_schedule_gather_schema_stats.sql actually performs the scheduling.


    The file test_stored_proc.sql creates a stored procedure WITH THE SAME NAME AS THE BMC VERSION.  It is used, obviously, just for testing.  Once edited properly, it can use UTL_SMTP to send email when it is run, so that you can verify the scheduler does what you want it to.  This step isn't necessary, quite frankly, but I'm leaving it here in case it is found useful.


    The file uninstall_schedule.sql, obviously, uninstalls all of the stuff that is used to put together the schedule.


    Finally, the file verify_schedule.sql contains commands useful in verifying and debugging.  It shows how to query two tables, one which shows that the program is in the scheduler (and when it will run), and the other that can show the success or failure (and any diagnostics messages) of the program after the scheduler has run it.