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

Question

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) {
    mtrace($table);
    try {
        $tomgr->reset_sequence($table);
    } catch (moodle_exception $e) {
        // There are a couple of temporary tables without indexes.
        mtrace($e->getMessage());
        continue;
    }
}

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

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

// 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");

Answer 1

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:

declare
  maxval number;
begin
  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 ||';';
end;
/

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.

Answer 2

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);
BEGIN
  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;

end;
/

Answer 3

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.

DECLARE
   max_id     NUMBER;
   sql_text   NVARCHAR2 (2500);
   sql_info   NVARCHAR2 (1000);
BEGIN
   DBMS_OUTPUT.enable (1000000);
FOR x
  IN (SELECT    'ALTER TABLE '
             || table_name
             || ' MODIFY '
             || column_name
             || '  GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH '
                q,
             owner,
             table_name,
             column_name
        FROM ALL_tab_columns
       WHERE owner = 'QA' AND identity_column = 'YES')
 LOOP
  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
  THEN
     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);
  ELSE
     sql_info := x.q || ' 1 );'; --if table is empty
     DBMS_OUTPUT.put_line (sql_info);
  END IF;
  END LOOP;
END;

Answer 4

declare
  maxval number;
begin
  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 ';
end;