Data audit when DML and DDL on table!

Hi Expert,

I have a table called student with ID and NAME as the column name whereas i have
made ID as sequence which will generate auto number 1..2..and so on when any new record will be inserted.

But the senario is that when i want delete the record or truncate the table
i want that the when i will re insert the table with values then the ID column should start
from begining i.e. 1..2..3...and so on instead of starting from last sequence number.

Could you please anyone can help out and provided the tried and tested solution.

MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?

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

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.

ste5anSenior DeveloperCommented:
What use-case? Cause we don't want this behaviour for surrogate keys.
Scott PletcherSenior DBACommented:
If you TRUNCATE the table, the identity (ID) value will automatically reset to its original starting value.
Kent OlsenDBACommented:
TRUNCATE won't automatically reset the SEQUENCE counter.  You'll need to do that as part of the process.

I read into your statement that you want to reuse IDs if/when a row is deleted.  That's a poor idea for several reasons.  It's also a challenge.

If you have keys 1 through 10 and delete keys 3 and 8, there's not a clean way to mark those two keys "available" for reuse when the next insert happens.  The overhead, particularly if there are a lot of rows in the table, could be prohibitive.
johnsoneSenior Oracle DBACommented:
I have to agree that I'm not sure why you want to do this, but as an exercise, it may be possible.

I do not agree that truncating will reset the identity value.  Try it:
create table mytab (id number generated always as identity, n number);
insert into mytab (n) values (1);
insert into mytab (n) values (2);
select * from mytab;
truncate table mytab;
insert into mytab (n) values (1);
insert into mytab (n) values (2);
select * from mytab;

Open in new window

An identity column in Oracle is a table with a sequence and a trigger.  The sequence and trigger are system generated.  They work the same way as building it yourself, just less steps for you.

As Kent said, you'll never be able to fill in gaps using a sequence.  However, in the case of a TRUNCATE, you can reset the sequence back to 1 (or whatever value you want).  First thing you need is the name of the sequence.  If you have created your own sequence, then you need to find the name of it.  If you don't know it and are setting it with a trigger, the name should be in the trigger.

The idea here is to alter the increment by to the negative of the current value to essentially make the next value go back to the original.  You'll need to know the increment value from USER_SEQUENCES (or ALL_ or DBA_), it is typically 1, but always check to be sure.  You can generate that statement with this (this assumes the original start value was 1):
SELECT 'alter sequence <seq_name> increment by ' 
       || To_char(<seq_name>.NEXTVAL *- 1 + 1) 
       || ';' 
FROM   dual; 

Open in new window

Obviously replace <seq_name> with the real sequence name twice.

Now, select the next value and reset the sequence:

select <seq_name>.nextval from dual;

Now, set the increment back:

alter sequence <seq_name> increment by 1;

In this process, you lose one value (because you have to reset the increment by).

You could drop the sequence and recreate it, however, then you need to recreate permissions, recompile dependent objects and things of that sort.

If you created the column as an identity column, the name of the sequence should be this:
       || object_id 
FROM   user_objects 
WHERE  object_name = 'MYTAB' 
       AND object_type = 'TABLE'; 

Open in new window

However, you cannot alter a system generated sequence, so the only way that I can think of to reset this one would be to drop and recreate the table.

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
Scott PletcherSenior DBACommented:
Sorry, I was referring to SQL Server resetting an identity value after truncation.

I have no idea how Oracle handles that.
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

From novice to tech pro — start learning today.