CODEWITHSUNDEEP
oracle-database
jim
jim

Reputation: 21

Recreate Sequences in 12C

We are migrating our system from Oracle 11g to 12C, my scenario only concerns testing, when we go live we will pause the application so no more sequences will be generated. I am also new to Oracle.

In our test environment we are getting key violations due to sequences numbers being behind.

I need to cycle through 343 existing sequences, getting the highest number and rebuild/recreate sequences starting with the current highest number +1 in each table. The code below(from another thread ) may work, but i need it to cycle through 343 tables. Getting the max sequence number from user_sequences in 11g;

     declare
      ex number;
     begin
      select MAX(MAX_FK_ID)  + 1 into ex from TABLE;
       If ex > 0 then
        begin
                execute immediate 'DROP SEQUENCE SQ_NAME';
           exception when others then
             null;
        end;
        execute immediate 'CREATE SEQUENCE SQ_NAME INCREMENT BY 1 START 
        WITH ' || ex || ' NOCYCLE CACHE 20 NOORDER';
       end if;
    end;

Upvotes: 0

Views: 333

Answers (2)

TenG
TenG

Reputation: 4004

This is where having a decent naming convention/standard helps.

In databases I've designed, each table has a 3 or 4 character alias, and we name things like indices, constraints, etc with that alias.

If a table called the_table has an alias ttab, the primary key column would be ttab_id, the primary key constraint would be pk_ttab, and the sequence that is used for ttab_id would be seq_ttab_id.

With this in mind, you would write someone like the following (not tested so might need a little adjustment, but hopefully you should get the general idea):

DECLARE
   CURSOR cseq IS
      SELECT sequence_name seqname FROM user_sequences;
   tabname VARCHAR2(30);
   colname VARCHAR2(30);
   cmd     VARCHAR2(1000);
   maxval  NUMBER;
BEGIN
   FOR r IN cseq LOOP
      /*
      ** Now get corresponding table/column name matching the sequence name
      */
      SELECT table_name, column_name 
      INTO tabname, colname
      FROM user_cons_columns #
      WHERE column_name = REPLACE ( r.seqname, 'SEQ_', '' ) 
      AND constraint_name = REPLACE( REPLACE(r.seqname, '_ID', '' ), 'SEQ_', 'PK_' );
      /*
      ** Query the table to get the current maximum value - could use stats
      */
      cmd := 'SELECT MAX(' || colname || ') FROM ' || tabname;
      EXECUTE IMMEDIATE cmd INTO maxval;
      /*
      ** Set sequence to inc by that amount
      */
      cmd := 'alter sequence ' || r.seqname || ' INCREMENT BY ' || maxval;
      EXECUTE IMMEDIATE cmd;
      /*
      ** SELECT the sequence to bump its value up
      */
      cmd := 'SELECT ' || r.seqname || '.nextval FROM DUAL';
      EXECUTE IMMEDIATE cmd INTO maxval;
      /*
      ** Set sequence inc by back down
      */
      cmd := 'alter sequence ' || r.seqname || ' INCREMENT BY 1';
      EXECUTE IMMEDIATE cmd;
   END LOOP;
END;
/

If however you do not have the convenience of usable naming conventions, you can do this:

  • Create a stored procedure that accepts a sequence name, table name, column name as parameters.
  • The procedure will query the given table/column for max value
  • The procedure will then do the increment by adjustments seen above
  • Now generate a wrapper stored procedure which will call the worker procedure passing in the 3 values.

End result should be:

BEGIN
  reset_seq_val ( 'seq1', 'table1', 'col1');
  reset_seq_val ( 'seq2', 'table2', 'col2');
...
END;

The hard work here will be compiling this list, but once done you should be able to re-use it on-demand.

Upvotes: 1

Roger Cornejo
Roger Cornejo

Reputation: 1547

If you export your data when application is quiesced, then import your data into 12c you should not have key issues.

If upgrade 12c in place, you shouldn't have key issues either.

I've been involved with dozens of 12c migrations and have never heard of this issue.

Upvotes: 0

Related Questions