Fixing Oracle Sequences

When using Oracle for Atlassian applications, your sequences should stay up to date in normal operation.  However, if you migrate from another platform you may run into a problem with sequences not being set right.

If you are in this position where Jira issues won't create or you can't add pages in Confluence, sequences maybe out of date.  What you will see in the logs are Oracle errors related to constraint violations.

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (JIRA.SYS_C007392) violated

A select and update of the sequence specifically referenced in the error will fix the immediate issue. More likely is that all sequence values are off.

Diagnostic Steps:

Find the table that is affected by the referenced constraint.

Select owner, constraint_name, constraint_type, table_name, index_owner, index_name from ALL_CONSTRAINTS where CONSTRAINT_NAME in ('SYS_C007392');

Find the sequence that is used for tracking the next value for that table.

SELECT o.owner AS table_schem, o.object_name AS table_name, o.object_type AS table_type FROM all_objects o WHERE o.owner = 'JIRA' AND o.object_type = 'SEQUENCE';

Find the highest value that is used in effective table.

select max(ID) from <TABLE_NAME From Step 1>;

If your Atlassian apps are having this problem, you may have database corruption or you have migrated data manually and forgot to make sure the sequences are up to date.

Resolution:

Shutdown the running application.

This script will check ALL sequences and makes sure the next value will not violate unique key constraints.

--Update all sequences
declare
maxval int;
seqval int;
begin
for i in ( SELECT ucc.column_name, ta.table_name, seq.sequence_name, tr.trigger_name
from user_tables ta
JOIN user_constraints uc ON uc.table_name = ta.table_name
JOIN user_cons_columns ucc ON ucc.constraint_name = uc.constraint_name
JOIN user_triggers tr ON tr.table_name=ta.table_name
JOIN user_dependencies deps ON deps.name=tr.trigger_name
JOIN user_sequences seq ON seq.sequence_name=deps.referenced_name
WHERE uc.constraint_type = 'P' AND ucc."POSITION" = 1
)
loop
execute immediate 'select max('||i.column_name||') from '||i.table_name into maxval;
execute immediate 'select '||i.sequence_name||'.nextval from dual' into seqval;
-- This is where the updates happen
if maxval > seqval then
execute immediate 'alter sequence '||i.sequence_name||' increment by '|| ( maxval - seqval );
execute immediate 'select '||i.sequence_name||'.nextval from dual' into seqval;
execute immediate 'alter sequence '||i.sequence_name||' increment by 1';
execute immediate 'select '||i.sequence_name||'.nextval from dual' into seqval;
end if;
end loop;
end;

Start the service and everything should be hunky-dory!