-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathxplan_defines.sql
470 lines (433 loc) · 20.1 KB
/
xplan_defines.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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
--------------------------------------------------------------------------------
-- Author: Alberto Dell'Era
-- Copyright: (c) 2008-2021 Alberto Dell'Era http://www.adellera.it
--------------------------------------------------------------------------------
variable ERROR_BEFORE_MAIN_BLOCK varchar2(50 char)
variable CURRENT_ERROR varchar2(50 char)
exec /* xplan_exec_marker */ :CURRENT_ERROR := '';
-- set version defines, get parameters
variable V_DB_MAJOR_VERSION number
variable V_DB_MINOR_VERSION number
variable V_DB_VERSION varchar2(20 char)
variable V_DB_VERSION_COMPAT varchar2(20 char)
variable DB_NAME varchar2(50 char)
variable INSTANCE_NAME varchar2(50 char)
declare /* xplan_exec_marker */
l_dummy_bi1 binary_integer;
l_dummy_bi2 binary_integer;
l_version_dot_1 binary_integer;
l_version_dot_2 binary_integer;
begin
sys.dbms_utility.db_version (:V_DB_VERSION, :V_DB_VERSION_COMPAT);
l_version_dot_1 := instr (:V_DB_VERSION, '.');
l_version_dot_2 := instr (:V_DB_VERSION, '.', 1, 2);
:V_DB_MAJOR_VERSION := to_number (substr (:V_DB_VERSION, 1, l_version_dot_1 - 1));
:V_DB_MINOR_VERSION := to_number (substr (:V_DB_VERSION, l_version_dot_1+1, l_version_dot_2 - l_version_dot_1 - 1));
l_dummy_bi1 := sys.dbms_utility.get_parameter_value ('db_name' , l_dummy_bi2, :DB_NAME );
l_dummy_bi1 := sys.dbms_utility.get_parameter_value ('instance_name', l_dummy_bi2, :INSTANCE_NAME);
end;
/
-- set version-dependent commenting-out defines
define COMM_IF_LT_19C="error"
define COMM_IF_LT_18C="error"
define COMM_IF_LT_12CR2="error"
define COMM_IF_LT_12C="error"
define COMM_IF_LT_11GR2="error"
define COMM_IF_LT_11G="error"
define COMM_IF_LT_10GR2="error"
define COMM_IF_LT_10G="error"
define COMM_IF_GT_9I="error"
col COMM_IF_LT_19C noprint new_value COMM_IF_LT_19C
col COMM_IF_LT_18C noprint new_value COMM_IF_LT_18C
col COMM_IF_LT_12CR2 noprint new_value COMM_IF_LT_12CR2
col COMM_IF_LT_12C noprint new_value COMM_IF_LT_12C
col COMM_IF_LT_11GR2 noprint new_value COMM_IF_LT_11GR2
col COMM_IF_LT_11G noprint new_value COMM_IF_LT_11G
col COMM_IF_LT_10GR2 noprint new_value COMM_IF_LT_10GR2
col COMM_IF_LT_10G noprint new_value COMM_IF_LT_10G
col COMM_IF_GT_9I noprint new_value COMM_IF_GT_9I
col COMM_IF_GT_10G noprint new_value COMM_IF_GT_10G
col COMM_IF_GT_10GR1 noprint new_value COMM_IF_GT_10GR1
select /*+ xplan_exec_marker */
case when :v_db_major_version < 19 then '--' else '' end COMM_IF_LT_19C,
case when :v_db_major_version < 19 then '--' else '' end COMM_IF_LT_18C,
case when :v_db_major_version < 11 or (:v_db_major_version = 12 and :v_db_minor_version < 2) then '--' else '' end COMM_IF_LT_12CR2,
case when :v_db_major_version < 12 then '--' else '' end COMM_IF_LT_12C,
case when :v_db_major_version < 11 or (:v_db_major_version = 11 and :v_db_minor_version < 2) then '--' else '' end COMM_IF_LT_11GR2,
case when :v_db_major_version < 11 then '--' else '' end COMM_IF_LT_11G,
case when :v_db_major_version < 10 or (:v_db_major_version = 10 and :v_db_minor_version < 2) then '--' else '' end COMM_IF_LT_10GR2,
case when :v_db_major_version < 10 then '--' else '' end COMM_IF_LT_10G,
case when :v_db_major_version > 9 then '--' else '' end COMM_IF_GT_9I,
case when :v_db_major_version > 10 then '--' else '' end COMM_IF_GT_10G,
case when :v_db_major_version >= 11 or (:v_db_major_version = 10 and :v_db_minor_version >= 2) then '--' else '' end COMM_IF_GT_10GR1
from dual;
-- set servroutput size clause to max possible (+infinite in10g+)
define SERVEROUT_SIZE_CLAUSE="error"
col SERVEROUT_SIZE_CLAUSE noprint new_value SERVEROUT_SIZE_CLAUSE
select /*+ xplan_exec_marker */
case when :v_db_major_version < 10 then 'size 1000000' else 'size unlimited' end SERVEROUT_SIZE_CLAUSE
from dual;
-- set SQL_LIKE bind variable (10g handles single-quotes much better )
-- also, set :XPLAN_OPTIONS
variable XPLAN_OPTIONS varchar2(400 char)
exec /* xplan_exec_marker */ if :CURRENT_ERROR is null then :CURRENT_ERROR := 'sql_like invalid'; end if;
variable SQL_LIKE varchar2(4000)
begin /*+ xplan_exec_marker */
:SQL_LIKE :=
&COMM_IF_GT_9I. '&SQL_LIKE.' ;
&COMM_IF_LT_10G. q'|&SQL_LIKE.|';
:CURRENT_ERROR := '';
:XPLAN_OPTIONS := '&XPLAN_OPTIONS.';
end;
/
-- set options defines
variable OPT_INST_ID number
variable OPT_PLAN_STATS varchar2(10 char)
variable OPT_ACCESS_PREDICATES varchar2(1)
variable OPT_LINES number
variable OPT_ASH_PROFILE_MINS number
variable OPT_MODULE varchar2(100 char)
variable OPT_ACTION varchar2(100 char)
variable OPT_HASH_VALUE varchar2(50 char)
variable OPT_PLAN_HASH_VALUE varchar2(50 char)
variable OPT_SQL_ID varchar2(50 char)
variable OPT_PARSED_BY varchar2(128 char)
variable OPT_CALLED_BY varchar2(257 char)
variable OPT_LAST_ACTIVE varchar2(60 char)
variable OPT_CHILD_NUMBER number
variable OPT_DBMS_XPLAN varchar2(1)
variable OPT_DBMS_METADATA varchar2(3)
variable OPT_PLAN_DETAILS varchar2(1)
variable OPT_PLAN_ENV varchar2(1)
variable OPT_TABINFOS varchar2(6 char)
variable OPT_OBJINFOS varchar2(1)
variable OPT_PARTINFOS varchar2(1)
variable OPT_SELF varchar2(1)
variable OPT_ORDER_BY varchar2(100 char)
variable OPT_SPOOL_NAME varchar2(100 char)
variable OPT_SPOOL_FILES varchar2(50 char)
variable OPT_NUMBER_COMMAS varchar2(1)
variable OPT_COLORS varchar2(1)
exec /* xplan_exec_marker */ if :CURRENT_ERROR is null then :CURRENT_ERROR := 'processing XPLAN_OPTIONS'; end if;
declare /* xplan_exec_marker */ -- process options
l_opt_string varchar2(200 char) := :XPLAN_OPTIONS||',';
l_curr_opt_str varchar2(200 char);
l_first_colon int; l_first_eq int;
l_name varchar2(50 char);
l_value varchar2(200 char);
begin
if :CURRENT_ERROR != 'processing XPLAN_OPTIONS' then
raise_application_error (-20001, 'skipping due to previous error');
end if;
-- set defaults
:OPT_INST_ID := userenv('Instance');
:OPT_PLAN_STATS := 'raw';
:OPT_ACCESS_PREDICATES := 'Y';
:OPT_LINES := 250;
:OPT_ASH_PROFILE_MINS := null;
:OPT_MODULE := null;
:OPT_ACTION := null;
:OPT_HASH_VALUE := null;
:OPT_PLAN_HASH_VALUE := null;
:OPT_SQL_ID := null;
:OPT_PARSED_BY := null;
:OPT_CALLED_BY := null;
:OPT_LAST_ACTIVE := null;
:OPT_CHILD_NUMBER := null;
:OPT_DBMS_XPLAN := 'N';
:OPT_DBMS_METADATA := 'N';
:OPT_PLAN_DETAILS := 'N';
:OPT_PLAN_ENV := 'Y';
:OPT_TABINFOS := 'N';
:OPT_OBJINFOS := 'N';
:OPT_PARTINFOS := 'N';
:OPT_SELF := 'Y';
:OPT_ORDER_BY := '';
:OPT_SPOOL_NAME := null;
:OPT_SPOOL_FILES := null;
:OPT_NUMBER_COMMAS := 'Y';
:OPT_COLORS := 'Y';
-- override defaults from XPLAN_OPTIONS
loop
l_first_colon := instr (l_opt_string, ',');
exit when l_first_colon = 0 or l_first_colon is null;
l_curr_opt_str := substr (l_opt_string, 1, l_first_colon-1);
l_opt_string := substr (l_opt_string, l_first_colon+1);
if trim(l_curr_opt_str) is not null then
l_first_eq := instr (l_curr_opt_str, '=');
if l_first_eq <= 1 or l_first_eq is null then
raise_application_error (-20001, 'invalid option ="'||l_curr_opt_str||'".');
end if;
l_name := trim(lower(substr (l_curr_opt_str, 1, l_first_eq-1)));
l_value := trim(lower(substr (l_curr_opt_str, l_first_eq+1)));
if l_name is null then
raise_application_error (-20002, 'invalid option ="'||l_curr_opt_str||'".');
end if;
if l_name in ('inst_id','i') then
:OPT_INST_ID := to_number (l_value);
elsif l_name = 'plan_stats' then
if l_value in ('raw','per_exec','last') then
:OPT_PLAN_STATS := l_value;
else
raise_application_error (-20003, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name = 'access_predicates' then
if l_value in ('y','n') then
:OPT_ACCESS_PREDICATES := upper (l_value);
else
raise_application_error (-20004, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name in ('lines','linesize','l') then
:OPT_LINES := to_number (l_value);
elsif l_name = 'module' then
:OPT_MODULE := l_value;
elsif l_name = 'action' then
:OPT_ACTION := l_value;
elsif l_name in ('hash', 'hash_value') then
:OPT_HASH_VALUE := to_number (l_value);
elsif l_name in ('plan_hash', 'plan_hash_value', 'ph') then
:OPT_PLAN_HASH_VALUE := to_number (l_value);
elsif l_name = 'sql_id' then
:OPT_SQL_ID := trim(l_value);
&COMM_IF_GT_9I. if :OPT_SQL_ID is not null then raise_application_error (-20005, 'cannot use sql_id before 10g'); end if;
elsif l_name = 'parsed_by' then
:OPT_PARSED_BY := upper(l_value);
elsif l_name = 'called_by' then
:OPT_CALLED_BY := upper(trim(l_value));
if :OPT_CALLED_BY is null or instr(:OPT_CALLED_BY, '.') <= 1 then raise_application_error(-20006, 'invalid value "'||l_value||'" for option '||l_name||'.'); end if;
elsif l_name = 'last_active' then
:OPT_LAST_ACTIVE := upper(trim(l_value));
elsif l_name in ('child_number','cn') then
:OPT_CHILD_NUMBER := to_number (l_value);
elsif l_name = 'dbms_xplan' then
if l_value in ('y','n') then
:OPT_DBMS_XPLAN := upper (l_value);
&COMM_IF_GT_9I. if :OPT_DBMS_XPLAN = 'Y' then raise_application_error (-20007, 'cannot use dbms_xplan before 10g'); end if;
else
raise_application_error (-20008, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name = 'dbms_metadata' then
if l_value in ('y','n','all') then
:OPT_DBMS_METADATA := upper (l_value);
else
raise_application_error (-20009, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name in ('plan_details', 'pd') then
if l_value in ('y','n') then
:OPT_PLAN_DETAILS := upper (l_value);
&COMM_IF_GT_9I. if :OPT_PLAN_DETAILS = 'Y' then raise_application_error (-20008, 'cannot display plan_details before 10g'); end if;
else
raise_application_error (-20010, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name = 'plan_env' then
if l_value in ('y','n') then
:OPT_PLAN_ENV := upper (l_value);
else
raise_application_error (-20011, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name in ('ash_profile_mins','ash_profile_min') then
:OPT_ASH_PROFILE_MINS := to_number (l_value);
if :OPT_ASH_PROFILE_MINS >= 0 and :OPT_ASH_PROFILE_MINS = trunc (:OPT_ASH_PROFILE_MINS) then
null;
else
raise_application_error (-20012, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
&COMM_IF_GT_9I. if :OPT_ASH_PROFILE_MINS != 0 then raise_application_error (-20013, 'cannot use ASH before 10g'); end if;
elsif l_name in ('tabinfos', 'ti') then
if l_value in ('y','n','bottom') then
:OPT_TABINFOS := upper (l_value);
else
raise_application_error (-20014, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name in ('objinfos', 'oi') then
if l_value in ('y','n') then
:OPT_OBJINFOS := upper (l_value);
else
raise_application_error (-20015, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name in ('partinfos', 'pi') then
if l_value in ('y','n') then
:OPT_PARTINFOS := upper (l_value);
else
raise_application_error (-20016, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name = 'self' then
if l_value in ('y','n') then
:OPT_SELF := upper (l_value);
else
raise_application_error (-20017, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name = 'order_by' then
:OPT_ORDER_BY := replace (trim(l_value), ';', ',') || ',';
elsif l_name = 'numbers_with_comma' then
if l_value in ('y','n') then
:OPT_NUMBER_COMMAS := upper (l_value);
else
raise_application_error (-20018, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name in ('colors') then
if l_value in ('y','n') then
:OPT_COLORS := upper (l_value);
else
raise_application_error (-20019, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
elsif l_name = 'spool_name' then
:OPT_SPOOL_NAME := l_value;
if instr (:OPT_SPOOL_NAME, '.') = 0 then
:OPT_SPOOL_NAME := :OPT_SPOOL_NAME || '.lst';
end if;
elsif l_name = 'spool_files' then
if l_value in ('single', 'by_hash', 'by_sql_id') then
:OPT_SPOOL_FILES := l_value;
else
raise_application_error (-20020, 'invalid value "'||l_value||'" for option '||l_name||'.');
end if;
&COMM_IF_GT_9I. if :OPT_SPOOL_FILES = 'by_sql_id' then raise_application_error (-20017, 'cannot name files using sql_id before 10g'); end if;
else
raise_application_error (-20099, 'invalid option name for "'||l_curr_opt_str||'".');
end if;
end if;
end loop;
-- handle ash_profile_mins not set
&COMM_IF_LT_10G. if :OPT_ASH_PROFILE_MINS is null then
&COMM_IF_LT_10G. :OPT_ASH_PROFILE_MINS := 1440;
&COMM_IF_LT_10G. end if;
-- handle spool_files not set
if :OPT_SPOOL_FILES is null then
if :OPT_SQL_ID is not null then
:OPT_SPOOL_FILES := 'by_sql_id';
elsif :OPT_HASH_VALUE is not null then
:OPT_SPOOL_FILES := 'by_hash';
else
:OPT_SPOOL_FILES := 'single';
end if;
end if;
-- handle spool_name not set
if :OPT_SPOOL_NAME is null then
if :OPT_SPOOL_FILES = 'single' then
:OPT_SPOOL_NAME := 'xplan'||'_i'||:OPT_INST_ID||'.lst';
else
:OPT_SPOOL_NAME := 'xplan.lst';
end if;
end if;
:CURRENT_ERROR := null;
end;
/
-- print current options values
variable CURRENT_XPLAN_OPTIONS varchar2(500 char)
begin
select /*+ xplan_exec_marker */
'inst_id=' || :OPT_INST_ID
|| ' plan_stats='||:OPT_PLAN_STATS
|| ' access_predicates='||:OPT_ACCESS_PREDICATES
|| ' lines='||:OPT_LINES
|| ' ash_profile_mins='||:OPT_ASH_PROFILE_MINS
|| ' module='||:OPT_MODULE
|| ' action='||:OPT_ACTION
|| ' hash='||:OPT_HASH_VALUE
|| ' plan_hash='||:OPT_PLAN_HASH_VALUE
|| ' sql_id='||:OPT_SQL_ID
|| ' parsed_by='||:OPT_PARSED_BY
|| ' called_by='||:OPT_CALLED_BY
|| ' last_active='||:OPT_LAST_ACTIVE
|| ' child_number='||:OPT_CHILD_NUMBER
|| ' dbms_xplan='||:OPT_DBMS_XPLAN
|| ' dbms_metadata='||:OPT_DBMS_METADATA
|| ' plan_details='||:OPT_PLAN_DETAILS
|| ' plan_env='||:OPT_PLAN_ENV
|| ' tabinfos='||:OPT_TABINFOS
|| ' objinfos='||:OPT_OBJINFOS
|| ' partinfos='||:OPT_PARTINFOS
|| ' self='||:OPT_SELF
|| ' order_by='||:OPT_ORDER_BY
|| ' numbers_with_comma='||:OPT_NUMBER_COMMAS
|| ' colors='||:OPT_COLORS
|| ' spool_name='||:OPT_SPOOL_NAME
|| ' spool_files='||:OPT_SPOOL_FILES
into :CURRENT_XPLAN_OPTIONS
from dual;
end;
/
-- set internal defines
define PLAN_LAST_OR_NULL="error"
col PLAN_LAST_OR_NULL noprint new_value PLAN_LAST_OR_NULL
select /*+ xplan_exec_marker */ case when :OPT_PLAN_STATS = 'last' then 'LAST_' else null end as PLAN_LAST_OR_NULL from dual;
define PLAN_AVG_PER_EXEC="error"
col PLAN_AVG_PER_EXEC noprint new_value PLAN_AVG_PER_EXEC
select /*+ xplan_exec_marker */ case when :OPT_PLAN_STATS = 'per_exec' then 'Y' else 'N' end as PLAN_AVG_PER_EXEC from dual;
define COMM_IF_NO_PREDS="error"
col COMM_IF_NO_PREDS noprint new_value COMM_IF_NO_PREDS
select /*+ xplan_exec_marker */ case when :OPT_ACCESS_PREDICATES = 'Y' then '' else '--' end as COMM_IF_NO_PREDS from dual;
define COMM_IF_NO_DBMS_XPLAN="error"
col COMM_IF_NO_DBMS_XPLAN noprint new_value COMM_IF_NO_DBMS_XPLAN
select /*+ xplan_exec_marker */ case when :OPT_DBMS_XPLAN = 'Y' then '' else '--' end as COMM_IF_NO_DBMS_XPLAN from dual;
define COMM_IF_NO_DBMS_METADATA="error"
col COMM_IF_NO_DBMS_METADATA noprint new_value COMM_IF_NO_DBMS_METADATA
select /*+ METADATA_exec_marker */ case when :OPT_DBMS_METADATA != 'N' then '' else '--' end as COMM_IF_NO_DBMS_METADATA from dual;
define COMM_IF_NO_HASH="error"
col COMM_IF_NO_HASH noprint new_value COMM_IF_NO_HASH
select /*+ xplan_exec_marker */ case when :OPT_HASH_VALUE is not null then '' else '--' end as COMM_IF_NO_HASH from dual;
define COMM_IF_NO_PLAN_HASH="error"
col COMM_IF_NO_PLAN_HASH noprint new_value COMM_IF_NO_PLAN_HASH
select /*+ xplan_exec_marker */ case when :OPT_PLAN_HASH_VALUE is not null then '' else '--' end as COMM_IF_NO_PLAN_HASH from dual;
define COMM_IF_NO_SELF="error"
col COMM_IF_NO_SELF noprint new_value COMM_IF_NO_SELF
select /*+ xplan_exec_marker */ case when :OPT_SELF = 'Y' then '' else '--' end as COMM_IF_NO_SELF from dual;
define COMM_IF_NO_SQL_LIKE="error"
col COMM_IF_NO_SQL_LIKE noprint new_value COMM_IF_NO_SQL_LIKE
select /*+ xplan_exec_marker */
case when :SQL_LIKE is null or :SQL_LIKE = '%' then '--' else '' end as COMM_IF_NO_SQL_LIKE
from dual;
define MAIN_BLOCK_SPOOL="error"
define BOTTOM_SCRIPT="error"
col MAIN_BLOCK_SPOOL noprint new_value MAIN_BLOCK_SPOOL
col BOTTOM_SCRIPT noprint new_value BOTTOM_SCRIPT
select /*+ xplan_exec_marker */
case when :OPT_SPOOL_FILES = 'single' then :OPT_SPOOL_NAME else 'xplan_run.lst' end MAIN_BLOCK_SPOOL,
case when :OPT_SPOOL_FILES = 'single' then 'xplan_null_script.sql' else 'xplan_run.lst' end BOTTOM_SCRIPT
from dual;
define LINE_SIZE="error"
col LINE_SIZE noprint new_value LINE_SIZE
select /*+ xplan_exec_marker */
case when :OPT_SPOOL_FILES = 'single' then to_char(:OPT_LINES) else to_char(500) end as LINE_SIZE
from dual;
define MAIN_ORDER_BY="error"
col MAIN_ORDER_BY noprint new_value MAIN_ORDER_BY
select /*+ xplan_exec_marker */ :OPT_ORDER_BY as MAIN_ORDER_BY from dual;
define SPOOL_NAME="error"
col SPOOL_NAME noprint new_value SPOOL_NAME
select /*+ xplan_exec_marker */ to_char(:OPT_SPOOL_NAME) as SPOOL_NAME from dual;
define SPOOL_FILES="error"
col SPOOL_FILES noprint new_value SPOOL_FILES
select /*+ xplan_exec_marker */ to_char(:OPT_SPOOL_FILES) as SPOOL_FILES from dual;
variable MODULE_LIKE varchar2(100 char)
variable ACTION_LIKE varchar2(100 char)
exec /*+ xplan_exec_marker */ :MODULE_LIKE := :OPT_MODULE; :ACTION_LIKE := :OPT_ACTION;
-- note: each RAC instance can have its own undo tablespace
variable UNDO_DATAFILES_LIST varchar2(200)
begin /*+ xplan_exec_marker */
for f in (select /*+ xplan_exec_marker */ file_id
from dba_data_files
where tablespace_name in (select value from sys.gv_$parameter where name = 'undo_tablespace')
order by file_id )
loop
if :UNDO_DATAFILES_LIST is not null then
:UNDO_DATAFILES_LIST := :UNDO_DATAFILES_LIST || ',';
end if;
:UNDO_DATAFILES_LIST := :UNDO_DATAFILES_LIST || f.file_id;
end loop;
-- set to impossible value if undo info not available (it happens inside PDBs without "local" undo tablespaces)
if :UNDO_DATAFILES_LIST is null then
:UNDO_DATAFILES_LIST := -42.3;
end if;
end;
/
define UNDO_DATAFILES_LIST="error"
col UNDO_DATAFILES_LIST noprint new_value UNDO_DATAFILES_LIST
select /*+ xplan_exec_marker */ :UNDO_DATAFILES_LIST as UNDO_DATAFILES_LIST from dual;
define ERROR_BEFORE_MAIN_BLOCK=""
col ERROR_BEFORE_MAIN_BLOCK noprint new_value ERROR_BEFORE_MAIN_BLOCK
select /*+ xplan_exec_marker */ case when :CURRENT_ERROR is null then null
else ' *** error before main block ( '||:CURRENT_ERROR||' ) ***'
end as ERROR_BEFORE_MAIN_BLOCK
from dual;