-
Notifications
You must be signed in to change notification settings - Fork 5
/
patch_monitor_create.sql
71 lines (59 loc) · 1.71 KB
/
patch_monitor_create.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
----------------------------------------------------------------------------------------
--
-- File name: monitor_sql.sql
--
-- Purpose: Prompts for a ql_id and creates a patch with the monitor hint
-
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for one value.
--
-- sql_id: the sql_id of the statement to attach the patch to
-- (the statement must be in the shared pool)
--
--
-- See kerryosborne.oracle-guy.com for additional information.
-----------------------------------------------------------------------------------------
accept sql_id -
prompt 'Enter value for sql_id: ' -
default 'X0X0X0X0'
set feedback off
set sqlblanklines on
set serverout on format wrapped
declare
l_patch_name varchar2(30);
cl_sql_text clob;
l_category varchar2(30);
l_validate varchar2(3);
b_validate boolean;
begin
select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';
dbms_sqldiag_internal.i_create_patch(
sql_text => cl_sql_text,
hint_text => 'MONITOR',
name => 'MONITOR_PATCH_'||'&&sql_id',
category => 'DEFAULT',
validate => FALSE
);
dbms_output.put_line(' ');
dbms_output.put_line('SQL Patch '||l_patch_name||' created.');
dbms_output.put_line(' ');
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(' ');
dbms_output.put_line('ERROR: SQL_ID: '||'&&sql_id'||' does not exist in v$sqlarea.');
dbms_output.put_line('The SQL statement must be in the shared pool to use this script.');
dbms_output.put_line(' ');
end;
/
undef sql_id
set sqlblanklines off
set feedback on
set serverout off