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.

UPDATE 2: Found some existing code in Moodle that resets the sequence, I had wrongly assumed the naming convention for the sequences was created by Oracle but its Moodle. Here is some Moodle/PHP code that will reset all the sequences:

$todb = moodle_database::get_driver_instance($dbtype, $dblibrary);
$todb->connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
$totables = $todb->get_tables(false);
$tomgr = $todb->get_manager();

foreach ($totables as $table) {
    try {
    } catch (moodle_exception $e) {
        // There are a couple of temporary tables without indexes.

UPDATE 3 : This is basically what the reset sequence function does

$value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');

// Calculate a sequence name from the tablename.
$seqname = $this->getSequenceFromDB($xmldb_table);
if (!$seqname) {
    // Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method
    $seqname = $this->getNameForObject($table, 'id', 'seq');

$this->mdb->execute("DROP SEQUENCE $seqname");
$this->mdb->execute("CREATE SEQUENCE $seqname START WITH $value INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size");
  • Here's what you must do : 1. import the data, 2. adjust the sequence to the data. This is to be done for each table separately. After that, each INSERT should do the needed to use the correct sequence value. Do you have triggers already ? If no, the above should be best solution for you. You're asking US if the sequence is imported ? Well, do YOU import it ?
    – tvCa
    Commented Dec 29, 2014 at 17:17
  • check the php function $tomgr->reset_sequenceto find out what it actually does.
    – o0x258
    Commented Dec 30, 2014 at 7:30

4 Answers 4


First you have to find out what the current sequence value is and then reset it. I would write a script which does the following tasks:

  1. Get the max value from the table
  2. update/recreate the sequence

The script could look like this:

  maxval number;
  select max(id) into maxval from <table_1>;
  execute immediate 'ALTER SEQUENCE <sequence_name_1> START WITH '|| maxval+1 ||';';

  select max(id) into maxval from <table_2>;
  execute immediate 'ALTER SEQUENCE <sequence_name_2> START WITH '|| maxval+1 ||';';

I'm pretty sure there are better scripts but if you have to do this task frequently this might do the job without much development time.

  • Super - can I get the sequence name for the table name? Commented Dec 29, 2014 at 14:17
  • 2
    @Russell England : There is no "sequence name for table name" in Oracle. Sequence is not related directly to a table - you may use the same sequence to generate values in multiple tables or columns. Normally, you adopt some naming convention for sequences, say [table_name]_SEQ , so it's more readable.
    – a1ex07
    Commented Dec 29, 2014 at 14:52
  • 2
    Yes, a1ex07 is right. To find out which sequence is related to which table, you have to check the database design which was created by the developers. But there might be an option. Check if the tables have triggers which populate the sequence controlled columns. If they do exist then use them to find out which sequence they get their value from.
    – o0x258
    Commented Dec 29, 2014 at 15:50

Of course you need to find a way to match the sequence name with the table name, since there's not a connection or constraint enforced by the RDBMS.

When you have it, you may use this procedure to reset a sequence value:

create or replace procedure proc_reset_sequence
( p_seq_owner in varchar2,
  p_seq_name in varchar2,
  p_target_number in number
) AS
  v_save_cache   number;
  v_desired_val number;
  v_last_number number;
  v_last_increment number;

  v_sqlstr varchar2(2000);
  SELECT cache_size, last_number, increment_by into v_save_cache, v_last_number, v_last_increment
  from all_sequences
  where sequence_owner = p_seq_owner and sequence_name = p_seq_name;

  v_desired_val := p_target_number - v_last_number;
  v_sqlstr := 'alter sequence ' || p_seq_owner || '.' || p_seq_name || ' nocache' || ' increment by ' || to_char(v_desired_val);

  execute immediate v_sqlstr;

  v_sqlstr := 'begin select ' || p_seq_owner || '.' || p_seq_name || '.nextval into :1 from dual; end;';

  execute immediate v_sqlstr using out v_desired_val;

  v_sqlstr := 'alter sequence ' || p_seq_owner || '.' || p_seq_name  || 
    case when v_save_cache > 1 then 
      ' cache ' || to_char(v_save_cache) 
    else ' nocache' end 
    || ' increment by ' || to_char(v_last_increment);

  execute immediate v_sqlstr;


This script will generate the ALTER TABLE statements needed to update all sequences to the correct START WITH values. Just replace the owner of 'QA' with the correct schema name, and it will output the list of statements to run.

   max_id     NUMBER;
   sql_text   NVARCHAR2 (2500);
   sql_info   NVARCHAR2 (1000);
   DBMS_OUTPUT.enable (1000000);
             || table_name
             || ' MODIFY '
             || column_name
        FROM ALL_tab_columns
       WHERE owner = 'QA' AND identity_column = 'YES')
  sql_text :=
        'select MAX ('
     || x.column_name
     || ')+1  into :max_id from '
     || x.owner
     || '.'
     || x.table_name;
  EXECUTE IMMEDIATE sql_text INTO max_id;
  IF max_id > 0
     sql_info := x.q || max_id || ');'; --get query for update auto generated sequences value to themax(id) for all tables
     --sql_info := 'select '|| x.sequance_name || '.nextval  from dual'; -- check next values for all sequences
     DBMS_OUTPUT.put_line (sql_info);
     sql_info := x.q || ' 1 );'; --if table is empty
     DBMS_OUTPUT.put_line (sql_info);
  • I understand why this was downvoted, but it was the answer that helped me get what I needed, so I made some updates to try to make it work for others who stumble across this post.
    – jlemley
    Commented Dec 7, 2021 at 22:57
  maxval number;
  select max(id) + 1 into maxval from <tablename>;
  execute immediate 'DROP SEQUENCE  <sequencename> ';
  execute immediate 'CREATE SEQUENCE  <sequencename>  MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH ' || maxval || ' CACHE 20 NOORDER  NOCYCLE ';
  • 1
    How does that differ from existing answers?
    – mustaccio
    Commented Mar 23, 2017 at 18:07
  • I assume that you're trying to provide an answer using a sequence (which the OP did post about). However, this just shows how to create a sequence; and, it's not clear that it really needs to be based off max(id) - max(reference) might actually be more to the point. It might help the OP to see how to use the sequence from here.
    – RDFozz
    Commented Mar 23, 2017 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.