Oracle Fehlermeldung ORA-00001: unique constraint (...) violated

Mögliche Fehlerquellen

Unserer Erfahrung nach deutet die Fehlermeldung "Unique Constraint (...) violated" auf eine Daten Inkonsistenz innerhalb des Tablespace hin.

In den meisten Fällen soll ein Unique Constraint die Eindeutigkeit eines Primärschlüssels innerhalb einer Tabelle gewährleisten. Dazu wird der zuletzt verwendete Wert eines Primärschlüssels in einer Sequenz gespeichert. Wenn der nächst höhere Wert der Sequenz nicht in die Tabelle eingefügt werden kann, weil es bereits einen Datensatz mit diesem Wert existiert, wird die Meldung "Unique Constraint (...) violated" ausgegeben und die Verarbeitung beendet.

Mögliche Ursachen

In der Vergangenheit ist die Fehlermeldung "Unique Constraint (...) violated" an uns heran getragen worden, wenn

eine Kopie des produktiven Tablespaces in einen Test Tablespace importiert wurde, in dem bereits zuvor eine alte raum]für[raum Instanz mit Tabellen, Sequenzen, etc. existiert hat.
das Erstellen des Schema Dumps via Data Pump Export ohne den Zusatzparameter flashback_time=systimestamp erfolgt ist

Import in einen nicht leeren Tablespace

Durch den erneuten Import werden i.d.R. zwar die Tabelleninhalte fehlerfrei eingelesen, allerdings bereinigen und aktualisieren sich oft nicht die in den Sequenzen gespeicherten Werte für die Primärschlüssel.

Export des Quell Tablespace ohne Parameter flashback_time

Nur durch den Parameter flashback_time kann sichergestellt werden, dass ein konsistenter Export des raum]für[raum Tablespaces wird. Durch die Konfiguration flashback_time=systimestamp ist gewährleistet, dass die Inhalte der Tabellen alle zum gleichen Zeitpunkt exportiert werden und nicht sequenziell nach einander. Es besteht dann die Gefahr, dass es durch den Zeitversatz zu Inkonsistenzen der Datensätze der verschiedenen Tabellen kommt.

Zielführende Lösungsansätze

Um die Konsistenz des Tablespace wieder herzustellen schlagen wir folgende Vorgehensweise vor:

Ziel Tablespace löschen
Ziel Tablespace erneut anlegen
Export des Quell Tablespaces via Data Pump Export inkl. Parameter flashback_time=systimestamp (Schema Dump)
Import des Schema Dumps in den Ziel Tablespace (inkl. Remapping)

Detaillierte Analyse der Fehlermeldung

htdocs/storage/logs/error.log

========================================================================================================
Error ID:             9121675687191417
Date:                 06.02.2023 12.39.51
Error type [2]:       Database - other errors
Error message:        
+   code: 1
+   message: ORA-00001: unique constraint (R4RDB_CLONE.SYS_C00155126) violated
+   offset: 0
+   sqltext: INSERT INTO veranstaltung (TYP,RAUMID,RESSOURCEGRUPPE,USERID,SERIENID,RUESTTIMESTAMP,BEGINTIMESTAMP,ORIGINALDATE,ENDTIMESTAMP,NACHTIMESTAMP,GEBUCHTAM,GEBUCHTVON,MODUS,ABGERECHNET,FLAGGEBUCHT,MSGEINGANG,LASTCHANGED,RABATT,SZENARIO,REMINDANDDELETE) VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19) RETURNING verId INTO :INCID
Script:               C:\inetpub\wwwroot\rfr55\htdocs\src\Database\Oci8.php //src\Database\Oci8.php(347) //src\Database\AbstractDatabase.php(385) //src\Termin.php(1209) //src\Modules\Bookassistant\Scenario.php(1588) //src\Modules\Bookassistant\Scenario.php(1474) //modules\bookassistant\selectDateDataMulti.php(41) //public\app\modulwrapper.php(51) // referer: app/modViewDates/searchRoomGrafik.php?
Line:                 347
Host:                 https://raumbuchung2.intranet/ (PHP 8.1.14) 
SQL-Command:          INSERT INTO veranstaltung (TYP,RAUMID,RESSOURCEGRUPPE,USERID,SERIENID,RUESTTIMESTAMP,BEGINTIMESTAMP,ORIGINALDATE,ENDTIMESTAMP,NACHTIMESTAMP,GEBUCHTAM,GEBUCHTVON,MODUS,ABGERECHNET,FLAGGEBUCHT,MSGEINGANG,LASTCHANGED,RABATT,SZENARIO,REMINDANDDELETE) VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19) RETURNING verId INTO :INCID
Prepared parameters:  (5 [string], 15 [string], 2 [string], 7 [string], -7 [string], 1675687500 [string], 1675687500 [string], 1675687500 [string], 1675705500 [string], 1675705500 [string], 1675687191 [string], 7 [string], 2 [string], nein [string], -1 [string], 1675687191 [string], 1675687191 [string], 0 [string], 4 [string], 5 [string])
Version:              RFR.5.5.0.6-DEFAULT-1.0.0
========================================================================================================
========================================================================================================

Aus der Fehlermeldung extrahierte Informationen:

Info	aktueller Wert
Name des Unique Constraint	R4RDB_CLONE.SYS_C00155126
Betroffene Tabelle	veranstaltung

Erforderliche Informationen zum Eruieren der Ursache

Um eine Beurteilung vornehmen zu können, sind folgende Daten erforderlich:

