INDEX

TUNING: scripts to aid with tuning

@dbms-sqltune-sqlid.sql: call with SQL_ID, create and execute a tuning task, run the report
@profile_from_awr.sql: create a SQL Profile from plan in AWR

APEX: Anything to do with Apex
@apex-version.sql: Get the version of Apex.  For CDB/PDB, run from both.

LIB ADMIN:
@distribution.sh: the script that builds the linux tar and windows zips files

BACKUP and RECOVERY:
@rman-bkup-status.sql: Status of backups
@rman-bkup-details.sql: Details for a backup set
@rman-recovery-scn.sql: determine the SCN from which the database must be restored and recovered 
@rman-recovery-min-scn.sql: determine minimum restore and recover SCN values

PARALLEL PROCESSING:

@px.sql: query gv$px_process to see all parallel slaves clusterwide-works for single node too
@pq-ash-all.sql: aggregate PQ query counts per time period
@pq-ash-sqlid.sql: aggregate PQ per sqlid and time
@pq-awr-all.sql: aggregate PQ per time period
@pq-awr-sqlid.sql: aggregate PQ per sqlid and time

SUPPORTING SCRIPTS:

@ascii.sql: generate a simple ascii table
@bad-date.sql:  Oracle believes there is a year zero
@bitwalk.sql: discover which bits are set in a bitmap column
@clears.sql: clear sqlplus settings
@clear_for_spool.sql: set sqlplus for spooling output without headers,etc
@colors.sql: define values for sqlprompt colors
@columns.sql: several sqlplus column settings
@enqueue-bitand.sql: Demonstrate how to decode v$session.p1 values for enqueue waits
@get_date_range.sql: get begin and end date, put in vars - also date format var
@get-schema-name.sql: prompt for schema name - schema name can be passed as a parameter
@get-table-name.sql: prompt for table name - table name can be passed as a parameter
@opcodes.sql: list of SQL opcodes for use in 10g-. See cpu-busy.sql
@oversion_minor.sql: get the XX.xx version of oracle and store in &v_oversion_minor
@oversion_major.sql: get the XX version of oracle and store in &v_oversion_major
@ttitle.sql: set title and width
@title.sql: set title and width
@title80.sql:  set title and width to 80
@title132.sql: title and width to 132
@nls_date_format.sql: set custom date and time formats, several options available at runtime
@nls_time_format.sql: set custom (fixed) date and time formats
@spool_example.sql: 
@scott.sql: create the scott tables
@sql_trick_1.sql: demonstrates a very useful technique for conditionally executing SQL


RDBMS UTILITIES:

@10046.sql: Set event 10046 in a session
@10046_off.sql: Stop event 10046 in a session
@block_decode.sql: find which object a block belongs to
@bootstrap_objects.sql: report objects from sys.bootstrap$ that may not be modified
@cluster-factor.sql: get the clustering factor for all indexes on a table
@cores.sql: report the number of CPU cores from v$osstat - may be subject to hyperthreading
@dual_data_gen.sql: generate many rows from dual - uses a lot of memory for large number of rows
@dual_data_gen-low-mem.sql: generate many rows without using extra PGA
@dbms_log.sql: use sys.dbms_log to write to log and trace files - 11.2.0.4+
@dbms_output-allow-blank-lines.sql: just a demo of how to create blank lines via 'set format wrapped'
@dbms_system_undoc_calls.sql: some undocumented dbms_system calls - how to write to alert.log
@dumptrace_off.sql: Turn on SQL_trace in a session
@dumptrace_on.sql: Turn off SQL_trace in a session
@dumptracem_off.sql: Turn on SQL_trace for all sessions for a user
@dumptracem_on.sql: Turn off SQL_trace for all sessions for a user
@dup_role.sql: Generate SQL script to duplicate a database role
@dup_role_users.sql: Generate SQL script to duplicate all users of a role
@dup_user.sql: Generate SQL script to duplicate a database user
@dump.sql: Dump a table to a CSV file, generate SQL Loader parameter and control files.
@find-index-sql.sql: find SQL where an index has been used - uses AWR
@gen_data_with_recursion.sql: use a recursive subfactored query to generate rows
@gen_fk_from-11.1.sql: generate existing foreign key constraints from data dictionary
@gen_fk_from-11.2.sql: generate existing foreign key constraints from data dictionary
@gen_fk_to-11.1.sql: generate existing foreign key constraints from data dictionary
@gen_fk_to-11.2.sql: generate existing foreign key constraints from data dictionary
@gen_list_data_with_dual.sql: generating test data with dual
@gen_list_data_without_dual.sql: generating test data without dual - 10g+
@gethostname.sql: get the hostname into substitution variable uhostname
@getinstance.sql: get the instance name into substitution variable uinstance
@getinstanceowner.sql: get the instance owner into substitution variable uinstanceowner
@getpid.sql: get the session PID into substitution variable upid
@gettracefile.sql: copy the current sessions tracefile from the host
@gettrcname.sql: get the name of the current sessions tracefile into substitution variable utracefile
@hash-function.sql: create a PL/SQL package 'hash' containing digest functions using dbms_crypto
@hwm-df.sql: Find the high water mark for each datafile and determine how much each file can be shrunk
@oradebug_doc.sql: dump the documentation for oradebug
@print_table_2.sql: Tom Kytes print_table, but as an anonymous block
@pt.sql: similar to Tom Kytes print_table, but no stored procedure required and better quoting
@q_quote.sql: demo for the q[] quoting mechanism in SQL - 10g+ I think
@remove-sqlplus-settings.sql: remove the 'store set' temp file
@restore-sqlplus-settings.sql: restore sqlplus settings from a temp flie
@save-sqlplus-settings.sql: save sqlplus settings to a temp file
@set-default-profile-unlimited.sql: Used to elimnate password timeouts in test databases
@set_events.sql: various methods to set events, including per sql_id
@show_event_messages.sql: List events 1000-10999
@spacemap.sql: create a map of segments and free space
@spacemap_rpt.sql: report on spacemap created by spacemap.sql
@spacemap_sum.sql: create a summary of space as created by spacemap.sql
@spacemap_sum_rpt.sql: report on space summary table created by spacemap_sum.sql
@sqlid-trace.sql: set 10046 or 10053 trace per sqlid regardless of session
@sql-command-types.sql: list all sql available commands 
@troff.sql: Turn off SQL tracing for all sessions of an account
@tron.sql: Turn on SQL tracing for all sessions of an account
@table_ddl.sql: generate DDL for owner.table, with indexes, constraints, etc
@user_ddl.sql: Generate SQL script to duplicate a database user using DBMS_METADATA


