Review
Geraldo Peralta, November 12, 2018 - 8:36 pm UTC
Thanks.
I am using -let's say- 20 sequences for 20 tables.
So if you're only using a sequence to supply primary key values, it's generally easier to go with identity.
1. When couldn't this be easier? Or it's just up to the DBA select one or another?
2. What do you recommend?
November 13, 2018 - 11:34 am UTC
1. When couldn't this be easier? Or it's just up to the DBA select one or another?
Like I said, it would be something like using the sequence for something other than providing PK values for one table. e.g. if you want to share one sequence between many tables.
I can't think of a good reason why you'd want to do that though ;)
2. What do you recommend?
What I said:
So if you're only using a sequence to supply primary key values, it's generally easier to go with identity.
If you're using the sequences for some other reason, you (probably) need to stick with regular sequences.
Review
Geraldo Peralta, November 13, 2018 - 12:44 pm UTC
Thanks.
It's just for PK. No other use.
Something else
Geraldo Peralta, November 13, 2018 - 1:09 pm UTC
I just realized that I cannot alter a system-generated sequence. The following error is thrown: ORA-32793 error “cannot alter a system-generated sequence”.
How can I change the cache, fox example, after a table is created with identity?
If this must be specified just from the beginning I prefer to use a regular sequence. Because I do not know if I have to change some sequence properties later on.
What do you thing?
November 13, 2018 - 3:00 pm UTC
You can do this by modifying the column and setting the identity properties:
create table t (
c1 int generated as identity
);
select cache_size, increment_by
from user_sequences
where sequence_name like 'ISEQ$$%';
CACHE_SIZE INCREMENT_BY
20 1
alter table t
modify c1
generated as identity
increment by 2
cache 1000;
select cache_size, increment_by
from user_sequences
where sequence_name like 'ISEQ$$%';
CACHE_SIZE INCREMENT_BY
1000 2
Review
Geraldo Peralta, November 13, 2018 - 3:09 pm UTC
Great! You're awesome!
Thanks. :)
November 13, 2018 - 4:46 pm UTC
Happy to help
NEXTVAL, CURRVAL and last_number confustion
t1dsoldier, October 01, 2024 - 1:24 pm UTC
Hello Tom,
Wasnt sure if this needed a new post or if it fits here.
CAT_TX_QUEUE_SEQ_ID is an identity column sequence but I also noticed this same behavor in another sequence the app team asked me to modify. I used alter sequence in that situation.
I dont know a lot about sequences so my assumption could be wrong, but shouldnt CURRVAL and LAST_NUMBER match or at least LAST_NUMBER be 1 number behind?
I cant give full DDL of the table because it is a prod table but this is most of it
CREATE TABLE TEST.CAT_TX_QUEUE
(
CATALOG_TX_QUEUE_SEQ_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY ( START WITH 999 MAXVALUE 9999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER NOKEEP NOSCALE) CONSTRAINT CAT_TX_QUEUE_NN1 NOT NULL,
SOURCE_TABLE_NM VARCHAR2(30 BYTE) CONSTRAINT CAT_TX_QUEUE_NN2 NOT NULL,
PRIMARY_KEY VARCHAR2(60 BYTE) CONSTRAINT CAT_TX_QUEUE_NN3 NOT NULL
)
PARTITION BY RANGE (CAT_TX_QUEUE_SEQ_ID)
INTERVAL (250000)
(
PARTITION CATTXQ_PART_01 VALUES LESS THAN (250000)
)
NOCACHE;
CREATE UNIQUE INDEX TEST.CAT_TX_QUEUE_PK ON TEST.CAT_TX_QUEUE
(CATALOG_TX_QUEUE_SEQ_ID);
ALTER TABLE TEST.CAT_TX_QUEUE ADD (
CONSTRAINT CAT_TX_QUEUE_PK
PRIMARY KEY
(CAT_TX_QUEUE_SEQ_ID)
USING INDEX TEST.CAT_TX_QUEUE_PK
ENABLE VALIDATE);
-- Sequence ISEQ$$_1206002 is created automatically by Oracle for use with an Identity column
SQL> alter table TEST.CAT_TX_QUEUE
2 modify CAT_TX_QUEUE_SEQ_ID
3 generated as identity
4* start with 997;
Table TEST.CAT_TX_QUEUE altered.
SQL> commit; <== dont think its needed but just in case
Commit complete.
SQL> SELECT ISEQ$$_1206002.nextval
2* FROM DUAL;
NEXTVAL
__________
997
SQL> SELECT ISEQ$$_1206002.currval
2* FROM DUAL;
CURRVAL
__________
997
SQL> select sequence_owner, sequence_name, last_number from dba_sequences where sequence_name = 'ISEQ$$_1206002';
SEQUENCE_OWNER SEQUENCE_NAME LAST_NUMBER
_________________ _________________ ______________
TEST ISEQ$$_1206002 998 <== shouldn't this be 997
SQL> SELECT ISEQ$$_1206002.nextval
2* FROM DUAL;
NEXTVAL
__________
998
SQL> SELECT ISEQ$$_1206002.currval
2* FROM DUAL;
CURRVAL
__________
998
SQL> select sequence_owner, sequence_name, last_number from dba_sequences where sequence_name = 'ISEQ$$_1206002';
SEQUENCE_OWNER SEQUENCE_NAME LAST_NUMBER
_________________ _________________ ______________
TEST ISEQ$$_1206002 999 <== shouldn't this be 998
SQL>
October 01, 2024 - 1:33 pm UTC
shouldnt CURRVAL and LAST_NUMBER match or at least LAST_NUMBER be 1 number behind?No.
From the docs:
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.e.g. if you create a sequence starting with 1 and a cache of 20 (the default), then get nextval, last_value in the data dictionary is updated to 1 + 20:
create sequence s;
select s.nextval from dual;
select last_number from user_sequences
where sequence_name = 'S';
LAST_NUMBER
-----------
21
thanks so much
t1dsoldier, October 01, 2024 - 2:53 pm UTC
they should allow thumbs up or something.
thanks for the fast response that helps a lot.
October 01, 2024 - 5:16 pm UTC
You're welcome - you can just leave a five star review if you're happy ;)