Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 25, 2019 - 8:21 am UTC

Last updated: September 25, 2019 - 2:33 pm UTC

Version: Oracle Database 11.2

Viewed 1000+ times

You Asked

A sequence was about to finish, so I had make it bigger. I work on database 11.2 so I had to use a workaround described here ( https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597 ).
The procedure shown in LiveSQL was run on test database and it went okay. But when I run procedure on production database it failed at last EXECUTE IMMEDIATE line (setting MINVALUE) throwing ORA-04007: MINVALUE cannot be made to exceed the current value.
Difference between test and production is that the sequence is being constantly used in the latter.
I can't understand why it threw error on production and not on test database.
Can you help?

with LiveSQL Test Case:

and Chris said...

I'm a bit confused as to what you're doing here. The approach you linked to is to reset the sequence back to 1.

It appears you're trying to move the next value up past the current max. There's no need to do this.

Just increase the maximum:

alter sequence zcmd_seq_test 
  maxvalue 99999999999999999999;


Whatever you do, there's no need to increase the min value too!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Cezary, September 25, 2019 - 2:04 pm UTC

Changing sequences was done on two separate databases. My reasoning behind setting MINVALUE was that I wanted to be able to recognize database just by looking at number based on sequence. I wanted to set one sequence in range 1-4,99 billion and second one 5-9,99 billion.
Parameters of sequence before deploying procedure were similar to those from LiveSQL.
Somehow setting the MINVALUE on live sequence produced an ORA-04007 error when I deployed it as attached procedure.
Is it just a matter of bad practice? Should I just drop and create sequence in the future?
Chris Saxon
September 25, 2019 - 2:33 pm UTC

I'd say that using the sequence numbers to identify the database is... a flawed approach. Particularly as you're already consuming the sequence, so presumably both databases already have rows in the 1 - 5 billion range.

Anyway the issue is because last_number in *_sequences is not the "next value". It's the last value written to disk. When you first access it, this is the initial value + cache value.

In testing, nothing uses the sequence, so last_value really is the "next value". But when someone's using it this is something between 1 and 20 (the cached values) more than the real next value:

create sequence ZCMD_SEQ_TEST 
minvalue 90000000 
maxvalue 100000000
start with 94683461 
increment by 1 
cache 20;

SELECT last_number
FROM all_sequences 
WHERE sequence_name = 'ZCMD_SEQ_TEST';

LAST_NUMBER   
      94683461 

SELECT ZCMD_SEQ_TEST.NEXTVAL FROM DUAL;

NEXTVAL    
   94683461 

SELECT last_number
FROM all_sequences 
WHERE sequence_name = 'ZCMD_SEQ_TEST';

LAST_NUMBER   
      94683481 


So when using this "for real", you're somewhere up to 20 values short of your desired new minimum of 1,000,000,000.

Outputting what's going on makes this clearer:

DECLARE 
  newnu NUMBER := 1000000001; 
  lnu NUMBER; 
  ib NUMBER; 
  stmt varchar2(100);
  procedure p as
  begin
    dbms_output.put_line ( stmt );
    execute immediate stmt;
  end p;

BEGIN 
  SELECT last_number, increment_by 
  INTO lnu, ib 
  FROM all_sequences 
  WHERE sequence_name = 'ZCMD_SEQ_TEST'; 

  stmt := 'ALTER SEQUENCE ZCMD_SEQ_TEST MAXVALUE 4999999999'; p();
  stmt := 'ALTER SEQUENCE ZCMD_SEQ_TEST INCREMENT BY ' || (newnu - lnu); p();
  
  dbms_output.put_line ( (newnu - lnu) );
  
  SELECT last_number, increment_by 
  INTO lnu, ib 
  FROM all_sequences 
  WHERE sequence_name = 'ZCMD_SEQ_TEST'; 
  
  dbms_output.put_line ( lnu );
  
  EXECUTE IMMEDIATE 'SELECT ZCMD_SEQ_TEST.NEXTVAL FROM DUAL' INTO lnu; 
  
  dbms_output.put_line ( lnu );
  
  stmt := 'ALTER SEQUENCE ZCMD_SEQ_TEST INCREMENT BY ' || ib; p();
  stmt := 'ALTER SEQUENCE ZCMD_SEQ_TEST MINVALUE 1000000000'; p();

END; 
/

ALTER SEQUENCE ZCMD_SEQ_TEST MAXVALUE 4999999999
ALTER SEQUENCE ZCMD_SEQ_TEST INCREMENT BY 905316520
905316520
999999981
999999981
ALTER SEQUENCE ZCMD_SEQ_TEST INCREMENT BY 905316520
ALTER SEQUENCE ZCMD_SEQ_TEST MINVALUE 1000000000

ORA-04007: MINVALUE cannot be made to exceed the current value


So you just need to increase your increment by (up to) 20.

But I reiterate: I don't see the advantage of doing this.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.