TEMPORARY SEGMENTS/SORTS:

@showtemp.sql: show who owns TEMP segments and type of segment
@whotmp8i.sql: show who owns TEMP segments - more info than showtemp.sql
@showsort.sql: Show sort activity

IO:

@avg_disk_times.sql: Show avg physical read/write times 
@who5.sql: physical IO per session
@io_begin.sql: Save snapshot of current file IO statistics
@io_end.sql: Save snapshot of current file IO statistics
@io_order.sql: Shows snapshot of IO stats based on io_begin and io_end
@io_stat2.sql: Shows snapshot of IO stats based on io_begin and io_end
@io_stat3.sql: Shows snapshot of IO stats based on io_begin and io_end
@io_stat.sql: Shows snapshot of IO stats based on io_begin and io_end
@io_tbs.sql: Shows snapshot of IO stats based on io_begin and io_end
@lfsdiag.sql: diagnose logfile sync
@ioweight.sql: Show IO per tablespace order by weight
@redo-rate.sql: show real time redo rates at the db level
@showtrans.sql: Show current transactions with IO
@trans_per_hour.sql: Transactions per hour with statistics per xaction

EVENTS:

@my-events.sql: display session stats
@set_events.sql: various methods of generating trace and dump info with events
@sysevent_begin.sql: Beginning snapshot of system events
@sysevent_end.sql: Ending snapshot of system events
@sysevent_rpt.sql: Report on system event snapshots
@sessevent2.sql: Show events from v$session_event
@sessevent.sql: Show events from v$session_event
@session_fix.sql: Show fix_control_settings for session
@system_fix.sql: Show fix_control_settings for system
@system_fix_all.sql: Show all fix_control_settings for system

WAITS/LOCKS/LATCHES and PERFORMANCE:

@active_status.sql: show which current active sessions are on CPU
@cpu-killer.sql: max out a CPU. Do Not use in production!
@itl_waits.sql: show itl waits - increase initrans
@itl_waits_hist.sql: show itl waits history
@showlatch.sql: Show latches and stats
@showlock.sql: Show locks in database with waiters and blockers
@getstat.sql: called by getstats.sql
@getstats.sql: Get stats from v$sysstat
@getstatu2.sql: Get stats from v$sesstat
@latch_statsa.sql: 
@latch_statss.sql: 
@sesswait.sql: Show waits from v$session_wait - calls the script linked or copied to sesswaitu.sql
@sesswaitu.sql: script called by sesswait.sql - copy or softlink one of the following sesswait scripts
@sesswaitug.sql: similar to sesswaitu.sql, but uses gv$ views
@sesswaitp.sql: show current waits for a session id - may call as '@sesswaitp SID'
@sesswaitu72.sql: sesswaitu for 72
@sesswaitu73.sql: sesswaitu for 73
@sesswaitu10g.sql: sesswaitu for 10g
@sesswaitu_112.sql: sesswaitu for 11.2
@dba_kgllock.sql: show waiters/blockers on library cache locks.
@libcachepin_waits.sql: if there are waits on Library Cache Pin in v$session_wait this script will show what the waits are for, and which session is causing them
@mystat.sql: query v$mystat

AWR/ASH:
@aas.sql: get AAS (average active sessions) from gv$sysmetric
@aas-awr-calc.sql: dump AAS calculated from AWR to CSV file
@aas-ash-calc.sql: report AAS calculated from ASH
@aas-awr-pdb-calc.sql: calculate AAS per PDB from AWR data.  Cuz Oracle does not do it.
@aas-std.sql: dump AAS from dba_hist_sysmetric_history to CSV file
@aas_hist_metrics.sql: get average active sessions along with CPU metrics
@aas_history.sql: get history of Average Active Sessions
@ash-all-events-5-pct.sql: show events per SQL where the event consumes > 5% of db time for the execution of that SQL
@ash-blocker-waits.sql: find top level blockers in ASH
@ash-events.sql: simple filtered query on ASH events for a SQL_ID
@ash-sessions.sql: frequency of sessions for a user
@ash-snapshot-define-begin-end.sql: example of how to bracket snap_id
@ash-waits-user.sql: summarize ASH all wait time for a user
@ash_blockers.sql: current blocking aggregated by event
@ash_blockers_10g.sql: find top level blockers in ASH for 10g
@ash_blocking.sql: get list of row lock blocks - blocked and blockers with SQL_ID
@ash_cpu_hist.sql: cpu historic usage from dba_hist_sysmetric_history - 12c+
@ash-current-waits.sql: find the current top wait events per SQL by class and event
@ash-current-waits-by-sql.sql: find the current top 20 SQL by execution time per session that occurred in a single session
@ash-current-waits-by-sql-event.sql: find the current top 20 SQL by execution time per event that occurred in a single session
@ash-sqlid-event-window.sql: show top SQL within window of time, such as from 1 minute before to 1 minute after the top of each hour
@ash_log_sync.sql: log sync events
@ashdump.sql: create an ASH Dump - be sure to read the comments in the script
@ashdump-summary.sql: example script to view ASHDUMP data
@ashtop.sql: Tanel Poder script for top ASH events
@awr-blocker-waits.sql: find top level blockers in AWR
@awr-cpu-stats.sql: Report on sar like CPU stats from AWR
@awr-export.sql: export AWR - useful for pre-migration work
@awr-get-retention.sql: Display AWR retention and interval
@awr-hist-model-top10.sql: Show Top 10 Snapshots based on DB Time + DB CPU from DBA_HIST_SYS_TIME_MODEL
@awr-itl-waits.sql: find ITL waits
@awr-resource-limit.sql: history of processes and sessions from dba_hist_resource_limit
@awr-set-retention.sql: Example of setting AWR retention and interval
@awr-top-5-events.sql: similar to awr-top-events.sql.  reports on past 7 days, shows pct of time used
@awr-top-events.sql: get the top events from AWR per instance for a date range
@awr-top-sqlid-events.sql: get the top events from AWR per instance and SQL_ID for a date range
@awr-trans-counts.sql: show summary of user commits, rollbacks and log sync writes by day
@awr_RAC_defined.sql: Run a non-interactive AWR report on RAC
@awr_blockers.sql: historic blocking aggregated by sql_id
@awr_bracket_baseline.sql: create a named and self expiring AWR baseline based on event time
@awr_bracket_snaps.sql: get snap_id values for a pair of days
@awr_create_snapshot.sql: create an AWR snapshot
@awr_defined.sql: Run a non-interactive AWR report
@awr_display_baselines.sql: display AWR baselines
@awr_drop_baseline.sql: drop an AWR baseline
@awr_file_io_times.sql: Historical IO times on ASM files
@awr_get_snapshots.sql: Get AWR snapshots for a date range
@awr_itl_waits_10g.sql: find ITL waits in 10g
@awr_settings.sql: query the dba_hist_wr_control view
@cpu-busy.sql: Show what SQL Operations were on CPU
@dba_hist_sys_time_model.sql: example of querying dba_hist_sys_time_model - set your own stat_name
@dbw-hist.sql: DBWR CPU and Wait time from dba_hist_active_sess_history
@flash-hist-stats.sql: retrieve recent flash cache stats from AWR
@get-binds.sql: get bind values from dba_hist_sqlbind
@getsql-awr.sql: call with sql_id to get SQL text from AWR
@osstat-cpu.sql: dump OS CPU metrics to CSV file
@plan-counts-force.sql: count of plans matched with force_matching_signature
@resize-ops-metric-awr.sql: Look back through AWR for excessive SGA resize operations before ORA-4031 occurs
@resize-ops-metric.sql: Look in gv$memory_resize_ops for excessive SGA resize operations before ORA-4031 occurs
@rowlock-hist.sql: rowlock history
@rowlock-mode-decode.sql: decode rowlocks in AWR
@rowlock-sqlid-counts.sql: count of rowlock enq by sqlid
@rowlock-sqlid-hist.sql: count of rowlock enq by sqlid - full outer join on snapshot
@session-history.sql: history of sessions from dba_hist_active_sess_history
@sql-count-ash.sql: count of number rows in ASH per SQL_ID
@sql-counts-fms.sql: get sql_id where there are 2+ sql_id per force_matching signature from ASH/AWR
@sql-counts.sql: simple count of SQL_ID from ASH/ASH
@sql-exe-events-ash.sql: show events per execution of SQL_ID in ASH
@sql-exe-events-awr.sql: show events per execution of SQL_ID in AWR
@sql-exe-times-ash.sql: stats and histograms of exucution times for a SQL_ID
@sql-exe-times-awr.sql: stats and histograms of exucution times for a SQL_ID for past 30 days
@sql-plans.sql: Show plans used by a selected SQL for a date and time range
@sysmetric-history.sql: pivot to CSV for several metrics in dba_hist_sysmetric_history
@top10-sql-ash.sql: get top (by count) sql statements from ASH
@top10-sql-awr.sql: get top (by count) sql statements from AWR for past 30 days
@wsqlmon.sql: Provide SQL-Monitor like report from AWR - based on Tanel Poder script for ASH

