Synchronize Sequence with Primary Key

//Synchronize Sequence with Primary Key

Synchronize Sequence with Primary Key

Background

Whilst migrating to Oracle 12c, it is clear that we will still have primary keys populated by sequences for years to come. When I pull production data over to our development server, I dread the steps required to synchronize the old sequences to the updated tables. Using the expdp / impdp, moving the tables has become routine and risk free. Then on Monday morning after a data refresh into dev, our applications hit a hard stop with a duplicate entry into a field with a unique index (oh, like a primary key).

Context

The standard here is that tables contain a prefix corresponding to their application and that sequences, indices and related objects contain the table name, as shown:

  • table: tt_employees
  • Sequence: tt_employees_seq
  • Primary key: tt_employees_pk
  • Trigger: tt_employees_trig

This permits us to take a few short cuts in managing the database objects. It means that we can write queries to explore our structures dynamically.

Synchronize Sequence with Primary Key

Initially, I relied on a query to identify sequence / primary key mismatches. After a while, it just wasn’t enough. Picking up techniques from Gokhan Atil, I took the process further down the pitch.

create or replace procedure update_sequence 
------------------------------------------------------------------
--
-- cmoore 25OCT15
-- explores the primary keys and sequences 
-- and identifies any that need updating. 
-- Designed to run after an import from production
-- 25feb2016 - added alter sequence 
------------------------------------------------------------------
as
  l_table_name        varchar2(50);
  l_primary_key       varchar2(50);
  l_sql               varchar2(4000);
  l_sequence_name     varchar2(4000);
  l_max               number;
  l_last_seq_number   number;
  l_select_count      number;
  l_prefix            varchar2(20) := 'TT_%';
  l_schema            varchar2(20) := 'STORMDEV';
  l_increment_by      number;
  l_sequence          varchar2(60);
  l_temp              number;
  
begin
for i in (
  select
    table_name
  from
    all_tables
  where table_name like l_prefix 
  order by table_name
  ) loop

  -- Does a primary key exist?
  SELECT count(cols.column_name)
  INTO l_select_count
  FROM all_constraints cons, all_cons_columns cols
  WHERE cols.table_name = i.TABLE_NAME
  AND cons.constraint_type = 'P'
  AND cons.constraint_name = cols.constraint_name
  AND cons.owner = l_schema
  and rownum = 1
  ORDER BY cols.table_name, cols.position;
	
	-- if a record exists, then there is a primary key
  if l_select_count = 1  then

    SELECT cols.table_name, cols.column_name
    INTO l_table_name, l_primary_key
    FROM all_constraints cons, all_cons_columns cols
    WHERE cols.table_name = i.TABLE_NAME
    AND cons.constraint_type = 'P'
    AND cons.constraint_name = cols.constraint_name
    AND cons.owner = l_schema
    and rownum = 1
    ORDER BY cols.table_name, cols.position;
    
      dbms_output.put( 'Table: ' );
      dbms_output.put_line( i.table_name);
	-- find the largest primary key value in the table
      l_sql := 'select max( #KEY# ) from #TABLE#';
      l_sql := replace(l_sql,'#KEY#',l_primary_key);
      l_sql := replace(l_sql,'#TABLE#',l_table_name);
      execute immediate l_sql into l_max;
			
      dbms_output.put('Max PK: ' ||  to_char(l_max));
      l_sequence := l_table_name || '_SEQ';
      
      -- find the last sequence number issued
	l_sql := 
	q'[select last_number from all_Sequences where sequence_owner = '#SCHEMA#' and sequence_name like '#SEQUENCE#']';
      l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
      l_sql := replace(l_sql,'#SCHEMA#',l_schema);

	-- note: little error checking going on...
      execute immediate l_sql into l_last_seq_number;
      dbms_output.put_line( '  Last Seq: ' || to_char(l_last_seq_number));
			
	-- sequence is less than the most recent primary key entry, so fix it
      if l_last_seq_number < l_max then
        l_increment_by := trim(to_char(l_max - l_last_seq_number + 1));
        dbms_output.put_line( ' ** adjusting sequence by: ' || to_char(l_increment_by));
				
	-- tell the sequence to jump by delta between max(pk) and last_seq_number
        l_sql := 'alter sequence #SEQUENCE# increment by #INCREMENT_BY#';
        l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
        l_sql := replace(l_sql,'#INCREMENT_BY#',l_increment_by);
        execute immediate l_sql;
				
	-- request the nextval from the sequence
        l_sql := 'select #SEQUENCE#.nextval from dual';
        l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
        execute immediate l_sql into l_temp;
				
	-- reset the sequence increment by to the normal of 1
        l_sql := 'alter sequence #SEQUENCE# increment by 1';
        l_sql := replace(l_sql,'#SEQUENCE#',l_sequence);
        execute immediate l_sql;

      end if; -- sequence less than key value
  else -- no primary key exists
      dbms_output.put( 'Table ' );
      dbms_output.put( i.table_name);
      dbms_output.put_line( ' no key found ' );
  end if; -- select count = 1
end loop;
end update_Sequence;

-/qed

By |2018-10-10T08:57:54+00:00January 31st, 2016|Oracle PL/SQL|Comments Off on Synchronize Sequence with Primary Key

About the Author: