Difference between revisions of "Oracle Scripts"

From Robs_Wiki
Jump to: navigation, search
Line 107: Line 107:
 
| [[perf_log_switches.sql]]<br />
 
| [[perf_log_switches.sql]]<br />
 
| redo log switch frequency map. Best practise is <= 3 log switches per hour
 
| redo log switch frequency map. Best practise is <= 3 log switches per hour
 +
 +
|-
 +
| [[perf_redo_volume.sql]]<br />
 +
|
  
 
|-
 
|-

Revision as of 10:21, 15 August 2019

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

Script Purpose
admin_active_sessions.sql shows active foreground sessions in the database
admin_all_sessions.sql shows all active and inactive sessions
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_hidden.sql
shows value of all hidden parameters
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_show_flashback_time.sql
shows the earliest time you can flashback to plus any restore points
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
dev_show_db_links.sql
shows database links
dev_show_user_grants.sql
shows granted roles and privileges for a user. Takes username as parameter
dev_find_child.sql find the children of a parent table, enter name and schema when asked
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_log_switches.sql
redo log switch frequency map. Best practise is <= 3 log switches per hour
perf_redo_volume.sql
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_show_sessions.sql
shows number of sessions and cpu activity grouped by hour. Takes the number of hours as input. Uses licensed awr views
perf_show_sessions_blocked.sql
shows who is blocking who
perf_tbl_fts.sql
analyses full table scans versus index scans of particular objects. Requires diagnosting & tuning pack
perf_tbl_hwm.sql
shows high water mark of table. Takes table_name (or ALL) and schema owner as input parameters
perf_topsql.sql
shows top 10 SQL order by CPU usage. Uses v$active_sessions_history view which requires Diagnostics Option
rman_progress.sql
view the progress of a rman operation
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