| NAME | DESCRIPTION | USAGE |
| ash_index_helper.sql | Santa's Little (Index) Helper BETA | @ash/ash_index_helper <sql_id> [<owner>.]<table_name> <from_time> <to_time>&@ash/ash_index_helper 8zz6y2yzdqjp0 %.% sysdate-1/24 sysdate&@ash/ash_index_helper % TPCDS.% sysdate-1/24 sysdate |
| ash_wait_chains.sql | Display ASH wait chains (multi-session wait signature, a session waiting for another session etc.) | @ash/ash_wait_chains <grouping_cols> <filters> <from_time> <to_time>&@ash/ash_wait_chains username||'-'||program2 "wait_class='Application'" sysdate-1/24 sysdate |
| ashtop.sql | Display top activity by grouping ASH columns | @ash/ashtop <grouping_cols> <filters> <from_time> <to_time>&@ash/ashtop username,sql_opname,event2 1=1 sysdate-1/24 sysdate&@ash/ashtop sql_opname,event2,sql_plan_operation||chr(32)||sql_plan_options,objt 1=1 sysdate-1/24 sysdate |
| asqlmon.sql | Report SQL-monitoring-style drill-down into where in an execution plan the execution time is spent (ASH based) | @ash/asqlmon <sql_id> <child#> <from_time> <to_time>&@ash/asqlmon 7q729nhdgtsqq 0 sysdate-1/24 sysdate&@ash/asqlmon 7q729nhdgtsqq % sysdate-1 sysdate |
| aw.sql | Display last minute database activity | @aw <filter_expression>&@aw 1=1 |
| awr_sqlid.sql | Display SQL text from AWR | @awr/awr_sqlid <sql_id>&@awr/awr_sqlid 7q729nhdgtsqq |
| awr_sqlstats.sql | Display SQL statistics from AWR | @awr/awr_sqlstats <sql_id> <plan_hash_value> <from_time> <to_time>&@awr/awr_sqlstats 0sh0fn7r21020 1541789278 sysdate-7 sysdate&@awr/awr_sqlstats 0sh0fn7r21020 % sysdate-7 sysdate |
| awr_sqlstats_per_exec.sql | Display SQL statistics per execution from AWR | @awr/awr_sqlstats_per_exec <sql_id> <plan_hash_value> <from_time> <to_time>&@awr/awr_sqlstats_per_exec 0sh0fn7r21020 1541789278 sysdate-7 sysdate&@awr/awr_sqlstats_per_exec 0sh0fn7r21020 % sysdate-7 sysdate |
| awr_sqlstats_unstable.sql | Display unstable SQL execution plans from AWR | @awr/awr_sqlstats_unstable <group_by_expr1> <group_by_expr2> <from_time> <to_time>&@awr/awr_sqlstats_unstable force_matching_signature plan_hash_value sysdate-7 sysdate |
| bg.sql | Display background processes | @bg <process_name|process_description>&@bg dbw&@bg writer&@bg % |
| bhobjects.sql | Display top objects in buffer cache | @bhobjects |
| bhobjects2.sql | Display buffer cache statistics | @bhobjects2 |
| cancel.sql | Generate commands for canceling selected SQL | @cancel <filter_expression>&@cancel sid=150&@cancel username='SYSTEM'&@cancel "username='APP' and program like 'sqlplus%'" |
| col.sql | Display column | @col <column_name>&@col open_mode |
| colusage.sql | Display column usage | @colusage [<owner>.]<table_name>&@colusage soe.orders&@colusage soe.% |
| create_sql_baseline.sql | Create SQL Plan Baseline from an existing "good" cursor | @create_sql_baseline <good_sqlid> <good_plan_hash_value> <to_bad_sqlid>&@create_sql_baseline g5tuxh82pk3qf 2966233522 d7khnbh6c9qas |
| create_sql_patch.sql | Create SQL patch | @create_sql_patch <sql_id> <hint>&@create_sql_patch g4pkmrqrgxg3b GATHER_PLAN_STATISTICS&@create_sql_patch b9dmj0ahu6xgc 'NO_INDEX_SS(@"SEL$26CA4453" "STORE_SALES"@"SEL$1")' |
| d.sql | Display data dictionary views and x$ tables | @d <object_name>&@d sql&@d % |
| dash_wait_chains.sql | Display ASH (based on DBA_HIST) wait chains (multi-session wait signature, a session waiting for another session etc.) | @ash/dash_wait_chains <grouping_cols> <filters> <from_time> <to_time>&@ash/dash_wait_chains username||'-'||program2 "wait_class='Application'" sysdate-1/24 sysdate |
| dashtop.sql | Display top activity by grouping ASH columns (based on DBA_HIST) | @ash/dashtop <grouping_cols> <filters> <from_time> <to_time>&@ash/dashtop username,sql_opname,event2 1=1 sysdate-1/24 sysdate&@ash/dashtop sql_opname,event2,sql_plan_operation||chr(32)||sql_plan_options,objt 1=1 sysdate-1 sysdate |
| dasqlmon.sql | Report SQL-monitoring-style drill-down into where in an execution plan the execution time is spent (AWR based) | @ash/dasqlmon <sqlid> <plan_hash_value> <from_time> <to_time>&@ash/dasqlmon 7q729nhdgtsqq 0 "timestamp'2019-10-07 07:00:00'" "timestamp'2019-10-07 07:00:00'"&@ash/dasqlmon 7q729nhdgtsqq % sysdate-1 sysdate |
| date.sql | Display current date | @date&@d sql&@d % |
| ddl.sql | Extracts DDL statements for specified objects | @ddl [<owner>.]<object_name>&@ddl sys.dba_users&@ddl sys.%tab% |
| desc.sql | Describe object | @desc <object_name>&@desc dba_tables |
| devent_hist.sql | Display a histogram of the number of waits from AWR (milliseconds) | @ash/devent_hist.sql <event> <filter_expression> <from_time> <to_time>&@ash/devent_hist.sql log_file 1=1 sysdate-1/24 sysdate&@ash/devent_hist.sql log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysdate |
| df.sql | Display tablespace usage (GB) | @df |
| dfm.sql | Display tablespace usage (MB) | @dfm |
| dirs.sql | Display database directories | @dirs |
| drop_sql_baseline.sql | Drop SQL Plan Baseline | @drop_sql_baseline <sql_handle>?? (get sql_handle from DBMS_XPLAN notes or DBA_SQL_PLAN_BASELINES)&@drop_sql_baseline SQL_52cb74b7097edbbd |
| drop_sql_patch.sql | Drop SQL patch | @drop_sql_patch <patch_name>&@drop_sql_patch SQL_PATCH_g4pkmrqrgxg3b |
| ev.sql | Set session event | @ev <event> <level>&@ev 10046 12 |
| event_hist.sql | Display a histogram of the number of waits from ASH (milliseconds) | @ash/event_hist.sql <event> <filter_expression> <from_time> <to_time>&@ash/event_hist.sql log.file 1=1 sysdate-1/24 sysdate&@ash/event_hist.sql log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysdate |
| event_hist_micro.sql | Display a histogram of the number of waits from ASH (microseconds) | @ash/event_hist_micro <event> <filter_expression> <from_time> <to_time>&@ash/event_hist_micro log.file 1=1 sysdate-1/24 sysdate&@ash/event_hist_micro log.file|db.file "wait_class='User I/O' AND session_type='FOREGROUND'" sysdate-1/24 sysdate |
| evh.sql | Display a histogram of the number of waits | @evh <event>&@evh log.file&@evh log.file|db.file |
| evo.sql | Disable session event | @evo <event>&@evo 10046 |
| fix.sql | Display fix controls description | @fix <bugno|description|optimizer_feature_enable|sql_feature>&@fix 13836796&@fix adaptive |
| grp.sql | Group function wrapper | @grp <column_name> <table_name>&@grp owner dba_tables&@grp owner,object_type dba_objects |
| hash.sql | Display the hash value, sql_id, and child number of the last SQL in session | @hash |
| help.sql | Display TPT script help | @help <search_expression>&@help explain&@help lock|latch.*hold&@help ^ind.*sql|^tab.*sql |
| hint.sql | Display all available hints | @hint <name>&@hint full |
| hintclass.sql | Display all available hints with hint class info | @hintclass <hint_name>&@hintclass merge |
| hintfeature.sql | Display all available hints with SQL feature info | @hintfeature <feature_name>&@hintfeature transformation |
| hinth.sql | Display hint hierarchy | @hinth <hint_name>&@hinth merge |
| ind.sql | Display indexes | @ind [<owner>.]<index_name|table_name>&@ind orders&@ind soe.ord_customer_ix&@ind soe.% |
| indf.sql | Display function-based index expressions | @indf [<owner>.]<index_name|table_name>&@indf orders&@indf soe.ord_customer_ix&@indf soe.% |
| kill.sql | Generate command to for killing user session | @kill <filter_expression>&@kill sid=284&@kill username='SYSTEM'&@kill "username='APP' AND program LIKE 'sqlplus%'" |
| latchprof.sql | Profile top latch holders (V$ version) | @latchprof <grouping_columns> <sid> <latch_name> <samples>&@latchprof name,sqlid 123 % 10000&@latchprof sid,name,sqlid % "shared pool" 10000 |
| latchprofx.sql | Profile top latch holders eXtended (X$ version) | @latchprofx <grouping_columns> <sid> <latch_name> <samples>&@latchprofx sid,name 123 % 10000&@latchprofx sid,name,timemodel,hmode,func % "shared pool" 10000 |
| lock.sql | Display current locks | @lock <filter_expression>&@lock 1=1&@lock type='TM' |
| log.sql | Display redo log layout | @log |
| long.sql | Display session long operations | @long <filter_expression>&@long 1=1&@long username='SOE' |
| ls.sql | Display tablespace | @ls <tablespace_name>&@ls system&@ls % |
| lt.sql | Display lock type info | @lt <lock_name>&@lt TM |
| mem.sql | Display information about the dynamic SGA components | @mem |
| memres.sql | Display information about the last completed memory resize operations | @memres |
| nls.sql | Display NLS parameters at session level | @nls |
| nonshared.sql | Display reasons for non-shared child cursors from v$shared_cursor | @nonshared <sql_id>&@nonshared 7q729nhdgtsqq |
| o.sql | Display database object based on object owner and name | @o [<owner>.]<object_name>&@o sys.dba_users&@o %.%files |
| oda.sql | Display oradebug doc event action | @oda <action>&@oddc latch&@oddc . |
| oddc.sql | Display oradebug doc component | @oddc <component>&@oddc optimizer&@oddc . |
| oerr.sql | Display Oracle error decription | @oerr <error_number>&@oerr 7445 |
| oi.sql | Display invalid objects | @oi |
| oid.sql | Display database objects based on object id | @oid <object_id>&@oid 10&@oid 10,20 |
| otherxml.sql | Display outline hints from library cache | @otherxml <sql_id> <child#>&@otherxml 1fbwxvngasv1f 1 |
| p.sql | Display parameter name and value | @p <parameter_name>&@pd optimizer |
| partkeys.sql | Display table partition keys | @partkeys [<owner>.]<table_name>&@partkeys soe.orders&@partkeys soe.% |
| pd.sql | Display parameter name, description and value | @pd <parameter_description>&@pd optimizer |
| pga.sql | Display PGA memory usage statistics | @pga |
| pmem.sql | Display process memory usage | @pmem <spid>&@pmem 1000 |
| proc.sql | Display functions and procedures | @proc <object_name> <procedure_name>&@proc dbms_stats table&@proc dbms_stats % |
| procid.sql | Display functions and procedures | @procid <object_id> <subprogram_id>&@procid 13615 84 |
| pv.sql | Display parameters based on the current value | @pv <value>&@pv MANUAL |
| pvalid.sql | Display valid parameter values | @pvalid <parameter_name>&@pvalid optimizer |
| rowid.sql | Display file, block, row numbers from rowid | @rowid <rowid>&@rowid AAAR51AAMAAAACGAAB |
| s.sql | Display current session wait and SQL_ID info (10g+) | @s <sid>&@s 52,110,225&@s "select sid from v$session where username = 'XYZ'"&@s mysid |
| sdr.sql | Control direct read in serial (_serial_direct_read) | @sdr <TRUE|FALSE> |
| se.sql | Display session events | @se <sid>&@se 10 |
| sed.sql | Display wait events description | @sed <event>&@sed log_file&@sed "enq: TX" |
| seg.sql | Display segment information | @seg [<owner>.]<segment_name>&@seg soe.customers&@seg soe.% |
| segcached.sql | Display number of buffered blocks of a segment | @segcached [<owner>.]<object_name>&@segcached soe.orders&@segcached soe.% |
| seq.sql | Display sequence information | @seq [<owner>.]<sequence_name>&@seq sys.jobseq&@seq %.jobseq |
| ses.sql | Display session statistics for given sessions, filter by statistic name | @ses <sid> <statname>&@ses 10 %&@ses 10 parse&@ses 10,11,12 redo&@ses "select sid from v$session where username = 'APPS'" parse |
| ses2.sql | Display session statistics for given sessions, filter by statistic name and show only stats with value > 0 | @ses2 <sid> <statname>&@ses2 10 %&@ses2 10 parse&@ses2 10,11,12 redo&@ses2 "select sid from v$ses2sion where username = 'APPS'" parse |
| settings.sql | Display AWR configuration | @awr/settings |
| sga.sql | Display instance memory usage breakdown from v$memory_dynamic_components | @sga |
| sgai.sql | Display instance memory usage breakdown from v$sgainfo | @sgai |
| sgares.sql | Display information about the last completed SGA resize operations from v$sga_resize_ops | @sgares |
| sgastat.sql | Display detailed information on the SGA from v$sgastat | @sgastat <name|pool>&@sgastat %&@sgastat result |
| sgastatx.sql | Display shared pool stats by sub-pool from X$KSMSS | @sgastatx <statistic_name>&@sgastatx "free memory"&@sgastatx cursor |
| sl.sql | Set statistics level | @sl <statistics_level>&@sl all |
| smem.sql | Display process memory usage | @smem <sid>&@smem 1000 |
| sqlbinds.sql | Display captured SQL bind variable values | @sqlbinds <sql_id> <child_number>&@sqlbinds 2swu3tn1ujzq7 0&@sqlbinds 2swu3tn1ujzq7 % |
| sqlf.sql | Display full sql text from memory | @sqlf <sql_id>&@sqlf 7q729nhdgtsqq |
| sqlfn.sql | Display SQL functions | @sqlf <name>&@sqlfn date |
| sqlid.sql | Display SQL: text, child cursors and execution statistics | @sqlid <sql_id> <child_number>&@sqlid 7q729nhdgtsqq 0&@sqlid 7q729nhdgtsqq % |
| sqlmon.sql | Run SQL Monitor report | @sqlmon <sid>&@sqlmon 1019 |
| syn.sql | Display synonym information | @syn [<owner>.]<synonym_name>&@syn system.tab&@syn system.% |
| sys.sql | Display system statistics | @sys <statistic_name>&@sys redo&@sys 'redo write' |
| t.sql | Display default trace file | @t |
| tab.sql | Display table information | @tab [<owner>.]<table_name>&@tab soe.orders&@tab soe.% |
| tabhist.sql | Display column histograms | @tabhist [<owner>.]<table_name> <column_name>&@tabhist soe.orders order_mode&@tabhist soe.orders % |
| tabpart.sql | Display table partitions | @tabpart [<owner>.]<table_name>&@tabpart soe.orders&@tabpart soe.% |
| tabsubpart | Display table subpartitions | @tabsubpart [<owner>.]<table_name>&@tabsubpart soe.orders&@tabsubpart soe.% |
| ti.sql | Force new trace file | @ti |
| tlc.sql | Display top-level call names | @tlc <call_name>&@tlc commit |
| topseg.sql | Display top space users per tablespace | @topseg <tablespace_name>&@topseg soe&@topseg % |
| topsegstat.sql | Display information about top segment-level statistics | @topsegstat <statistic_name>&@topsegstat reads&@topsegstat % |
| trace.sql | Enable tracing | @trace <filter_expression>&@trace sid=123&@trace username='SOE' |
| traceme.sql | Enable tracing for the current session | @traceme |
| traceoff.sql | Disable tracing | @traceoff <filter_expression>&@traceoff sid=123&@traceoff username='SOE' |
| trans.sql | Display active transactions | @trans |
| trig.sql | Display trigger information | @trig [<owner>.]<trigger_name>&@trig sys.delete_entries&@trig sys.% |
| ts.sql | Display tablespaces | @ts <tablespace_name>&@ts soe&@ts % |
| uds.sql | Display undo statistics | @uds |
| us.sql | Display database usernames from dba_users | @us <username>&@us username |
| usid.sql | Display user sessoin and process information | @usid <sid>&@us 1234 |
| uu.sql | Display user sessions | @uu <username>&@uu %&@uu username&@uu %username% |
| wrka.sql | Display PGA and TEMP usage | @wrka <fileter_expression>&@wrka 1=1&@wrka sid=1000 |
| wrkasum.sql | Display summary of SQL workareas groupbed by opertion type (PGA and TEMP) | @wrkasum <filter_expression>&@wrkasum sql_id='7q729nhdgtsqq' |
| x.sql | Display SQL execution plan for the last SQL statement | @x |
| xa.sql | Display SQL execution plan for the last SQL statement - alias | @xa |
| xall.sql | Display SQL execution plan for the last SQL statement - advanced | @xall |
| xawr.sql | Display SQL execution plan from AWR | @xawr <sql_id> <plan_hash_value>&@xawr 0sh0fn7r21020 1541789278&@xawr 0sh0fn7r21020 % |
| xb.sql | Explain a SQL statements execution plan with execution profile directly from library cache - for the last SQL executed in current session | @xb |
| xbi.sql | Explain a SQL statements execution plan with execution profile directly from library cache - look up by SQL ID | @xbi <sql_id> <sql_child_number>&@xbi a5ks9fhw2v9s1 0 |
| xi.sql | Display SQL execution plan from library cache | @xi <sql_id> <child#>&@xi 7q729nhdgtsqq 0&@xi 7q729nhdgtsqq % |
| xia.sql | Display SQL execution plan from library cache: ADVANCED | @xia <sql_id>&@xia 7q729nhdgtsqq |
| xp.sql | Run DBMS_SQLTUNE.REPORT_SQL_MONITOR (text mode) for session | @xp <session_id>&@xp 47 |
| xplto.sql | Display execution plan operations | @xplto <name>&@xplto full |
| xprof.sql | Run DBMS_SQLTUNE.REPORT_SQL_MONITOR for session | @xprof <report_level> <type> <sql_id|session_id> <sql_id|sid> |