STATSPACK:

@statspack-tables.txt: not a script - just a description of statspack tables
@snapNmin.sql: start level 7 snapshot, sleep 2 minutes, complete snapshot and create report
@sp_current.sql: get data associated with latest snapshot
@sp_get_date_range.sql:  enter a begin and end date and this script looks up the snap_id for each and sets variables for them
@sp_getsql.sql: retrieve the SQL from input is the hash value of the sql statement in stats$sqltext this will be seen in reports created by spreport.sql in 9i+ where the snapshot level is 5+
@sp_io_stat_drive.sql: get statspack data on physical IO per drive and date range aggregated per hour
@sp_io_stat_sys.sql: report on total IO for the system aggregated per the hour
@sp_job_submit.sql:  run statspack snapshot every 15 minutes via dbms_job
@sp_lvl_0.sql: change statspack to level 0
@sp_lvl_5.sql: change statspack to level 5
@sp_lvl_6.sql: change statspack to level 6
@sp_lvl_7.sql: change statspack to level 7
@sp_lvl_current.sql: get current default snapshot level
@sp_lvl_sql.sql: example - change statspack SQL collection levels
@sp_plan.sql: display historic execution plans from statspack data inputs are number of most recent snapshots to search and the SQL statement to look for (search is case insensitive) the function full_sql_text (full_sql_text.sql) must be created prior to running this script
@full_sql_text.sql:  use this to return the full text of a sql statement from statspack data - version dependent - may not be needed.
@sp_plan_hash.sql: Show execution plans from statspack data. first create view with sp_plan_table.sql - input is the hash value of the sql statement in stats$sqltext this will be seen in reports created by spreport.sql in 9i+ where the snapshot level is 5+
@sp_plan_table.sql: create a view stats_plan_table for use with dbms_xplan.display and stats$sql_plan
@sp_recent.sql: get the 10 most recent snapshots
@sp_resource_limit.sql: history of processes and sessions from stats$resource_limit
@sp_snap.sql: perform a snapshot
@sp_snap_6.sql: perform a level 6 snapshot
@sp_snap_id.sql: example of searching for specific snap_id
@sp_top_sql_io.sql: get top 10 SQL from statspack in terms of Disk Reads

@spreport.sql: call ?/rdbms/admin/sprepins statspack report - calls snap_ids.sql to create a text file of snapshot IDs for viewing in another window
@snap_ids.sql: called by spreport.sql - generate list of snapshot IDs

USERS LOGGED ON:

@get-curr-ospid.sql: get the server PID for your current session
@who.sql: summary of users logged on
@who2.sql: detailed info of users logged on
@who2s.sql: shortened version of who2.sql which is called by some scripts
@who2g.sql: detailed info of users logged on - includes all instances and PDB for 12c
@who5.sql: IO per session
@who6.sql: Show session info for background sessions
@who7.sql: Show session info with IO stats per session
@who8.sql: similar to who2.sql: - shows module and action
@who_dba_jobs.sql: show sessions with jobs running (from dba_jobs)
@who9.sql: same as who_dba_jobs.sql
@who_dblink.sql: sessions using a database link
@who_protocol.sql: show connection method for each session

PARAMETERS:

@check_events.sql: Determine if any events are set in database
@get-alert-log-location.sql: return the filename for the text based alert log file
@getallparm.sql:  get parameters including hidden
@getparm.sql: get parameters
@parm-hist-diff.sql: show difference in parameters from AWR
@showallparm.sql: Show all database parameters, including .hidden. parameters
@showparm.sql: Show database parameters
@showparmchanges.sql: show parameters that have changed - uses AWR
@showparmdrvr.sql: Performs the query for getparm.sql and showparm.sql
@showallparm73drvr.sql: Performs the query for getallparm.sql and showallparm.sql
@showallparm12c-drvr.sql: 12c update for all parms
@parms_dump_csv.sql: Dump all parameters to CSV file
@parms_dump_12c_csv.sql: Dump all 12c parameters to CSV file
@sys_context.sql: Demo of getting oracle environment settings with sys_context function

EXECUTION_PLAN:

@explain_plan_columns.sql: column settings
@sql_current_plan.sql: get dynamic sql plans for hash value from v$sqlplan - works on 9i - must create view with dynamic_plan_table.sql
@dynamic_plan_table.sql: creates view used by sql_current_plan.sql
@liveplan.sql: get dynamic execution plan from hash value
@liveplan10g_hash.sql: get dynamic execution plan from hash value for 10g+
@liveplan10g_sqlid.sql: get dynamic execution plan from sql_id for 10g+
@liveplan_hash.sql: a bit of a misnomer - pulls sql and hash value for a session
@showplan72.sql: show execution plans for oracle 7.2
@showplan73.sql: show execution plans for oracle 7.2+
@showplan9i.sql: show execution plans for oracle 9i+
@showplan_awr.sql: show execution plans from AWR
@showplan_last.sql: show execution plan for most recently executed cursor in current session
@gen_bind_vars.sql: gather bind values from v$sql_bind and generate SQL
@gen_bind_vars_awr.sql: gather bind values from dba_hist_sqltext and generate SQL
@get_bind_values.sql:  get the bind values for a sql_id
@get_awr_bind_values.sql:  get the bind values for a sql_id from AWR