Welche Spalte überwacht der Unique Constraint
Welcher Trigger ist verantwortlich für das Erzeugen von konsistenten Werten für die Primärschlüssel Spalte
Welche Sequenz speichert den eindeutigen Wert für die Primärschlüssel Spalte
Welcher Wert ist in der Sequenz gespeichert
Welcher Wert wurde in der Primärschlüssel Spalte des zuletzt eingefügten Datensatzes verwendet

1. Unique Constraint: Überwachte Spalte identifizieren

Unique Constraints sollen doppelte Einträge in einer Tabellenspalten verhindern.

Wie findet man die zuständige Tabelle und Spalte zu einem bestimmten Unique Contraint (z.B. SYS_C00155126) heraus?

Unique und Primary Keys ermitteln

select 
    con.CONSTRAINT_NAME, con.OWNER, con.TABLE_NAME, cc.COLUMN_NAME, con.INDEX_NAME, con.CONSTRAINT_TYPE, con.STATUS
from USER_CONSTRAINTS con, USER_CONS_COLUMNS cc
where con.CONSTRAINT_NAME = 'SYS_C00155126'
and con.CONSTRAINT_TYPE in ( 'U', 'P' )
and con.TABLE_NAME = cc.TABLE_NAME
and con.CONSTRAINT_NAME = cc.CONSTRAINT_NAME

CONSTRAINT_NAME	OWNER	TABLE_NAME	COLUMN_NAME	INDEX_NAME	CONSTRAINT_TYPE	STATUS
SYS_C00155126	R4RDB_CLONE	VERANSTALTUNG	VERID	SYS_C00105126	P	ENABLED

Hinter dem Constraint verbirgt sich also ein Primärschlüssel (Constraint_Type → P)

Ein Primärschlüssel wird mit Hilfe eines Triggers abgebildet.

2. Trigger identifizieren

Verantwortlichen Trigger ermitteln

SELECT TRIGGER_NAME, TRIGGER_BODY, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, TABLE_OWNER, STATUS FROM all_triggers WHERE TABLE_NAME = 'VERANSTALTUNG'

TRIGGER_NAME	TRIGGER_BODY	TRIGGER_TYPE	TRIGGERING_EVENT	TABLE_NAME	TABLE_OWNER	STATUS
TRIG_INS_VERANSTALTUNG	DECLARE new_seq_veranstaltung int; begin if inserting then SELECT SEQ_VERANSTALTUNG.nextval INTO new_seq_veranstaltung FROM dual; :new.VERID := new_seq_veranstaltung; end if; end;	BEFORE EACH ROW	INSERT	VERANSTALTUNG	R4RDB_CLONE	ENABLED

3. Sequenz identifizieren

Eine Sequenz wird verwendet, um eine automatische Nummerierung zu realisieren.

Im TRIGGER_BODY findet man die Anweisungen, die ausgeführt werden, unter anderem den Namen der verwendeten Sequenz

TRIGGER_BODY: Verarbeitungslogik des Triggers

DECLARE new_seq_veranstaltung int;
 
begin
    if 
        inserting
    then 
        SELECT SEQ_VERANSTALTUNG.nextval INTO new_seq_veranstaltung FROM dual;
        :new.VERID := new_seq_veranstaltung;
    end if;
end;

Der aktuelle Wert für den Primary Key wird in einer Sequenz abgespeichert, hier SEQ_VERANSTALTUNG.

4. Aktueller Wert der Sequenz ermitteln

Sequenz ermitteln

SELECT SEQUENCE_NAME, LAST_NUMBER FROM USER_SEQUENCES where SEQUENCE_NAME='SEQ_VERANSTALTUNG'

SEQUENCE_NAME	LAST_NUMBER
SEQ_VERANSTALTUNG	36205

In der Sequenz ist der zuletzt gespeicherte Wert in der Spalte LAST_NUMBER hinterlegt, hier also 36205

Der nächste zulässige Wert wäre also 36205 + 1 = 36206

Eine Verletzung der Integrität liegt also vor, wenn in der Tabelle VERANSTALTUNG bereits eine Zeile existiert, in deren Spalte VERID der Wert 36206 existiert.

4. Zuletzt eingefügter Wert der Primärschlüssel Spalte der Tabelle identifizieren

Zuletzt eingefügten Wert der Primärschlüssel Spalte ermitteln

SELECT MAX(VERID) AS MAX FROM VERANSTALTUNG

MAX
57237

Ermitteln der Veranstaltungs-ID und des korrespondierenden Zeitpunkts der letzten Buchung

Zuletzt eingefügten Wert der Primärschlüssel Spalte

SELECT 
VERID, 
TO_CHAR(
    TO_DATE('1970-01-01','YYYY-MM-DD') + numtodsinterval(GEBUCHTAM,'SECOND'),
    'YYYY-MM-DD HH24:MI:SS'
) AS GEBUCHT_AM
FROM VERANSTALTUNG v WHERE VERID=(
SELECT MAX(v2.VERID) FROM VERANSTALTUNG v2)

Beurteilung der Konsistenz

Man muss bewerten, ob der in der Sequenz gespeicherte Wert (s. Punkt 4) identisch oder größer als der Wert in der Primärschlüssel Spalte des zuletzt in der Tabelle eingefügten Datensatzes (s. Punkt 5)

Was	Wert
In Sequenz gespeicherter Wert	36205
In der Tabelle vorhandener Wert	57237

Der nächste regulär Primärschlüssel Wert soll laut Sequenz 36205 + 1 sein.

In der Tabelle wurden aber bereits Datensätze mit Primärschlüssel IDs bis zum Wert 57237 eingefügt.

Es liegt die Vermutung nahe, dass

der Tablespace entweder bereits früher einmal für eine Kopie des produktiven Tablespaces verwendet wurde, aber alten Sequenzen nicht gelöscht wurden
die Daten des produktiven Tablespace mehrfach importiert wurden