Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    56

    Unanswered: Speed-Incrementing a Sequence

    My database application uses a sequence to generate the primary key for a table. Today, however, somebody used an obsolete module to create primary keys without relying on the sequence (basically, max(primary__key) + 1). As a result, the newer module of mine is unable to create primary keys for this table. Oracle rejects attempts at data insertion on the ground of unique constraint on the primary key.

    I need to devise an SQL or PL/SQL command to smash all the nextval's falling in the interval between CALC__KEY.currval + 1 and max(CALC_HDR1.CALC__KEY). It would be quite okay to keep manually issuing a command like below, if the blank interval were less than 100:

    select CALC__KEY.nextval from DUAL;

    But it's not that small.

    Could anyone make a suggestion?

    Ikviens

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Here's a procedure which willl fix all sequences you list in the sequence_list table. It will do it across all schemas except those you exclude from the query in the for loop. I run this proc as an Oracle job as we often get build owners copying up data which was initally loaded in dev and test and thus they bypass the sequence.

    Alan


    Code:
    CREATE TABLE SEQUENCE_LIST
    (
      SEQUENCE_NAME  VARCHAR2(100 BYTE),
      TABLE_NAME     VARCHAR2(100 BYTE),
      COLUMN_NAME    VARCHAR2(100 BYTE)
    );
    
    
    CREATE OR REPLACE PROCEDURE sequence_fixer IS
    	maxval	number;
    	curval	number;    
        	v_app	varchar2(40);
    BEGIN
    
    	FOR stmt_rec in 
        	(
            SELECT SEQUENCE_OWNER OWNER, S.SEQUENCE_NAME,
              LAST_NUMBER, L.TABLE_NAME, L.COLUMN_NAME
            FROM 
              DBA_SEQUENCES S,
              SEQUENCE_LIST L,
              DBA_TABLES T
            WHERE
              S.SEQUENCE_NAME LIKE '%_SEQ' AND 
              SEQUENCE_OWNER NOT LIKE '%SYS%' AND  -- add all schemas you dont want to check here    
              S.SEQUENCE_NAME = L.SEQUENCE_NAME AND
              UPPER(L.TABLE_NAME) = T.TABLE_NAME AND  
              S.SEQUENCE_OWNER = T.OWNER 
             ORDER BY 1        
    	) LOOP   
        		EXECUTE IMMEDIATE 'select nvl(max('||stmt_rec.column_name||'),-1) from '||stmt_rec.owner||'.'||stmt_rec.table_name INTO maxval;
    --        	dbms_output.put_line(stmt_rec.owner||'.'||stmt_rec.table_name ||' -> ' ||maxval);
     		if maxval>=stmt_rec.last_number then
    	        dbms_output.put_line(stmt_rec.owner||'.'||stmt_rec.table_name ||' -> ' ||maxval);        
    	    	EXECUTE IMMEDIATE 'alter sequence ' ||stmt_rec.owner||'.'||stmt_rec.sequence_name||' increment by '||to_char((maxval-stmt_rec.last_number)+1);
    		EXECUTE IMMEDIATE 'SELECT ' ||stmt_rec.owner||'.'||stmt_rec.sequence_name ||'.nextval FROM dual' INTO curval;                       
    	    	EXECUTE IMMEDIATE 'alter sequence ' ||stmt_rec.owner||'.'||stmt_rec.sequence_name||' increment by 1';
            end if;       
        END LOOP;   
    
        COMMIT;
    END sequence_fixer;
    /

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Another, maybe less stressful approach - of course, if I understood you well - you'd like to fetch the next sequence value to be higher than the current primary key value.

    DROP SEQUENCE CALC__KEY;

    CREATE SEQUENCE CALC__KEY
    START WITH <a NUMBER that represents MAX(CALC_HDR1.CALC__KEY)>;

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would recommend doing the alter sequence method as then you dont have to worry about your app keeling over when a sequence disappears

    Alan

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I agree; who knows was it granted to anyone else etc.

    It would be wonderful if there was "ALTER SEQUENCE its_name START WITH some_number", but there isn't ...

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    well the "alter sequence ... increment by" is the next best thing and means you dont have to drop the sequence...

    Alan

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What you can do with alter sequence is:
    Code:
    SQL> select myseq.nextval from dual;
    
       NEXTVAL
    ----------
            41
    
    SQL> alter sequence myseq increment by 123000;
    
    Sequence altered.
    
    SQL> select myseq.nextval from dual;
    
       NEXTVAL
    ----------
        123041
    
    SQL> alter sequence myseq increment by 1;
    
    Sequence altered.
    
    SQL> select myseq.nextval from dual;
    
       NEXTVAL
    ----------
        123042
    Of course, you would have to do this when no one else was using the sequence, unless you don't mind it being incremented to a much larger value than you had in mind!

  8. #8
    Join Date
    Mar 2006
    Posts
    56
    Tony, thank you for your sample of the alter sequence solution. My module started generating CALC__KEYs now.

    Ikviens

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •