57 lines
4.9 KiB
SQL
57 lines
4.9 KiB
SQL
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
|
|
--------------------------------------------------------------------------------
|
|
--
|
|
-- File name: help.sql
|
|
-- Purpose: Help
|
|
-- Author: Tomasz Sroka
|
|
-- Usage: @help <string>
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
--ACCEPT search_string CHAR PROMPT "Search: [%] "
|
|
|
|
DEFINE amp=chr(38)
|
|
DEFINE nl=chr(10)
|
|
DEFINE search_string=&1
|
|
|
|
COLUMN name FORMAT A30 TRUNC
|
|
COLUMN description FORMAT A60 WORD_WRAP
|
|
COLUMN usage FORMAT A115
|
|
|
|
WITH q AS (
|
|
SELECT name, description, usage
|
|
FROM (
|
|
SELECT 'stats_opls.sql' AS name, 'List stats operations' AS description, '@stats_opls <since> <until> <level> <format>'||&nl||'@stats_opls sysdate-14 sysdate BASIC TEXT'||&nl||'@stats_opls "timestamp''2023-01-12 14:00:00''" "timestamp''2023-02-12 14:00:00''" TYPICAL HTML' AS usage FROM dual UNION ALL
|
|
SELECT 'stats_opdet.sql' AS name, 'Display details of a stats operation' AS description, '@stats_opdet <operation_id> <level> <format>'||&nl||'@stats_opdet 1482'||&nl||'@stats_opdet 1482 TYPICAL HTML
|
|
' AS usage FROM dual UNION ALL
|
|
SELECT 'histo_endpoints.sql' AS name, 'List bucket details in histograms' AS description, '@histo_endpoints.sql <SCHEMA>.<TABLE_NAME> <COLUMN>' AS usage FROM dual UNION ALL
|
|
SELECT 'sqlmon_sqlid.sql' AS name, 'SQL Monitor reports list of sql_id' AS description, '@sqlmon_sqlid 8c2mb6gp8w8zy sysdate-1 sysdate'||&nl||'@sqlmon_sqlid 8c2mb6gp8w8zy "timestamp''2023-04-22 09:30:45''" "timestamp''2023-04-22 12:00:00''"' AS usage FROM dual UNION ALL
|
|
SELECT 'sqlmon_rep.sql' AS name, 'Display a SQL Monitor report' AS description, '@sqlmon_rep.sql <report_id> <report_type>(default=TEXT)'||&nl||'@sqlmon_rep.sql 553 '||&nl||'@sqlmon_rep.sql 553 HTML'||&nl||'@sqlmon_rep.sql 553 ACTIVE' AS usage FROM dual UNION ALL
|
|
SELECT 'stdby_redolog' AS name, 'List standby redolog status' AS description, '@stdby_redolog' AS usage FROM dual UNION ALL
|
|
SELECT 'mv_m_logtable.sql' AS name, '(matview) Log table of a master table' AS description, '@mv_m_logtable.sql <master_table>_owner> <master_table_name>' AS usage FROM dual UNION ALL
|
|
SELECT 'mv_m_refreshlog.sql' AS name, '(matview) Refresh history of a master table' AS description, '@mv_m_refreshlog.sql <master_table>_owner> <master_table_name>' AS usage FROM dual UNION ALL
|
|
SELECT 'mv_m_registeredsnap.sql' AS name, '(matview) Registered snapshots a master table' AS description, '@mv_m_registeredsnap.sql <master_table>_owner> <master_table_name>' AS usage FROM dual UNION ALL
|
|
SELECT 'awrsqlid.sql' AS name, 'sql_id AWR execution history' AS description, '@awrsqlid <sql_id> <snap_begin_time> <snap_end_time>' ||&nl||'@awrsqlid 2qr52n47vt2aj sysdate-1000 sysdate'||&nl||'@awrsqlid 2qr52n47vt2aj "timestamp''2023-06-10 02:30:00''" "timestamp''2023-06-12 02:30:00''"' AS usage FROM dual UNION ALL
|
|
SELECT 'monitoring/code_dep_on.sql' AS name, 'Displays all objects dependant on the specified object' AS description, '@monitoring/code_dep_on.sql <owner> <object_name>' AS usage FROM dual UNION ALL
|
|
SELECT 'monitoring/code_dep.sql' AS name, 'Displays all dependencies of specified object' AS description, '@monitoring/code_dep.sql <owner> <object_name>' AS usage FROM dual UNION ALL
|
|
SELECT 'transactions.sql' AS name, '(VG) Displays transactions' AS description, '@transactions <where clause>'||&nl||'@transactions "and 1=1"'||&nl||'@transactions "and sid=506"' AS usage FROM dual UNION ALL
|
|
SELECT 'transaction_long_running.sql' AS name, '(VG) Displays long transactions' AS description, '@transaction_long_running.sql <where clause>'||&nl||'@transaction_long_running "and 1=1"'||&nl||'@transaction_long_running "and sid=506"' AS usage FROM dual UNION ALL
|
|
SELECT 'racasqlmon.sql' AS name, '(RAC aware) Report SQL-monitoring-style drill-down into where in an execution plan the execution time is spent (ASH based)' AS description, '@racasqlmon <sql_id> <child#> <from_time> <to_time>'||&nl||'@racasqlmon 7q729nhdgtsqq 0 sysdate-1/24 sysdate'||&nl||'@racasqlmon 7q729nhdgtsqq % sysdate-1 sysdate' AS usage FROM dual UNION ALL
|
|
SELECT 'stats_history.sql' AS name, '(VG) Display table/index/partition statistics history' AS description, '@stats_history.sql USR <owner> <object_name> <partition_name> <object_type> '||&nl||'@stats_history.sql USR T1 % %'||&nl||'@stats_history.sql USR IDX_T1_BLOCKS % INDEX' AS usage FROM dual UNION ALL
|
|
SELECT '' AS name, '' AS description, '' AS usage FROM dual UNION ALL
|
|
SELECT '' AS name, '' AS description, '' AS usage FROM dual
|
|
)
|
|
)
|
|
SELECT * FROM q
|
|
WHERE
|
|
(upper(name) LIKE upper ('%&search_string%') OR regexp_like(name, '&search_string', 'i'))
|
|
OR (upper(description) LIKE upper ('%&search_string%') OR regexp_like(description, '&search_string', 'i'))
|
|
-- OR (upper(usage) LIKE upper ('%&search_string%') OR regexp_like(usage, '&search_string', 'i'))
|
|
ORDER BY
|
|
name
|
|
/
|
|
|
|
UNDEFINE search_string
|
|
CLEAR COLUMNS
|