To reset existing (Oracle) Sequence.

#Set the value of variable cnt_fx to reset the Oracle Sequence.
#Replace owner and SEQUENCE_name with your schema owner and Sequence name.
Login to Database and execute below SQLs:

set serveroutput on

declare
cnt integer;
— Target sequence number#
— change the number that you want reset or set.

cnt_fx integer :=701;
cnt_final integer;

begin
select owner.SEQUENCE_name.nextval into cnt from dual;
select owner.SEQUENCE_name.currval into cnt from dual;
cnt_final := cnt_fx – cnt;
dbms_output.put_line(cnt_final);
execute immediate ‘alter sequence owner.SEQUENCE_name increment by ‘ ||  cnt_final;
select owner.SEQUENCE_name.nextval into cnt from dual;
dbms_output.put_line(cnt);
execute immediate ‘alter sequence owner.SEQUENCE_name increment by 1’;
end;
/
To see updated Sequence number :

select owner.SEQUENCE_name.currval  from dual;

Advertisements

About deepakguptadba

10 years of experience in Database Administrator for Oracle Database 10g//9i/8i/7.x with total 15 years’ comprehensive experience in IT , Pharma / Biotech , Manufacturing, Construction , Auto & Electronics.
This entry was posted in Shell Script and tagged . Bookmark the permalink.

9 Responses to To reset existing (Oracle) Sequence.

  1. It’s hard to find well-informed people about this topic,
    but you seem like you know what you’re talking about!
    Thanks

  2. I rarely drop comments, however i did some searching and wound up here To reset existing (Oracle) Sequence.

    | My Learning as Oracle DBA. And I do have 2 questions for you if it’s allright.
    Could it be only me or does it give the impression like some of these remarks appear
    like left by brain dead people? 😛 And, if you are posting on additional places, I’d like to keep up with anything fresh you have to post.
    Would you list of every one of all your community sites like your linkedin profile, Facebook page or
    twitter feed?

  3. Julian says:

    This is a good tip particularly to those new to the blogosphere.

    Short but very precise information… Appreciate your sharing
    this one. A must read post!

  4. naturally like your web-site however you need to take a look at the spelling on quite a few of your
    posts. Several of them are rife with spelling problems
    and I find it very bothersome to inform the truth then again I’ll surely come
    back again.

  5. Nice post. I was checking constantly this weblog and I’m inspired!

    Extremely useful information particularly the final part 🙂
    I deal with such info much. I used to be looking for this
    particular info for a long time. Thank you and best of
    luck.

  6. scary maze says:

    Hi colleagues, good post and pleasant urging commented here, I am in fact enjoying by these.

  7. patent says:

    patent, patent tescil, marka patentpatent,
    patent tescil, marka patent
    I’ve been browsing online more than three hours today,
    yet I never found any interesting article like
    yours. It’s pretty worth enough for me. In my opinion, if
    all site owners and bloggers made good content as you did, the
    net will be much more useful than ever before.

  8. xxx says:

    Oh my goodness! Amazing article dude! Thank you, However I
    am encountering troubles with your RSS. I don’t
    understand the reason why I am unable to join it.
    Is there anybody getting identical RSS problems?
    Anybody who knows the answer will you kindly respond?
    Thanx!!

  9. However, the rarer colors (like bay, black, buckskin) will cost you more $$.
    Fuego XII (now Fuego de Cardenas) won the hearts of horse
    lovers from every discipline, inspiring a standing ovation in the 2010 World Equestrian Games after his freestyle
    dressage performance. The Andalusian’s conformation is very strong: arched neck, short-coupled, powerful body, strong hock action and impulsion.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s