profile_from_awr.sql - create a SQL Profile from plan in AWR
distribution.sh - the script that builds the linux tar and windows zips files
rman-bkup-status.sql - Status of backups rman-bkup-details.sql - Details for a backup set
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
ascii.sql - generate a simple ascii table bad-date.sql - Oracle believes there is a year zero 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 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 timestamp_to_millisecond.sql - convert timestamp to millisecond demo timestamp-diff-seconds.sql - convert the difference between 2 timestamps to seconds. Preserves fractional seconds 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 - sql_trick_1.sql - demonstrates a very useful technique for conditionally executing SQL
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 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_fk_from-11.1.sql - generate existing foreign key constraings from data dictionary gen_fk_from-11.2.sql - generate existing foreign key constraings from data dictionary gen_fk_to-11.1.sql - generate existing foreign key constraings from data dictionary gen_fk_to-11.2.sql - generate existing foreign key constraings from data dictionary gen_data_with_recursion.sql - use a recursive subfactored query to generate rows gen_list_data_with_dual.sql - generating test data with dual gen_list_data_without_dual.sql - generating test data without dual - 10g+ set_events.sql - various methods to set events, including per sql_id 10046.sql - Set event 10046 in a session 10046_off.sql - Stop event 10046 in a session troff.sql - Turn off SQL tracing for all sessions of an account tron.sql - Turn on SQL tracing for all sessions of an account oradebug_doc.sql - dump the documentation for oradebug 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 user_ddl.sql - Generate SQL script to duplicate a database user using DBMS_METADATA hwm-df.sql - Find the high water mark for each datafile and determine how much each file can be shrunk dbms_system_undoc_calls.sql - some undocumented dbms_system calls - how to write to alert.log 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 table_ddl.sql - generate DDL for owner.table, with indexes, constraints, etc 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
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
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 showtrans.sql - Show current transactions with IO trans_per_hour.sql - Transactions per hour with statistics per xaction
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
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 snapper.sql - Tanel Poder script extraordinaire snapper4.sql - Tanel Poder script extraordinaire - version 4
aas-calc.sql - dump calculated AAS to CSV file aas-std.sql - dump AAS 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-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_log_sync.sql - log sync events 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-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-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-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-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
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 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
check_events.sql - Determine if any events are set in database getallparm.sql - get parameters including hidden getparm.sql - get parameters 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 parms_dump_csv.sql - Dump all parameters to CSV file sys_context.sql - Demo of getting oracle environment settings with sys_context function
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
bulk-collect-1.sql - demo of fetch .. bulk collect into raise_error.sql - raise any error in the database 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! 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
chk4incremental.sql - check to see if incremental stats were gathered for a table 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 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_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 disable_resource_manager.sql - the right method to disable the resource manager 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
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
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 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 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+ 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 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 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 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_data_types.sql - Show non-system column data types show_fk.sql - Show foreign keys for a user showpk.sql - Show all primary keys 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 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 wait_chains.sql - Troubleshooting Database Contention With V$Wait_Chains (Doc ID 1428210.1)
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
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
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_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_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
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 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-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
db_cache_advice.sql - run db cache advisor 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_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 shared_pool_advice.sql - shared pool advisor
metric-names.sql - detail of metrics reported along with collection intervals os-load.sql - OS Load as reported by oracle for past hour
show_container.sql - display the current container database name