Oracle 12c: How can I change an existing primary key column to an identity column?

Question

I have a table that contains a primary key column, which is automatically incremented from the application. How to change column in identifier column in Oracle 12c?

The following is an example of an example -

create table tmp_identity (
   id number(100) primary key,
   value varchar2(100)
);

Let's say we populated the table with the following data -

ID        VALUE
---------------
1         Sample 1
2         Sample 2
3         Sample 3

What we plan to do is turn this column idinto a columnthat will be -

• Auto increase by 1
• Start at 4

How can i do this? If this is not possible, is there any work available?

Answer 1

, , .

create sequence seq_tmp_identity_id
  start with 4
  increment by 1;

:

alter table tmp_identity 
   modify id 
   default seq_tmp_identity_id.nextval;

. , default on null, null, ( )

, id, :

alter table tmp_identity drop column id;

alter table tmp_identity 
     add id number(38) 
     generated always as identity;

, start with 4,

Answer 2

id identity,

ALTER TABLE tmp_identity
RENAME COLUMN id TO identity; 

1 4, .

CREATE SEQUENCE identity_incre
MINVALUE 4
START WITH 4
INCREMENT BY 1;

identity_incre

INSERT INTO suppliers
(identity, VALUE)
VALUES
(identity_incre.NEXTVAL, 'sample4');