Scripts

There are many different scripts out there on the web. This collection of scripts is not the same old scripts you will find elsewhere. We have tried to include those scripts which we feel are not as easily found on the web. Use and modify these scripts to suit your purposes. We do not assume any risk when you choose to use these scripts.

SQL Scripts

fix_stats.sql - Will determine if the statistics on the table are incorrect. If so, uses DBMS_STATS to recalculate the statistics. I schedule this in DBMS_SCHEDULER to run once a week...just to keep the Optimizer honest. (05/30/2006)

log_buffer_tune.sql -Displaysl information to know if the log buffer needs to be increased. (04/27/2006)

optimizer_index_cost_adj.sql - Uses system statistics to determine a more optimal value for this CBO parameter. (08/14/2002)

archivelog_histogram.sql - Creates a histogram of archive log activity so you can easily spot your busiest times of the day.

buffer_map.sql - Shows you what is currently in the buffer cache.

free_space.sql - Shows the tablespace free space

locks.sql - Shows who is holding what and who is waiting for what.

rollback_activity.sql - Shows which sessions are using which rollback segments

table_dependencies.sql - Shows everything that depends on this table and everything this table depends on. Useful information before dropping a table during a reorg.

ts_map.sql - Shows a map of a tablespace's objects, including free space. Really useful in spotting fragmented free space.

user_trace.sql - Used to start a trace in a user's active session.

Perl Scripts

delete_old.zip - Used to delete archived redo logs older than a certain number of days. This is easily done in Unix with a series of piped commands, but Windows doesn't have this same sort of native ability. I wrote this script to accomplish the same thing in Windows, but it can be run on any Perl-supported platform.

Contact me at oracle_dba@peasland.net if you have any questions.