If I create a sequence and then not-alter it like
CREATE SEQUENCE TEST_SEQUENCE MINVALUE 1 START WITH 1;
ALTER SEQUENCE TEST_SEQUENCE MINVALUE 1;
this gives me an error
ORA-04007: MINVALUE cannot be made to exceed the current value.
Why is that? LAST_NUMBER is still 1, why does the database think it's less? MINVALUE is also not exclusive, since it obviously worked for CREATE. I'd like to write a procedure that dynamically alters the sequence and it would be much simpler if I could just use the input parameters without the need to figure out if they're already applied.
You're misinterpreting reality. If you didn't use that sequence, it doesn't contain any value - but will, as soon as you ask Oracle to return the next value, and it'll be 1.
SQL> create sequence test_sequence minvalue 1 start with 1;
Sequence created.
What is its current value? None; it hasn't been defined yet (in this session):
SQL> select test_sequence.currval from dual;
select test_sequence.currval from dual
*
ERROR at line 1:
ORA-08002: sequence TEST_SEQUENCE.CURRVAL is not yet defined in this session
This is what you saw:
SQL> select min_value, max_value, last_number from user_sequences where sequence_name = 'TEST_SEQUENCE';
MIN_VALUE MAX_VALUE LAST_NUMBER
---------- ---------- -----------
1 1,0000E+28 1
Last number actually is 1, but - did you check documentation what it means?
Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.
For session sequences, the value in this column should be ignored.
So: once you decide to actually use it, value that's fetched is the one you wanted: 1:
SQL> select test_sequence.nextval from dual;
NEXTVAL
----------
1
SQL>
Conclusion: alter sequence you tried to use is useless and unnecessary and - can't be done, not like that.
If you wanted to adjust values returned by the sequence (re-read what Jarlh commented about it; doing that might cause problems), here's how.
To demonstrate it, I fetched from the sequence several times and now its value is
SQL> select test_sequence.nextval from dual;
NEXTVAL
----------
11
Increment it by a negative number; calculate it yourself. For this demo, I'm using -5:
SQL> alter sequence test_sequence increment by -5;
Sequence altered.
It means that the sequence is now reset to a lower value (11 - 5 = 6):
SQL> select test_sequence.nextval from dual;
NEXTVAL
----------
6
As you probably don't want to go any lower by every next fetch, set it to increment by 1 again:
SQL> alter sequence test_sequence increment by 1;
Sequence altered.
SQL> select test_sequence.nextval from dual;
NEXTVAL
----------
7
SQL>