Skip to Main Content
  • Questions

  • Reset 12c identity column sequence to 1

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 08, 2017 - 11:23 am UTC

Last updated: December 12, 2018 - 3:31 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

For entire schema need a script to reset all 12c identity columns seq to 1.

Identify all identity columns....I believe there should be a data dictionary view.

Then do some alter script.

Like select 'alter identity ' from view ;

Thanks.


and Connor said...

SQL> create table t ( x int generated as identity, y int);

Table created.

SQL>
SQL> insert into t (y) select 10*rownum from dual connect by level <= 10;

10 rows created.

SQL>
SQL> alter table t modify x generated as identity ( minvalue 0 );

Table altered.

SQL>
SQL> alter table t modify x generated as identity ( increment by -10 );

Table altered.

SQL>
SQL> insert into t (y) values ( 1 );

1 row created.

SQL> roll;
Rollback complete.
SQL>
SQL> alter table t modify x generated as identity ( increment by 1);

Table altered.

SQL>
SQL> insert into t (y) values ( 1 );

1 row created.

SQL>
SQL> select * from t;

         X          Y
---------- ----------
         1         10
         2         20
         3         30
         4         40
         5         50
         6         60
         7         70
         8         80
         9         90
        10        100
         1          1


But seriously...try rethink that requirement. An identity column is just that - an identity. Needing it to be "1" is conveying some sort of special meaning to the column values...not the intent of an identity column

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 14, 2017 - 2:22 am UTC

How about - simply do

alter table t modify x generated as identity ( start with 1);
Connor McDonald
May 15, 2017 - 4:13 pm UTC

Yes indeed ! I didnt think that would be allowed, but it is.

Thanks for picking this up.

delete auto generate squence

A reader, December 05, 2018 - 1:23 pm UTC

as per above example, sequence name is like "ISEQ$$_70764" and i want to delete this sequence so how to possible?

when i use drop sequence ISEQ$$_70764 ; it's return an error.
Connor McDonald
December 12, 2018 - 3:31 am UTC

I wrote a blog on that just the other day

https://connor-mcdonald.com/2018/12/03/automatic-sequences-not-being-dropped/
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.