ORA-32793: cannot alter a system-generated sequence
create table tq84_ora_32793 (
id integer generated by default as identity primary key,
val varchar2(20) not null
);
begin
insert into tq84_ora_32793 (id, val) values ( 7, 'ABC');
insert into tq84_ora_32793 (id, val) values ( 2, 'DEF');
insert into tq84_ora_32793 (id, val) values ( 4, 'GHI');
commit;
end;
/
begin
insert into tq84_ora_32793 (val) values ('IJK');
insert into tq84_ora_32793 (val) values ('LMN');
insert into tq84_ora_32793 (val) values ('OPQ');
commit;
end;
/
-- ORA-00001: unique constraint (DWH_DM_ABS.SYS_C001450294) violated
-- See Problems with GENERATED BY DEFAULT ON NULL AS IDENTITY columns
select
idc.sequence_name
from
user_tab_identity_cols idc
where
idc.table_name = 'TQ84_ORA_32793' and
idc.column_name = 'ID';
--
-- ISEQ$$_11993184
select max(id) + 1 nextval from tq84_ora_32793 ;
--
-- 8
alter sequence ISEQ$$_11993184 restart start with 8;
--
-- ORA-32793: cannot alter a system-generated sequence
alter table tq84_ora_32793 modify id generated by default as identity start with 8;
begin
insert into tq84_ora_32793 (val) values ('IJK');
insert into tq84_ora_32793 (val) values ('LMN');
insert into tq84_ora_32793 (val) values ('OPQ');
commit;
end;
/
select * from tq84_ora_32793 order by id;
drop table tq84_ora_32793;