Technical Blog Post
Abstract
Oracle Error ORA-01400 and ORA-00001
Body
Once every couple of months I will hear about our client getting the following Oracle error messages.
If you get one of these errors after restoring from backup or migrating to another server, please rebuild the sequence or Recreate Triggers:
1. ORA-01400: cannot insert NULL into ("MAXIMO"."WORKPERIOD"."ROWSTAMP")
RECREATE and ENABLE the TRIGGER
For Example:
create or replace TRIGGER WORKPERIOD_T
BEFORE INSERT OR UPDATE ON WORKPERIOD
FOR EACH ROW DECLARE NEXTVAL INTEGER;
BEGIN SELECT MAXSEQ.NEXTVAL INTO NEXTVAL FROM DUAL;
:NEW.ROWSTAMP := NEXTVAL; END;
COMMIT;
2. ORA-00001: unique constraint (MAXIMO.LOGINTRACKING_NDX1) violated
REBUILD THE ORACLE SEQUENCE
a) Run query: select MAX(LOGINTRACKINGID) from LOGINTRACKING
OUTPUT: 110
b) Drop the sequence in question:
DROP SEQUENCE LOGINTRACKINGSEQ;
c) Recreate the sequence as the recorded value+1.
create sequence LOGINTRACKINGSEQ start with 111;
commit;
UID
ibm11131447