PL/SQL:

@build-record.sql: generate a PL/SQL record type based on table columns
@bulk-collect-1.sql: demo of fetch .. bulk collect into
@dbms_output-abstracted.sql: abstracted procedures and functions for dbms_output
@get_table_lock.sql:  runs a tight loop trying to acquire lock on table - use on busy systems to get the lock required - DO NOT LEAVE TABLE LOCKED!
@package-error.sql: show the source lines for a PL/SQL error
@plsql-return-bool-from-sql.sql: demo of returning a boolean from a function when based on a numeric value
@raise_error.sql: raise any error in the database
@sqlplus_return_code.sql: examples of exiting SQLPlus with an error code
@sqlplus_return_code_2.sql: more examples of exiting SQLPlus with an error cod
@user_exit.sql: an example of exiting sqlplus if the current user is not the one expected

DATABASE STATISTICS - DBMS_STATS - OPTIMIZER:

@chk4incremental.sql: check to see if incremental stats were gathered for a table
@cursor-check.sql: some detail on open cursors per session
@cursor-counts.sql: simple report on cursors with count of child cursors
@cursor-invalidation-reasons.sql: show reasons for cursor invalidation from v$sql_shared_cursor
@dbms_stats_get_prefs.sql: get stats prefs per table and indexes
@dbms_stats_report.sql: HTML report of dbms_stats activity
@dup-system-stats.sql: Generate PL/SQL to duplicate system statistics to another database
@gather_table_stats.sql: gather stats on a tables specified in table_list.sql
@gather_system_stats_iteratively.sql: gather OS stats every 10 minutes for 24 hours
@get_system_stats.sql: display Oracle OS statistics
@global-prefs.sql: display global dbms_stats prefs
@get_prefs.sql: show stats prefs for a schema
@get_stats_job.sql: get name of stored procedure used for autotask stats job - 10g+, maybe 9i
@get_stats_task.sql: get the name of the autotask task used to run the auto stats job - 11g+
@getobj_stats.sql:  show stats for a table down to subpartition level
@histogram_values.sql: show the actual values for histograms
@histo_types.sql: get type of histograms for a schema 
@histo_dist.sql:  show distribution for frequency histograms for schema,table, column
@histo_hist.sql: show historical histogram info for schema,table, column
@histo_hist_dist.sql: show distribution of values for historical histograms for schema,table, column
@locked_stats.sql: show tables and indexes with locked statistics
@logsetup.sql: called by some scripts to create a log - create logs dir first
@ndv.sql: show NDV for a table
@os-stats-avgs.sql: averages of OS IO stats - trying to reduce SAN cache effect
@partstats.sql: Show basic stats info on table and partitions
@partstats_sum.sql: Summary of partition stats
@sampled_size.sql:  show sample size used to collect stats
@sampled_size_details.sql:  show sample size used to collect stats
@session-cursor-metrics.sql: show histograms for open and cached cursors
@set_avg_stats.sql: set average stats on empty partitions - uses table_list.sql
@set_table_prefs.sql:  set table preferences - uses table_list.sql
@show_os_stats.sql: Show stats from v$aux_stats$
@show_os_stats_hist.sql: Show stats from wri$_optstat_aux_history
@stale-stats.sql: Show stats that are stale and at least 7 days old
@stat.sql: get stats info for a table - see comments
@stats_config.sql: set the schema name for some stats scripts
@stats_mod.sql:  show stats being gathered by gather_table_stats.sql
@stats_prefs.sql: show dbms_stats preferences
@stats-sqlid.sql: show basic stats infor for tables and indexes associated with a SQL_ID
@stats_trace.sql: show how to trace dbms_stats - comments only
@stats_trace_test.sql: show that settings to trace stats are not persistent
@stats_wait.sql: show waits on stats collection
@sysaux_free.sql: show free space in sysaux
@table_list.sql: list of tables for gather_table_stats.sql
@unlock_stats.sql: unlocks stats - uses table_list.sql


AUTOTASK and SCHEDULER:

@autotask_auto_stats_disable.sql: disable automatic stats gathering
@autotask_auto_stats_enable.sql: enable automatic stats gathering
@autotask_auto_tasks_disable.sql: disable all autotasks
@autotask_auto_tasks_enable.sql: enable all autotasks
@autotask_client_attributes.sql: call dbms_auto_task_admin.get_client_attributes
@autotask_client_history.sql: show dba_autotask_client_history
@autotask_client_job.sql: show dba_autotask_client_job
@autotask_clients.sql: show dba_autotask_client
@autotask_job_history.sql: show dba_autotask_job_history
@autotask_operation.sql: show dba_autotask_operation
@autotask_resources.sql: call dbms_auto_task_admin.get_p1_resources
@autotask_sched.sql: show dba_autotask_schedule
@autotask_sql_setup.sql: set env for autotask scripts
@autotask_task.sql: show dba_autotask_task
@autotask_window_clients.sql: show dba_autotask_window_clients
@autotask_window_hist.sql: show dba_autotask_window_history
@dba_sched_jobs.sql: show dba_scheduler_jobs
@dba_sched_jobs_hist.sql: show scheduler jobs history
@opthist.sql: show values of dba_stats prefs from the source table
@schedcols.sql: col commands for scripts
@scheduler_programs.sql: show dba_scheduler_programs
@scheduler_windows.sql: show dba_scheduler_windows
@test_calendar_string.sql: provide a scheduler calendar string and number of iterations to see when job runs in dbms_scheduler. Courtesy of oracle-base.com
timezone specific:
@tz_set.sql: set the nls_timezone_tz_format for autotask scripts
@get_sched_tz.sql: get the default timezone for the scheduler
@set_sess_tz.sql: set session timezone the same as scheduler default timezone

