Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- dba:oracle_sqlfehler_protokoll [2019/06/10 12:19] – gpipperr
+++ dba:oracle_sqlfehler_protokoll [2019/06/11 11:23] (aktuell) – [Auswerten] gpipperr
@@ Zeile 261: / Zeile 261: @@
 ==== Auswerten ====
-<code plsql>
+Siehe auch https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/01-db-setup/report_global_errorlog.sql
+<code plsql report_global_errorlog.sql>
@@ Zeile 338: / Zeile 341: @@
 set long 64000
-SELECT  COUNT (*) AS anzahl
+with ErrorLog as
-        ,to_char(min(log_date),'dd.mm.yyyy hh24:mi') first_log_entry
+  ( select   stmt
-        ,to_char(max(log_date),'dd.mm.yyyy hh24:mi') last_log_entry
+           , log_date
-        ,nvl(LOG_USR,'n/a') AS LOG_USR
+		   , LOG_USR
-        ,ERR_NR
+		   , ERR_NR
-        ,substr(ERR_MSG,1,300) mesg
+		   , substr(ERR_MSG,1,300) mesg
-		,to_char(STMT) as stmt
+		   , dbms_lob.getlength(STMT) len
-    FROM SYSTEM.ora_errors
+	FROM SYSTEM.ora_errors
-    WHERE nvl(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP')
+   WHERE nvl(log_usr,'n/a') NOT IN ('SYS','SYSMAN','DBSNMP')
-GROUP BY nvl(LOG_USR,'n/a')
+	)
-        ,ERR_NR
+  select
-        ,substr(ERR_MSG,1,300)
+		COUNT (*) AS anzahl
-		,to_char(stmt)
+	  , to_char(min(log_date),'dd.mm.yyyy hh24:mi') first_log_entry
-ORDER BY 2,1
+	  , to_char(max(log_date),'dd.mm.yyyy hh24:mi') last_log_entry
+	  , LOG_USR
+	  , ERR_NR
+	  , mesg
+	  , dbms_lob.substr(stmt,4000,1) sql_part1
+	  , case when len > 4000 then dbms_lob.substr(stmt,4000,4001)    end sql_part2
+	  , case when len > 8000 then dbms_lob.substr(stmt,4000,8001)    end sql_part3
+	  , case when len > 12000 then dbms_lob.substr(stmt,4000,12001)  end sql_part4
+	  , case when len > 16000 then dbms_lob.substr(stmt,4000,165001) end sql_part5
+  FROM ErrorLog
+ GROUP BY LOG_USR
+	  , ERR_NR
+	  , mesg
+	  , dbms_lob.substr(stmt,4000,1)
+	  , case when len > 4000 then dbms_lob.substr(stmt,4000,4001)    end
+	  , case when len > 8000 then dbms_lob.substr(stmt,4000,8001)    end
+	  , case when len > 12000 then dbms_lob.substr(stmt,4000,12001)  end
+	  , case when len > 16000 then dbms_lob.substr(stmt,4000,165001) end
+ORDER BY 1
 /
 set markup html off