Oracle Script Bank
Here is a grab bag of Oracle SQL and unix/linux shell (work in progress) scripts from my days of working with Oracle databases. I thought I would throw them on here — hopefully you will find them useful.
Some of these are quite old now, so no guarantee they will work on recent versions of the Oracle database. Feel free to view and download any of these scripts. Comments are welcome.
Check out the main Oracle page for other database related information and also our home page to see our treasure trove of blogs and articles.
SQL Scripts
Real-Time Monitoring
2pc.sql – Displays distributed transactions.
active_tx.sql – Displays active transactions.
all_users.sql – Displays all sessions currently connected, apart from the SYS user.
bigusers.sql – Users ordered by the number of commits they have done.
blocker.sql – Blocking sessions and corresponding waiting sessions in the database.
bad_queries.sql – Helps display potentially shoddy/tunable queries.
buffers.sql – Displays database buffers/memory blocks by segment.
cache_hits.sql – Database reads — logical vs physical.
contention.sql – Database contention related queries.
dbprobe.sql – Performs a bunch of queries to help report on database performance/health.
enq.sql – Shows locks currently held.
enq_stats.sql – Shows lock stats.
find_latch_holders.sql – Shows session info for latch holders.
io.sql – Displays I/O stats
latch_holder.sql – Displays session info for sessions holding library cache latches
latch_holders.sql – Displays session info for sessions holding any latches
library_lock.sql – Displays session info for sessions holding any library cache resources
lock_info.sql – Displays information about locks held
lockinfo.sql – Displays information about locks held
lock_review.sql – Displays information about sessions holding library cache resources.
locks_and_sql.sql – Sessions and the SQL they are executing for lock holders.
ltn.sql – Locking transactions. Some quite detailed info.
machine_count.sql – Number of sessions, grouped by machine.
num_users.sql – Number of sessions, grouped by machine and program.
os_num_users.sql – Number of sessions, grouped by os_user, machine and program.
packages_kept.sql – Triggers, procedures and packages that are “kept” in the SGA.
pkgs_not_kept.sql – Triggers, procedures and packages that are NOT “kept” in the SGA.
rbs_contention.sql – Rollback/undo segment contention.
rbs_objs.sql – Session transactions with rollback segment info and the objects they are locking
rbs_user_objs.sql – Similar to above, but for a specified username
rbs_who.sql – What users and objects are using rollback segments currently
rbs_wrap_time.sql – How many hours since each rollback segment “wrapped”
rollbacks_in_use.sql – Rollback segments being used
sess_mem.sql – User session memory stats
unshared_sql.sql – SQL in shared pool that is not “shared”
user_rbs_objs.sql – Rollback segments used by a given os_oser
waiters.sql – Sessions that are waiting on events
who.sql – Sessions info for all connected sessions
writers.sql – Sessions that are doing database write activities
Space Management & File Management
backup_mode.sql – Shows the backup mode/status of all database files.
block_usage.sql – Reports block usage statistics for segments. Tables need to be analyzed first.
early_warning.sql – Provides proactive warnings for space issues.
early_warning_extents.sql – Similar to above
early_warning_maxext.sql – Similar to above
early_warning_space.sql – Similar to above
free_space_files.sql – Free space in database files
space_report.sql – Database space report
ts_free.sql – Tablespace free space report
ts_max_blocks.sql – Displays the “high-watermark” block by file/tablespace
Objects and Structures
analyze.sql – Analyze a table.
cbo_stats.sql – Optimizer statistics for tables and indexes.
cbo_tab_info.sql – Optimizer statistics for tables and indexes.
index_ratios.sql – Displays index size as a ratio of its table size.
index_size.sql – Gives an estimate of how big an index will be before you create it.
invalid_objs.sql – Displays invalid objects
search_triggers.sql – Searches trigger code for the specified string value
sel_indx.sql – Display indexes for the specified table name
sel_indx_cols.sql – Display index columns for the specified index
sel_obj.sql – Display objects that are like the specified object name
temp_segs.sql – Displays temporary segments
Security, Users and Sessions
find_PID.sql – Finds session info by specified username.
fpid.sql – Displays session info for a user for the specified client/foreground process ID.
fpid_sql.sql – Displays executing SQL for a user for the specified client/foreground process ID.
pid_sql.sql – Displays executing SQL for a user for the background process ID.
role.sql – Shows all privs with the role and all users with the role.
sel_sess.sql – Shows all non-system sessions currently connected
sel_sess_u.sql – Similar to above, but for a specified user name
sel_user.sql – Show user information including granted priv’s
show_sid.sql – Display session ID for a given background/shadow process ID
show_spid.sql – Display background/shadow process ID for a give session ID
show_user.sql – Display session information for a given user name
sid_sql.sql – Displays executing SQL for the specified session ID.
sid_stat.sql – Displays session stats for the specified session ID.
user_sess_mem.sql – UGA memory used by a given session ID
user_sess_mem_all.sql – Similar to above but for all sessions
user_sess_mem_max.sql – Max UGA memory used for a given session ID
user_sql.sql – Currently executing SQL for a given username
user_stats.sql – Aggregated SQL execution information for a given username
Database Instance
hash_show.sql – Shows SQL for a given hash value.
job_list.sql – Lists database jobs
jobs_running.sql – Jobs currently running
long_ops.sql – Long running operations still to complete
max_free_sga.sql – Max free chunk of memory in the SGA
memory.sql – Spits out a pile of memory related stats
rbs_header_details.sql – Rollback segment header details
rbs_info.sql – Detailed Rollback segment details
rbs_shrink.sql – Shrink a rollback segment
shared_pool.sql – Shared pool information
system_state_dump.sql – Generates a system state dump trace file.
Script Generation
gen_break_jobs.sql – Generates a script to “break” database jobs
gen_change_pct.sql – Generates a script to change the PCTFREE property on segments
gen_users.sql – Generates a script to create database users.
get_sniped_PID.sql – Generates a unix style script to kill users whose sessions are SNIPED.
rbs_shrink_all.sql – Generates a script to shrink all rollback segments
Miscellaneous
broken_jobs.sql – List all broken database jobs.
compile_objs.sql – Re-compiles invalid objects.
delete_dup_rows.sql – Generic script to delete duplicate rows from a table.
display_plan.sql – Query plan display.
find_job.sql – Finds a database job by (part) name.
get_link_info.sql – Displays database link details
hex_dec.sql – Function to convert the specified hex value to decimal
job_brk.sql – Breaks a single job.
long_date.sql – Alters default date format to “long” format
plan.sql – Displays query plan
shared_pool_calc.sql – Script to help estimate the required size of the shared pool
sqlplan.sql – Displays query plan for the specified SQL hash value
times_pack.sql – Package containing some time based utility functions