RESOURCE MANAGER:

@disable_resource_manager.sql: the correct method to disable the resource manager
@resmgr-columns.sql: configure report columns
@resmgr-consumer-groups.sql: show consumer groups
@resmgr-group-privs.sql: show group privs
@resmgr-plan-directives.sql: show resource manager plan directives
@resmgr-resource-plans.sql: show resource manager plans
@resmgr-setup.sql: set pagesize and linesizes
@resmgr-user-consumer-groups.sql: show consumer group per user
@resmgr-waits-pdb.sql: show resmgr waits per pdb
@resmgr-waits.sql: show resmgr waits 
@resmgr-who.sql: show resmgr waits per user


INSTANCE and/or DATABASE:

@average_active_sessions.sql: show average active sessions - does not use ASH
@archived_log_hist_matrix.sql: show matrix of archive log switch activity for 2 weeks
@archived_log_sums.sql: show rolling total of archive logs for N days
@archived_log_dest.sql: show archived log destination and status for active destinations
@bct_status.sql: show status of block change tracking file
@blocker-tree.sql: show tree of blocked sessions
@colcomm.sql: show columns in common between a set of tables in a CSV list
@csv-split.sql: Demo of using recursive subfactored query to split CSV list from sqlplus command line
@csv-split-2.sql: Demo of using regular expressions to conver a CSV list to rows - both SQL and PL/SQL
@iot_segments.sql: show segments for IOT objects. These are actually index segments
@db_corrupt.sql: report on corrupt database blocks and objects
@dba_dependencies.sql: find all dependencies for owner/object
@dba_jobs_running.sql: Show db jobs currently running
@dba_jobs.sql: Show all scheduled db jobs
@dba_feature_usage.sql: report on used features from dba_feature_usage_statistics
@dba-registry.sql: current registered components
@dba-registry-history.sql: report on upgrade and PSU history
@database_properties.sql: show properties from database_properties
@dbms_application.sql: example of dbms_applicatoin_info usage
@default_tablespace.sql: show default tablespace properties 10g+
@dml-log-errors-test.sql: demo of INSERT INTO Log Table, with Reject Limit
@findobj.sql: Find an object in the data dictionary
@findcol.sql: Find a column for a user in the data dictionary
@fk_hierarchy.sql: Display hierarchy of tables related by Foreign Key (use fktree.sql or fktree-rcte.sql instead)
@fk-circular-ref.sql: Find any examples of tables that reference each other via foeign key
@fktree.sql: Display a hierarchy of tables related by Foreign Key (new script - old one broken)
@fktree-rcte.sql: Display a hierarchy of tables related by Foreign Key (RCTE Version - needs work - still broken)
@fra_config.sql: show FRA location and size
@getsql.sql: call with sql_id to get sql_fulltext
@incarnations.sql: Show database incarnations
@index-col-use-ratios.sql: Show ratio of table columns to columns indexed
@index-correlate.sql: find indexes that appear in a list of plan_hash values
@index-usage-awr.sql: Query AWR to try and determine which indexes are unused
@all_jobs.sql: Show all scheduled db jobs
@show_jobs.sql: does the work for dba_jobs.sql and all_jobs.sql
@supp-col-info.sql: show column level supplemental logging info for a user
@supp-db-info.sql: show database supplemental logging parameters
@supp-tab-info.sql: show table level supplemental logging info for a user
@kglh-growth.sql: monitor for unbounded growth of shared pool memory structures
@kglh-growth-awr.sql: check AWR for unbounded growth of shared pool memory structures
@la8.sql: Shows last analyzed dates for database objects . 8.0+
@la.sql: Shows last analyzed dates for database objects . 7.3
@login.sql: set prompt and editor on login
@log-switch-histogram.sql: Display a histogram of redo log switch times
@loghistory_8.sql: show archive logs with time between switches
@loghist-csv.sql: dump history of archive logs (with timing) to CSV
@oracle-exclude-demo.sql: demonstrate the use of oracle-exclude-inline.sql
@oracle-exclude-inline.sql: inline version of oracle-exclude-schema.sql
@oracle-exclude-schema.sql: show schemas owned by Oracle and are frequently excluded from queries
@oracle-naming-inconsistencies.sql: highlight some of the inconsistencies oracle data dictionary column names
@pivot.sql: Simple demo of PIVOT
@purge_cursors.sql: purge a list of SQL cursors from shared_pool - 10g+ see Oracle Note 457309.1
@reserved-words.sql: List reserved words from v$reserved_words
@setc.sql: automatically or interactively set 'do alter session set container'
@sql_spawned_reasons.sql: Show reasons for creating new child of SQL
@shared-pool-top-sql.sql: show top SQL consumers of shared_pool
@shared-pool-top-users.sql: show top SCHEMA/USER consumers of shared_pool
@show_check_cons.sql: Show non-system generated check constraints
@show-pdbs.sql: Show the con_id and con_name for available PDBs
@show_data_types.sql: Show non-system column data types
@show-fk.sql: Show foreign keys for a user
@show-pk.sql: Show all primary keys for a user
@show-uk.sql: Show all unique keys for a user
@showsga.sql: Show SGA breakdown
@showuser.sql: Show user info
@showpriv.sql: Show privileges granted to a role or user
@showrole.sql: Show roles for a grantee
@showroles.sql: Show all roles and privileges granted
@showprofile.sql: Show resources for a profile from dba_profiles
@showrbs.sql: Show RBS and info
@showrbslock.sql: Show RBS locks
@showsnapshot_logs.sql: Show snapshot logs
@showsnapshots.sql: Show snapshots
@show_supp_logs.sql: Show supplemental logs for replication
@showdiscon.sql: Show all disabled constraints
@showdistrg.sql: Show all disabled triggers
@showlog.sql: Show redo logs
@show_logon_triggers.sql: Show logon triggers
@showindex.sql: Show indexes for a user
@showtab.sql: Show tables for a user
@showcol.sql: Show column details for OWNER.TABLE
@invalid.sql: Show invalid objects
@showinv.sql: soft link to invalid.sql
@shownls.sql: Show database NLS parameters
@showview.sql: Show the text for views - opens up view.txt in editor
@showdblink.sql: Show database links
@showdis.sql: Show disabled constraints
@showkey.sql: Show primary and unique keys and unique indexes for a table
@showmem.sql: Show memory usage per session
@showobjprivs.sql: Show privileges granted on an owners objects
@showpin.sql: Show objects pinned in the shared pool
@showpipes.sql: Show database pipes
@showsrc.sql: show source of PL/SQL stored objects
@show-x-dollar-tables.sql: list of all x$tables
@sql-version-counts.sql: top 10 count of versions of SQL_ID
@tabcols.sql: list of columns in alpha order for owner and table_name
@tabidx.sql: show indexes and columns for owner and table_name
@plsql_called_objects.sql: Shows entry PL/SQL object and current PL/SQL object for a session
@plsql-init.sql: example initialization for PL/SQL flags
@rbs_no_optimal.sql: Set all rollback segments to have no OPTIMAL size
@rbs_optimal.sql: Set all rollback segments to have an OPTIMAL size of 2xInitial
@rbs_shrink.sql: Shrink all rollback segments to OPTIMAL
@uifk.sql: Select from view creatdd in uifk_v.sql
@uifk_gen.sql: Uses the view created in uifk_v.sql to generate index DDL
@uifk_v.sql: Creates a view find all unindexed foreign key contraints
@showdb.sql: show database info
@show_active_log_dest.sql: show active log dest if available
@dba_recyclebin_purge_gen.sql: generate code to purge individual objects from dba_recyclebin
@restricted_session_disable.sql: everyone can login
@restricted_session_enable.sql: only DBA can login
@sess_longops.sql: query v$session_longops
@recompile.sql: Recompile invalid objects.  Still works better than DBMSU_UILITY.COMPILE_SCHEMA
@reverse_role_lookup.sql: Find all users granted a role
@undo-active.sql:  Show active undo blocks - RAC aware
@undo-active-12c.sql:  Show active undo blocks in 12c - RAC aware
@undo-mon-fast.sql: monitor undo from v$fast_start_transactions - useful for when a proccess/session has been killed
@undo-mon-trans.sql: monitor rollback for transactions
@unrevorable-files.sql: report of files that are unrecoverable, likely due to nologging inserts
@wait_chains.sql: Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1)

