How to reset the auto increment back to 1 after a table truncate to an Oracle table

I am creating an Oracle table with an index column that is auto generated. It works great, and my id column is auto generated starting at the number 1.

After I truncate the table it does not reset the auto increment back to 1. What sql command will reset my auto increment value back to 1  after truncation?

Example of how I create the table :

CREATE TABLE temp (id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), BRGS VARCHAR2(36));
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
NerdsOfTechTechnology ScientistCommented:
Why don't you DROP the table instead of TRUNCATE? Then re-CREATE it? This should reset the autoincrement.

DROP TABLE temp;
CREATE TABLE temp (id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), BRGS VARCHAR2(36));

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I don't believe there is an automatic way to do this on truncate without some DDL triggers.

You can alter table:
alter table temp  modify id generated as identity ( start with 1);

A bigger question is why would you do this on a TEMP table?
0
 
slightwv (䄆 Netminder) Commented:
>>DROP TABLE temp;

You would need to add PURGE to that or remember to empty the recycle bin.  Otherwise every dropped table will remain there.

It is also unnecessary to drop and recreate every time when a simple ALTER works.
1
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
johnsoneSenior Oracle DBACommented:
DROP also removes indexes, privileges, triggers, etc.  All those things need to be put back.  A simple create does not necessarily put back what was there.
1
 
Geert GOracle dbaCommented:
instead of providing the column like that
create a named sequence: create sequence seq_yourtable nocache;

after your truncate table, recreate the sequence
drop sequence seq_yourtable;
create sequence seq_yourtable nocache;

oracle will create a system-named sequence
you could probably find it via dba_sequences and recreate that
0
 
johnsoneSenior Oracle DBACommented:
Dropping the sequence presents the same problems as dropping the table.  You lose privileges, invalid PL/SQL code, etc.

While you cannot alter a sequence to restart it, you can do this (just make sure to put the correct name in the first variable declaration):
DECLARE 
    seq_name user_sequences.sequence_name%TYPE := 'SEQ_NAME'; 
    min_val  user_sequences.min_value%TYPE; 
    incr_val user_sequences.increment_by%TYPE; 
    seq_val  user_sequences.last_number%TYPE; 
BEGIN 
    SELECT min_value, 
           increment_by 
    INTO   min_val, incr_val 
    FROM   user_sequences 
    WHERE  sequence_name = seq_name; 

    EXECUTE IMMEDIATE 'select '|| seq_name|| '.nextval from dual' INTO seq_val; 

    EXECUTE IMMEDIATE 'alter sequence '|| seq_name|| ' increment by '|| To_char( 
    min_val - seq_val); 

    EXECUTE IMMEDIATE 'select '|| seq_name|| '.nextval from dual' INTO seq_val; 

    EXECUTE IMMEDIATE 'alter sequence '|| seq_name|| ' increment by '|| incr_val 
    ; 
END; 

/ 

Open in new window

That will reset the sequence and you lose the first number.  But, you don't have to drop it and possibly lose privileges and invalidate PL/SQL code.

While it is probably possible to determine the name of a system sequence that is associated with an auto increment, I wouldn't alter that sequence and/or drop and recreate.  You are just opening up a can of worm there.  Never touch system generated stuff.
0
 
slightwv (䄆 Netminder) Commented:
Oracle 12c provides the identity column for a reason.  Use it when you can.

Creating a sequence object sort of defeats the purpose of that.  Why use another object when you don;t need to?
0
 
slightwv (䄆 Netminder) Commented:
Can I ask why dropping and recreating the table is a better solution than just altering the existing column?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.