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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.