SNAPSHOTS and MATERIALIZED_VIEWS:

@show_mview_status.sql: show status from dba_mview_analysis
@showregistered_snapshots.sql: Show all snapshots registered at master site
@deregister_snapshots.sql: Degister a snapshot - see script comments
@showsnapshot_logs.sql: Show snapshot/mview logs
@showsnapshot_sites.sql: run from the master site-shows databases that have snapshots based on-tables/logs in master database
@showsnapshots.sql: Show snapshots/mviews in database

SECURITY:

@dba_table_audit_flags.sql: This script creates a SYS view against SYS tables  to show all audit flags per object
@show_session_audit.sql: select all from session_audit - lots of rows
@getaud.sql: generate SQL to reproduce current audit settings
@privmaps.sql: Show all privileges granted to a user, and whether direct or through a role
@orapwdhash.sql: Determine the 10g password hash for username and password.  Good for detecting accounts where username = password

STORAGE:

@dfshrink-gen-9i.sql: report of space savings by shrinking datafiles - generate df shrink code
@dfshrink-gen.sql: generate code to shrink datafiles - improved script for 10g+
@dbms_space_asa_rpt.sql: Show report from Auto Space Advisor
@showdf.sql: Show all database tablespace files and file info
@showdf8i.sql: Show all database tablespace files and file info oracle 8i
@showdf7.sql: Show all database tablespace files and file info oracle 7
@showfreemax.sql: Show size of maximum chunk of free space per tablespace
@showfree.sql: Show all free space per tablespace
@showfreesum.sql: Show sum of all free space per tablespace
@showtbs.sql: Show all tablespaces and info
@showspace.sql: Use DBMS_SPACE to display space stats for an object
@maxext3.sql: Locates database objects that will be unable to extend based on next extent size and available space, and/or due to maximum number of extents.
@undo_blocks_required.sql: calculate the number bytes of UNDO space required to satisfy the undo requirements based on the UNDO_RETENTION paramter (seconds), block size  and UNDO block requests per second
@undo_retention_available.sql: calculate how long undo retention should be good for based on the the bytes available in the UNDO tablespace  block size and UNDO block requests per second
@undo_stats.sql: used to see if ORA-1555 occurred. also shows maxquerylen and undo_retention - should not be ora-1555 if maxquerylen lt undo_retention

