You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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
idle-sessions-histogram.sql - show histogram of idle users in 10 second buckets
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
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
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
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
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_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