Re: Sequences

kiss a écrit dans le message <37D5724D.114C4388_at_yahoo.com>...
>Couple of questions re: sequences
>
>1. What is the "Cycle Flag"?

When Cycle Flag is Y and the sequence reaches its max value (if increment is positive) or min value (if increment is negative) it restart to the min value (respectively, the max value). In this case, if the Cycle Flag is N, Oracle reaches an error.

v734>create sequence s minvalue 1 maxvalue 3 start with 1; Sequence created.
v734>select s.nextval from dual;

   NEXTVAL

         1
1 row selected.
v734>/

   NEXTVAL

         2
1 row selected.
v734>/

   NEXTVAL

         3
1 row selected.
v734>/
ERROR:
ORA-08004: sequence S.NEXTVAL exceeds MAXVALUE and cannot be instantiated no rows selected

v734>alter sequence s cycle;
Sequence altered.

v734>select s.nextval from dual;

   NEXTVAL

         1
1 row selected.
v734>/

   NEXTVAL

         2
1 row selected.
v734>/

   NEXTVAL

         3
1 row selected.
v734>/

   NEXTVAL

         1
1 row selected.

>2. Is there a way to "clean" the actual values up after an export and
>then import? (doubtful, but want to make sure :-)).
>We moved a database from development to production, and the sequence
>values have stopped at a certain value, and then restarted at another
>value way above where they left off. We forgot about the whole caching
>issue, as well as the importing effecting what the values are going to
>be.

No, you cannot clean the current value, you have to drop and recreate the sequence.

>
>Any help would be greatly appreciated.
>
>Thanks in advance.
>
Received on Wed Sep 08 1999 - 04:23:56 CDT