DBA Scripts

Here are some of the scripts I use regularly. Some of them are not necessary when using Oracle Enterprise Manager (OEM), but if like me you hate waiting around for OEM to start up you'll often get the job done more quickly with these. Cheers Tim...

These scripts can be used by easyscript for oracle and are available in the file oraclebase.esp produced by James Wang of Gudu Software. Alternatively, download all the scripts in a single zip file (dba.zip).





10g...
active_session_waits.sql db_usage_hwm.sql dynamic_memory.sql
feature_usage.sql generate_multiple_awr_reports.sql job_classes.sql
job_programs.sql job_schedules.sql jobs.sql
jobs_running.sql lock_tree.sql segment_advisor.sql
services.sql session_waits.sql sga_buffers.sql
window_groups.sql windows.sql

11g...
job_credentials.sql memory_target_advice.sql temp_free_space.sql

constraints...
disable_chk.sql disable_fk.sql disable_pk.sql
disable_ref_fk.sql enable_chk.sql enable_fk.sql
enable_pk.sql enable_ref_fk.sql

miscellaneous...
analyze_all.sql column_comments.sql comments.sql
compare_schemas.sql compile_all.sql compile_all_bodies.sql
compile_all_funcs.sql compile_all_procs.sql compile_all_specs.sql
compile_all_trigs.sql compile_all_views.sql conversion_api.sql
csv.sql dict_comments.sql drop_all.sql
dsp.pkb dsp.pks err.pkb
err.pks ftp.pkb ftp.pks
gen_health.sql get_pivot.sql login.sql
proc_defs.sql rebuild_index.sql soap_api.sql
string_agg.sql string_api.sql switch_schema.sql
table_comments.sql table_defs.sql table_differences.sql
trc.pkb trc.pks




monitoring...
access.sql active_sessions.sql cache_hit_ratio.sql
call_stack.sql code_dep.sql code_dep_on.sql
column_defaults.sql db_cache_advice.sql db_info.sql
db_links.sql db_properties.sql dispatchers.sql
error_stack.sql errors.sql explain.sql
file_io.sql fk_columns.sql fks.sql
free_space.sql health.sql hidden_parameters.sql
high_water_mark.sql hot_blocks.sql identify_trace_file.sql
index_extents.sql index_monitoring_status.sql index_partitions.sql
index_usage.sql invalid_objects.sql jobs.sql
jobs_running.sql latch_hit_ratios.sql latch_holders.sql
latches.sql library_cache.sql license.sql
locked_objects.sql longops.sql lru_latch_ratio.sql
max_extents.sql min_datafile_size.sql monitor.sql
monitor_memory.sql monitoring_status.sql non_indexed_fks.sql
obj_lock.sql object_status.sql open_cursors.sql
open_cursors_by_sid.sql open_cursors_full_by_sid.sql parameter_diffs.sql
parameters.sql pga_target_advice.sql pipes.sql
profiler_run_details.sql profiler_runs.sql rbs_extents.sql
rbs_stats.sql recovery_status.sql roles.sql
search_source.sql segment_stats.sql session_events.sql
session_events_by_sid.sql session_events_by_spid.sql session_io.sql
session_rollback.sql session_stats.sql session_stats_by_sid.sql
session_undo.sql session_waits.sql sessions.sql
show_indexes.sql show_space.sql show_tables.sql
source.sql spfile_parameters.sql sql_area.sql
sql_text.sql sql_text_by_sid.sql system_events.sql
system_parameters.sql system_stats.sql table_dep.sql
table_extents.sql table_indexes.sql table_partitions.sql
table_stats.sql temp_extent_map.sql temp_io.sql
temp_segments.sql top_latches.sql top_sessions.sql
top_sql.sql trace_run_details.sql trace_runs.sql
ts_extent_map.sql ts_full.sql tuning.sql
unusable_indexes.sql unused_space.sql user_hit_ratio.sql
user_temp_space.sql user_undo_space.sql

rac...
locked_objects_rac.sql longops_rac.sql monitor_memory_rac.sql
session_undo_rac.sql session_waits_rac.sql sessions_rac.sql

resource manager...
active_plan.sql consumer_group_usage.sql consumer_groups.sql
plan_directives.sql resource_plans.sql

script creation...
backup.sql build_api.sql build_api2.sql
create_data.sql drop_cons_on_table.sql drop_fks_on_table.sql
drop_fks_ref_table.sql drop_indexes.sql fks_on_table.sql
fks_ref_table.sql index_monitoring_off.sql index_monitoring_on.sql
monitoring_off.sql monitoring_on.sql rbs_structure.sql
recreate_table.sql seq_structure.sql table_constraints.sql
table_indexes.sql table_structure.sql tablespace_structure.sql
view_structure.sql

security...
grant_delete.sql grant_execute.sql grant_insert.sql
grant_select.sql grant_update.sql package_synonyms.sql
sequence_synonyms.sql table_synonyms.sql view_synonyms.sql