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?

Thanks.

It's just for PK. No other use.

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?

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 

Great! You're awesome!

Thanks. :)

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>

create sequence s;
select s.nextval from dual;
select last_number from user_sequences
where  sequence_name = 'S';

LAST_NUMBER
-----------
         21

they should allow thumbs up or something.

thanks for the fast response that helps a lot.