lundi 29 décembre 2014

How to set the sequence to the last used id for all tables in Oracle?


I'm working on an open source elearning system called Moodle which usually uses MySql or Postgresql, but can also use MSSQL and Oracle.


A client wishes to use Oracle, which I have limited experience of. I've got a local install of Oracle 11g r2.


I used a script to transfer data from MySql into Oracle, including the ID to keep the references.


But now when creating a record I get the error message : ORA-00001: unique constraint (SCHEMANAME.TABLENAME_ID_PK) violated


I'm guessing the sequences weren't updated when I inserted the ID's?


Is there an easy way to update all the table sequences to the last ID number used + 1 in each table?


Unless it's something else?


UPDATE : I can use this to get the last number in the sequence



SELECT sequence_name, last_number
FROM dba_sequences
WHERE sequence_owner = 'SCHEMA_NAME'
AND sequence_name LIKE 'V_%'


The last_number column has values but it looks like they are lower than the last id used in the table.





Aucun commentaire:

Enregistrer un commentaire