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
| set long 5000
set serveroutput on
declare
id_tache varchar2(50);
resu clob;
begin
-- suppression de la tâche 'plantage'
dbms_sqldiag.drop_diagnosis_task('plantage');
-- création de la tâche d'analyse
id_tache:=dbms_sqldiag.create_diagnosis_task
(sql_text => Q'!WITH t AS (
SELECT 5 AS counter, to_date('23/02/2011','dd/mm/yyyy') AS time FROM dual union ALL
SELECT 6, to_date('23/02/2011','dd/mm/yyyy') FROM dual union ALL
SELECT 5, to_date('23/02/2011','dd/mm/yyyy') FROM dual union ALL
SELECT 12, to_date('23/02/2011','dd/mm/yyyy') FROM dual union ALL
SELECT 5, to_date('01/03/2011','dd/mm/yyyy') FROM dual union ALL
SELECT 36, to_date('01/03/2011','dd/mm/yyyy') FROM dual union ALL
SELECT 5, to_date('01/03/2011','dd/mm/yyyy') FROM dual union ALL
SELECT 5, to_date('01/03/2011','dd/mm/yyyy') FROM dual union ALL
SELECT 8, to_date('01/03/2011','dd/mm/yyyy') FROM dual
)
SELECT *
FROM t
JOIN (
SELECT counter, count(DISTINCT time) AS nb_time
FROM t
GROUP BY counter
HAVING count(DISTINCT time) = 2
) t1
ON t.counter = t1.counter!',
task_name => 'plantage',
problem_type => dbms_sqldiag.problem_type_execution_error
);
-- activation de l'analyse la plus complète
dbms_sqldiag.set_diagnosis_task_parameter(id_tache, '_SQLDIAG_FINDING_MODE', dbms_sqldiag.SQLDIAG_FINDINGS_ALL);
-- exécution de l'analyse
dbms_sqldiag.execute_diagnosis_task('plantage');
-- affichage du résultat
resu:=dbms_sqldiag.report_diagnosis_task('plantage', dbms_sqldiag.type_text);
dbms_output.put_line(resu);
end;
/ |
Partager