Thinking Out Loud

March 6, 2013

Resetting Sequences

Filed under: 11g,oracle — mdinh @ 7:09 am

I know what you are thinking. Why on earth would you reset a sequence? Let me tell you why.

Schema A ‘s tables are being refreshed from production to development database.

Schema A contains the data and references sequences in Schema B.

From the scheme for things, sequence > max(value) from table or sequence < max(value) from table.

max(value) from table:

LAX:(MDINH@db01)> select max(id) from t;
100

sequence > max(value):

LAX:(MDINH@db01)> select s.nextval from dual;
1978979888

Sequence reset:

LAX:(MDINH@db01)> DECLARE
  l_seq NUMBER;
  2    3  BEGIN
  4    FOR x IN(
  5      SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
  6    ) LOOP
  7      EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
  8      SELECT s.nextval INTO l_seq FROM dual;
  9      EXECUTE immediate 'alter sequence s increment by 1';
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> select s.nextval from dual;
       101

sequence < max(value):

LAX:(MDINH@db01)> drop sequence s;

Sequence dropped.

LAX:(MDINH@db01)> create sequence s;

Sequence created.

LAX:(MDINH@db01)> select s.nextval from dual;
         1

Sequence reset:

LAX:(MDINH@db01)> DECLARE
  2    l_seq NUMBER;
  3  BEGIN
  4    FOR x IN(
  5      SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
  6    ) LOOP
  7      EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
  8      SELECT s.nextval INTO l_seq FROM dual;
  9      EXECUTE immediate 'alter sequence s increment by 1';
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> select s.nextval from dual;
       101

LAX:(MDINH@db01)>

Happy Resetting!

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 302 other followers

%d bloggers like this: