Oracle Scripts

From Robs_Wiki
Jump to: navigation, search

More SQL scripts are available in the Oracle Development section of this website

Script Purpose
admin_dbinfo.sql gathers info about the database in html format, good for database intakes
admin_db_maintenance_jobs.sql
shows dba maintenance jobs and run history. Checks if maintenance window is large enough.
admin_param_history.sql
shows historic and current value of db parameter
admin_patch_info.sql
script to check sql registry after patching, or to see patch history
admin_stats_table.sql
check if table and related indexes contain stale statistics
admin_stats_partitions.sql
gather statistics on all partitions of a given table
admin_streams_status.sql
shows the status of streams apply and capture processes (if configured)
admin_streams_stop_start.sql
scripts to stop and start streams capture and apply processes
admin_user_grants.sql
shows granted roles and privileges for a user. Takes username as parameter
dev_show_foreign_keys.sql lists foreign key dependencies, takes some optional parameters, for example parent or child table name
dev_show_index_columns.sql show the indexes and their columns from a single table, takes table_name as input
dev_find_fk.sql
script to find Foreign Key releationships. Enter Parent Table (optional) and Child Table (optional) as parameters
dev_find_pmk.sql
script to find Primary Key columns. Enter Table Name as parameter
dev_find_related_objects.sql
script to find in which other object the provided object is being used.
dev_gen_indexes.sql script to generate index creation statements, in parallel and distributed across multiple files (use one file per compute node)
perf_active_session_cpu.sql
displays cpu usage of active sessions
perf_buffer_pool.sql
displays buffer pool info and objects currently cached in the buffer pool
perf_dbms_xplan_cursor.sql script to format output of the execution plan based on display cursor of latest statement, also shows execution order.
perf_get_sqlid.sql
get the sql_id and child_number of a certain statement, takes text string as argument
perf_longops.sql
view long running sessions in the database
perf_sqlid_history.sql
get the snapshot history of a certain sql_id, takes sql_id as parameter input
perf_show_plan.sql
shows execution plan of certain statement
perf_topsql.sql
shows top 10 SQL order by CPU usage. Uses v$active_sessions_history view which requires Diagnostics Option
rman_report_backup.sql
creates backup report of last 5 rman backups of the database
rman_report_backup2.sql
shows backups, status and duration of a database
sqlnet check hourly database connections checks number of database connections per hour
storage_tbsinfo1.sql displays tablespace information
storage_show_temp_usage.sql shows current users and their temporary tablespace consumption
storage_show_temp_usage2.sql shows historic temporary tablespace consumption
win_start_all.bat start database and listener on windows
win_stop_all.bat stop database and listener on windows