Ein Kollege wollte kürzlich wissen, wie man das Statement eruieren könnte, das nachts für den folgenden Eintrag im Alert.log der Datenbank sorgte:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Mir fiel dazu der Datenbanktrigger AFTER SERVERERROR ON DATABASE ein, der mit Oracle 8i eingeführt wurde. Eine Suche nach dieser Syntax in Verbindung mit ORA-1652 führte schnell zu einem Code, der auf den ersten Blick nichts zu wünschen übrig ließ und außerdem von Tom Kyte stammte.
Hier ist der Trigger in leicht veränderter Form. Um Database-Trigger zu erstellen, benötigt man das Recht ADMINISTER DATABASE TRIGGER.
conn system/sys
CREATE TABLE temp_ts_problems (errmsg VARCHAR2 (4000));
CREATE OR REPLACE TRIGGER ora_1652_trig
AFTER SERVERERROR ON DATABASE
DECLARE
v_sql_text ora_name_list_t;
v_cnt NUMBER;
v_errmsg VARCHAR2(4000);
BEGIN
IF is_servererror(1652) THEN
-- Damit kann man das Statement abfangen
v_cnt := ora_sql_txt(v_sql_text);
FOR i IN 1 .. v_cnt LOOP
v_errmsg := v_errmsg||v_sql_text(i);
END LOOP;
INSERT INTO temp_ts_problems
VALUES('Benutzer: ' || ora_login_user||
', Datum: '||to_char(sysdate, 'dd.mm.rr hh24:mi:ss')||
', SQL-Statement: '||v_errmsg);
END IF;
END;
/
Ein paar Erklärungen zur Syntax
- ora_name_list_t ist eine Nested Table aus dem Package DBMS_STANDARD: TYPE ora_name_list_t IS TABLE OF VARCHAR2(64)
- is_servererror, ora_login_user und ora_sql_txt sind sogenannte Attributfunktionen von System-Events, die nur in Datenbank-Triggern verwendet werden.
- is_servererror übernimmt den SQLCODE eines Fehlers und gibt TRUE zurück, falls dieser unter den 5 ersten Fehlern im Errorstack steht, ansonsten false.
- Über ora_login_user bekommt man den Verursacher des Fehlers
- ora_sql_text liefert das Statement, das den Trigger ausgelöst hat, als Zeilen der Nested Table ora_name_list_t über einen OUT-Parameter sowie die Zahl der Zeilen über den RETURN- Wert zurück. Deshalb muss man den Text in einer Schleife aufsammeln.
Weitere z. T. nützliche Funktionen:
- ora_server_error(1) würde den ersten Fehler im Errorstack liefern. Das braucht man nur, wenn man den Trigger generell für das Fehlerlogging nutzen will.
- ora_sysevent informiert über das triggernde Event, hier SERVERERROR.
Folgende Fehler kann der Trigger laut Oracle-Dokumentation nicht abfangen
- ORA-01403: no data found
- ORA-01422: exact fetch returns more than requested number of rows
- ORA-01423: error encountered while checking for extra rows in exact fetch
- ORA-01034: ORACLE not available
- ORA-04030: out of process memory when trying to allocate string bytes (string, string)
TESTS
Erst den Temp-Tablespace drastisch verkleinern
ALTER DATABASE TEMPFILE 'e:\oracle\oradata\o21c\temp01.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE 50M;
als Applikations-User eine aufwendige Sortierung durchführen
conn scott/tiger
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c ORDER BY 1;
=>
FEHLER in Zeile 2:
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden
nd als User System die Fehlertabelle abfragen
conn system/sys
SELECT * FROM temp_ts_problems;
=>
ERRMSG
--------------------------------------------------------------------------------
Benutzer: SCOTT, Datum: 30.01.2015 09:10:24', SQL-Statement: SELECT a.object_name
FROM all_objects a, all_objects b, all_objects c ORDER BY 1
Was will man mehr ?
Es gibt aber leider noch andere Szenarios, die den Fehler ORA-1652 auslösen können, z. B. eine Index-Reorganisation oder ein Index-Aufbau.
Dazu erzeugen wir als System im Userschema eine Tabelle mit 2 Mio Zeilen big_tab.sql, Copyright wieder Tom Kyte. Der ausführende User braucht Select-Rechte auf dba_objects) und darauf einen mehrspaltigen Index
CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id);
=>
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-06502: PL/SQL: numerischer oder Wertefehler
ORA-06512: in Zeile 8
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden
Das Create-Index-Statement ist aber leider nicht in der Fehlertabelle gelandet.
conn system/sys
SELECT * FROM temp_ts_problems;
=>
ERRMSG
----------------------------------------------------
Benutzer: SCOTT, Datum: 30.01.2022 09:10:24', SQL-Statement: SELECT a.object_name
FROM all_objects a, all_objects b, all_objects c ORDER BY 1
Weitere Tests ohne Einschränkung auf ORA-1652 ergaben, dass das Problem bei allen getesteten DDL-Statements auftauchte, aber nur in Version 11g.
Um dem Fehler auf die Spur zu kommen, muss man nur den Trigger etwas umschreiben.
conn system/sys
CREATE OR REPLACE TRIGGER ora_1652_trig
AFTER SERVERERROR ON DATABASE
DECLARE
v_sql_text ora_name_list_t;
v_cnt NUMBER;
v_errmsg VARCHAR2(4000);
BEGIN
IF is_servererror(1652) THEN
DBMS_OUTPUT.PUT_LINE('Event: '||ora_sysevent||
', Fehlernummer: '||ora_server_error(1));
v_cnt := ora_sql_txt(v_sql_text);
DBMS_OUTPUT.PUT_LINE('Anzahl der Zeilen in der nested Table: '||NVL(v_cnt, 0));
DBMS_OUTPUT.NEW_LINE;
FOR i IN 1 .. v_cnt LOOP
v_errmsg := v_errmsg||v_sql_text(i);
DBMS_OUTPUT.PUT_LINE(v_errmsg);
END LOOP;
END IF;
END;
/
conn scott/tiger
set serveroutput on
CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id);
Event: SERVERERROR, Fehlernummer: 1652
Anzahl der Zeilen in der nested Table: 0
ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id)
*
FEHLER in Zeile 2:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-06502: PL/SQL: numerischer oder Wertefehler
ORA-06512: in Zeile 11
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden
Der Fehler wird also durchaus abgefangen, aber bei der Füllung der nested table über ora_sql_text geht etwas schief. Weil die obere Grenze der Schleife NULL ist, bekommt man dann den Fehler ORA-06502: PL/SQL: numerischer oder Wertefehler und als interner Fehler löst dies die Meldung ORA-00604: Fehler auf rekursiver SQL-Ebene 1 aus
LÖSUNG
Wenn Oracle das auslösende SQL-Statement nicht liefert, muss man halt selber in v$sqlarea nachsehen.
Für diesen Trigger braucht man SELECT-Rechte an v_$sqlarea.
DELETE temp_ts_problems;
CREATE OR REPLACE TRIGGER ora_1652_trig
AFTER SERVERERROR ON DATABASE
DECLARE
v_sql_text VARCHAR2(1000);
v_user VARCHAR2(30);
BEGIN
IF is_servererror(1652) then
v_user := ora_login_user;
INSERT into temp_ts_problems
SELECT ora_login_user||', '||
TO_CHAR(systimestamp, 'dd.mm.rr hh24:mi:ssxff')||CHR(10)||sqltext
FROM (SELECT SUBSTR(sql_text, 1,200) sqltext
FROM v$sqlarea
WHERE parsing_schema_name = v_user
ORDER BY last_load_time DESC)
WHERE rownum = 1;
END IF;
END;
/
Wenn sich viele Benutzer unter dem gleichen Usernamen auf der Datenbank tummeln, muss man natürlich die Anzahl der Zeilen erhöhen (z. B. WHERE rownum < 20) und mit WHERE-Bedingungen arbeiten, um das problematische Statement aus dem Wust von recursive calls und ähnlichem herauszufiltern. Hier soll nur die Basisfunktionalität gezeigt werden.
Der User Scott reizt wieder den Temp Tablespace aus (die Fehlermeldungen spar ich mir jetzt).
conn scott/tiger
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c ORDER BY 1;
CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id);
Diesmal finden sich beide Statements in der Logging-Tabelle.
conn system/sys
SELECT * FROM temp_ts_problems;
ERRMSG
----------------------------------------------------------------------------------------
SCOTT, 02.02.15 00:36:02,417000
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c ORDER BY 1
SCOTT, 02.02.15 00:36:30,887000
CREATE INDEX big_tab_idx ON big_tab(id, owner, object_name, subobject_name, object_id, data_object_id)
Statt eine Tabelle mit den Fehlermeldungen zu füllen, kann man natürlich auch Textfiles mit UTL_FILE erzeugen, sich über UTL_MAIL eine E-mail schicken lassen oder die Fehlermeldungen über DBMS_SYSTEM.KSDWRT gleich ins Alertlog schreiben.
ALTERNATIVLÖSUNG
Für alle, die Trigger hassen, und viel Platz haben, gibt es noch eine Alternative: Tracing.
conn sys/<pwd> as sysdba
ALTER SYSTEM SET EVENTS '1652 trace name errorstack level 12';
conn scott/tiger
SELECT a.object_name FROM all_objects a, all_objects b, all_objects c ORDER BY 1;
Wo das Tracefile steckt, bekommt man über die View v$diag_alert_ext am schnellsten heraus.
conn / as sysdba
-- Die 0 ist hier wichtig, sonst bekommt man nicht die Zeilen mit dem Tracefile
col host_id for a10
col datum for a18
col module_id for a15
col message_text for a100
SELECT TO_CHAR(originating_timestamp, 'dd.mm.rr hh24:mi:ss') datum, host_id, module_id, message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-01652%';
DATUM HOST_ID MODULE_ID MESSAGE_TEXT
------------------ ---------- ------------- -----------------------------------------------------------------------------
02.02.15 14:42:05 BLECHDEPP SQL Developer Errors in file E:\ORACLE\diag\rdbms\o11g\o11g\trace\o11g_ora_4436.trc:
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden
Im riesigen Tracefile (> 10 MB) steht dann (Gott sei Dank ziemlich weit oben)
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)
----- Error Stack Dump -----
ORA-01652: Temp-Segment kann nicht um 128 in Tablespace TEMP erweitert werden
----- Current SQL Statement for this session (sql_id=8agc1pg0us4mf) -----
select a.object_name
from all_objects a, all_objects b, all_objects c
order by object_name;
Zusätzlich wird noch ein Diagnostic Dump gestartet, der zusätzlich ca 1,2 MB belegt, insofern bevorzuge ich persönlich die Platz sparendere und flexiblere Lösung mit dem Trigger.
FAZIT
AFTER SERVERERROR ON DATABASE-Trigger können wertvolle Dienste beim Troubleshooting leisten, wenn man sich nicht darauf verlässt, dass ein Code, die unter 10g noch funktioniert hat, auch unter 11g problemlos läuft und die Trigger vorsichtig und gezielt für einzelne Datenbankfehler einsetzt.
Falls Sie Fragen zur Umsetzung haben, hilft unser Consulting-Team Ihnen gerne weiter.