ASM:

@asm_copyblock.sql: copy ASM blocks to an datafile format file
@asm_disks.sql: show ASM disks
@asm_disk_errors.sql: show ASM disk errors
@asm_disk_stats.sql: show ASM disk statistics
@asm_diskgroups.sql: show diskgroups
@asm_diskgroup_attributes.sql: show diskgroup attributes
@asm_diskgroup_templates.sql: show diskgroup template values
@asm_failgroup_members.sql: show diskgroups by failgroup and members
@asm_extent_distribution.sql: show extent distribution across disks
@asm_files.sql: show files in ASM
@asm_files_path.sql: show files in ASM with full path
@asm_extent_multi_au.sql: show asm file extents that have AU count GT 1
@asm_partners.sql: show ASM disk partners - must be run from ASM instance

DRCP: Database Resident Connection Pooling

@drcp_show_config.sql: show current DRCP config
@drcp_set_connections_per_broker.sql: set number of connections managed per broker
@drcp_set_num_brokers.sql: set the number of DRCP brokers
@drcp_pool_cc_stats.sql: show connection class statistics
@drcp_pool_ratio.sql: show ratio of connection requests to number of pools
@drcp_pool_stats.sql: show aggregate DRCP pool stats
@drcp_start.sql: start DRCP
@drcp_stop.sql: stop DRCP
@whocp.sql: like who2.sql - includes DRCP service name

DATES: Dates and Date Math

@between-trunc-demo.sql: demo of using dates and timestamps with BETWEEN or similar so that indexes can be used
@date_math.sql: how to get the minutes between to dates of the same day
@date_math_2.sql: how to get the minutes between to dates of the same day
@date_math_3.sql: cause a job to run at exactly 00:30 or 12:30, regardless of the last time it started
@date_math_4.sql: round timestamps to previous interval of N minutes
@date_math_epoch.sql: get epoch to the millisecond using timestamp
@job_submit.sql: controlling run_time of dbms_jobs
@e2ts.sql: Convert epoch value to oracle timestamp
@e2ts-hires.sql: Convert epoch value to oracle timestamp
@timestamp_to_millisecond.sql: convert timestamp to millisecond demo
@timestamp-day-boundaries.sql: determine the beginning and ending timestamps for a day in SQL and PL/SQL
@timestamp-diff-seconds.sql: convert the difference between 2 timestamps to seconds. Preserves fractional seconds
@timestamp-trunc.sql: demonstrates how to truncate a timestamp to remove the time portion
@timestamp-types.sql: simple demo of timestamp data types via dump()
@ts2e.sql: Convert oracle timestamp to epoch value
@ts2e-hires.sql: Convert oracle timestamp to epoch value

MEMORY: Memory Settings and/or Advisors

@db_cache_advice.sql: run db cache advisor
@mem-leak-detect.sql: discover sessions that may be leaking memory
@mem-subpool-mgt.sql: parameters used to manage memory subpools - requires SYSDBA
@ora-4031-info-shared-pool.sql: displays several memory related configuration settings
@pgacols.sql: column formatting
@pga_advice.sql: run pga cache advisor
@pga_advice_hist.sql: pga cached advice history
@pga_advice_selective.sql: reports on pga cache advice only if min_pct gains achieved
@pga_history_sum.sql: pga cached advice summary
@pga_history_week.sql: pga history per week
@pga_workarea_active.sql: show active pga workareas
@pga_workarea_hist.sql: history of active pga workarea
@pgastat.sql: PGA stats from gv$pgastat
@pgastat_hist.sql: PGA stats from dba_hist_pgastat
@sga_advice_selective.sql: reports on sga cache advice only if min_pct gains achieved
@shared_pool_advice.sql: shared pool advisor
@shared_pool_advice_selective.sql: reports on shared pool advice only if min_pct gains achieved


METRICS: Metrics reported by oracle - v$sysmetric, v$sysmetric_history ...

@metric-names.sql: detail of metrics reported along with collection intervals
@os-load.sql: OS Load as reported by oracle for past hour


CDB-PDB: Scripts that are specific to Container and Pluggable databases

@pdb-modifiable-params-dump.sql: Dump the parameters from v$system_parameter on the CDB
@cdb_sched_jobs.sql: show all scheduler jobs from CDB Root Level

@show_container.sql: display the current container database name

XML: Scripts for use with XML and or XMLDB

@xmldb-status.sql: check status of XMLDB

#################