Oracle sequence изменить nextval
В Oracle/PLSQL, вы можете создать автонумерацию с помощью последовательности. Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда вам нужно создать уникальный номер в качестве первичного ключа.
8 Answers 8
You can temporarily increase the cache size and do one dummy select and then reset the cache size back to 1. So for example
Please check out Leniel Macaferi's answer if you just simply want to reset the start value to a number. alter sequence
In my case I have a sequence called PS_LOG_SEQ which had a LAST_NUMBER = 3920 .
I then imported some data from PROD to my local machine and inserted into the PS_LOG table. Production data had more than 20000 rows with the latest LOG_ID (primary key) being 20070. After importing I tried to insert new rows in this table but when saving I got an exception like this one:
Surely this has to do with the Sequence PS_LOG_SEQ associated with the PS_LOG table. The LAST_NUMBER was colliding with data I imported which had already used the next ID value from the PS_LOG_SEQ .
To solve that I used this command to update the sequence to the latest \ max(LOG_ID) + 1:
This command reset the LAST_NUMBER value and I could then insert new rows into the table. No more collision. :)
Note: this alter sequence command is new in Oracle 12c.
Note: this blog post documents the ALTER SEQUENCE RESTART option does exist, but as of 18c, is not documented; it is apparently intended for internal Oracle use.
I'm trying to create a script to migrate data from one DB to another. One thing I'm not currently able to do is set the nextval of a sequence to the nextval of a sequence in another DB.
I got the difference in values from user_sequences and generated the following dynamic SQL statements:
But nothing happens. What am I missing? If I run the same statements outside the procedure, they work fine:
EDIT: Apologies to all for not being clear. I'm actually altering the sequence in the same DB. I'm only getting the value to be set from a remote DB. Perhaps it was unnecessary to mention the remote DB as it doesn't affect things. I only mentioned it to explain what my goals were.
Step 1. I get the nextval of the sequence from a remote DB.
Step 2. I generate dynamic SQL statements with this value:
No error was raised, but nothing happened. When I wrote the SQL statements with DBMS_OUTPUT.PUT_LINE and ran them outside they worked.
@[APC] Yes, I don't get any error message (errors are logged). Sorry I was not clear before - I'm actually altering the sequence from the same DB that owns it. The PL/SQL procedure completes successfully. I'm using 10g 10.2.0.5.0 - 64bi
Hmm I just tried it without dynamic SQL - it won't me put any alter statements inside the PL/SQL block, unless it's dynamic SQL. It says "Encountered the symbol "ALTER" when expecting one of the following. ". My intention is to have a procedure I can run to refresh data when I need it. And I will need to have a PL/SQL block to get the value from the remote DB - it won't work with a subquery to get the value for "alter sequence myseq increment by X":
@[APC] Hmm I just tried it without dynamic SQL - it won't me put any alter statements inside the PL/SQL block, unless it's dynamic SQL. It says "Encountered the symbol "ALTER" when expecting one of the following. ". My intention is to have a procedure I can run to refresh data when I need it. And I will need to have a PL/SQL block to get the value from the remote DB - it won't work with a subquery to get the value for "alter sequence myseq increment by X".
4 Answers 4
Here is some code which dynamically sets a sequence to a new (higher) value. I have written this so it will work for any sequence in your schema.
The procedure doesn't need a COMMIT because DDL statements issue an implicit commit (two in fact).
You can execute it and see the synced value like this (in SQL*PLus):
Incidentally, the only way to reset a sequence (to its original starting value or a different lower value) is dropping and re-creating the sequence.
In 18c Oracle added a RESTART capability to ALTER SEQUENCE. The straightforward option .
. resets the sequence to the value specified by the START WITH clause in the original CREATE SEQUENCE statement. The other option allows us to specify a new starting point:
Excitingly this new starting point can be ahead or behind the current value (within the usual bounds of a sequence).
The one snag is that this new capability is undocumented (only for Oracle's internal usage) and so we're not supposed use it. Still true in 20c. The only approved mechanism for changing a sequence's value is what I outlined above.
Пример
Рассмотрим на примере, как удалить последовательность в Oracle.
Этот пример удалит последовательность supplier_seq .
Пример
Этот код создаст объект последовательность под названием supplier_seq. Первый номер последовательности 1, каждый последующий номер будет увеличиваться на 1 (т.е.. 2,3,4, . ). Это будет кэшировать до 20 значений для производительности.
Если вы опустите параметр MAXVALUE , ваша последовательность по умолчанию до:
Таким образом, вы можете упростить CREATE SEQUENCE. Написав следующее:
Теперь, когда вы создали объект последовательности для автонумерации поля счетчика, мы рассмотрим, как получить значение из этого объекта последовательности. Чтобы получить следующее значение, вам нужно использовать NEXTVAL .
Например:
Это позволит извлечь следующее значение из последовательности supplier_seq . Предложение NEXTVAL нужно использовать в SQL запросе. Например:
Этот isert запрос будет вставлять новую запись в таблицу suppliers (поставщики). Полю Supplier_id будет присвоен следующий номер из последовательности supplier_seq . Поле supplier_name будет иметь значение 'Kraft Foods'.
DROP SEQUENCE
После того как вы создали последовательность в Oracle, вам можете понадобиться удалить её из базы данных.
Синтаксис
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
sequence_name имя последовательности, которую вы хотите создать.
CREATE SEQUENCE
ЧАСТО ЗАДАВАЕМЫЕ ВОПРОСЫ
Вопрос: При создании последовательности, что означают опции cache и nocache ? Например, можно создать последовательность с опцией cache 20 следующим образом:
Или вы могли бы создать такую же последовательность, но с опцией nocache :
Ответ: Что касается последовательности, опция cache определяет, сколько значений последовательности будут сохранены в памяти для быстрого доступа.
Недостатком создания последовательности с cache, что если происходит отказ системы, все кэшированные значения последовательности, которые не были использованы, будут утеряны. Это приведет к разрывам в значениях, назначенной последовательности. Когда в система восстановится, Oracle будет кэшировать новые номера, с того места, где была прервана последовательность, игнорируя утерянные значения последовательности.
Примечание: Для восстановления утраченных значений последовательности, вы всегда можете выполнить команду ALTER SEQUENCE для сброса счетчика на правильное значение.
nocache означает, что ни одно из значений последовательности не хранятся в памяти. Эта опция может понизить производительность, однако, вы не должны столкнуться с разрывами в значениях, назначенной последовательности.
Вопрос: Как установить значение lastvalue в последовательность Oracle?
Ответ: Вы можете изменить lastvalue для последовательности Oracle, выполнив команду ALTER в последовательности.
Например, если последнее значение используемой последовательности Oracle был 100, и вы хотите, чтобы следующее значение было 225. Вы должны выполнить следующие команды.
Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.
CREATE SEQUENCE for additional information on sequences
The sequence must be in your own schema, or you must have the ALTER object privilege on the sequence, or you must have the ALTER ANY SEQUENCE system privilege.
The ke yword s and p arameters in this statement serve the same purposes they serve when you create a sequence.
If you change the INCREMENT BY value before the first invocation of NEXTVAL , then some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value.
Specify RESTART to reset NEXTVAL to MINVALUE for an ascending sequence. For a descending sequence RESTART resets NEXTVAL to MAXVALUE .
To restart the sequence at a different number, specify RESTART with the START WITH clause to set the value at which the sequence restarts.
If you alter the sequence by specifying the KEEP or NOKEEP clause between runtime and failover of a request, then the original value of NEXTVAL is not retained during replay for Application Continuity for that request.
Oracle Database performs some validations. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.
CREATE SEQUENCE for information on creating a sequence and DROP SEQUENCE for information on dropping and re-creating a sequence
Use SCALE to enable sequence scalability. When SCALE is specified, a numeric offset is affixed to the beginning of the sequence which removes all duplicates in generated values.
If you specify EXTEND with SCALE the generated sequence values are all of length (x+y) , where x is the length of the scalable offset (default value is 6), and y is the maximum number of digits in the sequence (maxvalue/minvalue) .
When you use SCALE it is highly recommended that you not use ORDER simultaneously on the sequence.
NOEXTEND is the default setting for the SCALE clause. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence (maxvalue/minvalue) . This setting is useful for integration with existing applications where sequences are used to populate fixed width columns.
Use this clause to generate unique sequence numbers across shards.
The sequence object is created as a global, all-shards sharded object that returns unique sequence values across all shards. The sequence object is also created at the catalog database that returns unique sequence values relative to the shard databases.
The EXTEND and NOEXTEND keywords define the behavior of a sharded sequence.
When you specify EXTEND with the SHARD clause, the generated sequence values are all of length (x + y) , where x is the length of an(a) SHARD offset of size 4. The size 4 corresponds to the width of the maximum number of shards i.e. 1000 affixed at the beginning of the sequence values. y is the maximum number of digits in the sequence maxvalue/minvalue .
The default setting for the SHARD clause is NOEXTEND .
When you specify NOEXTEND , the generated sequence values are at most as wide as the maximum number of digits in the sequence maxvalue/minvalue . This setting is useful for integration with existing applications where sequences are used to populate fixed width columns.
If you call NEXTVAL on a sequence with SHARD NOEXTEND specified, a user error is thrown, if the generated value requires more digits of representation than the maxvalue/minvalue of the sequence.
Sequence with SHARD and SCALE
If you specify the SCALE and the SHARD clauses together, the sequence generates scalable, globally unique values within a shard database for multiple instances and sessions.
If you specify EXTEND with the SCALE and SHARD clauses, the generated sequence values are all of length (x+y+z) , where x is the length of a SHARD offset with a default value of size 4, y is the length of the scalable offset with a default value of 6(5), and z is the maximum number of digits in the sequence maxvalue/minvalue .
If you specify EXTEND or NOEXTEND with the SHARD and SCALE clauses, it applies to both SHARD and SCALE . You do not need to specify EXTEND or NOEXTEND separately. If you specify the EXTEND or NOEXTEND option separately for both the SHARD and SCALE clauses, with the same or different value, a parsing error results, with a message of a duplicate or conflicting EXTEND clause.
When you use SHARD it is highly recommended that you not use ORDER simultaneously on the sequence.
You can use SHARD with CACHE and NOCACHE modes of operation.
Modifying a Sequence: Examples
This statement sets a new maximum value for the customers_seq sequence, which was created in "Creating a Sequence: Example" :
Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.
CREATE SEQUENCE for additional information on sequences
The sequence must be in your own schema, or you must have the ALTER object privilege on the sequence, or you must have the ALTER ANY SEQUENCE system privilege.
The ke yword s and p arameters in this statement serve the same purposes they serve when you create a sequence.
If you change the INCREMENT BY value before the first invocation of NEXTVAL , then some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value.
Specify RESTART if you want to reset NEXTVAL to MINVALUE for an ascending sequence. For a descending sequence RESTART resets NEXTVAL to MAXVALUE .
To restart the sequence at a different number, specify RESTART with the START WITH clause to set the value at which the sequence restarts.
If you alter the sequence by specifying the KEEP or NOKEEP clause between runtime and failover of a request, then the original value of NEXTVAL is not retained during replay for Application Continuity for that request.
Oracle Database performs some validations. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.
CREATE SEQUENCE for information on creating a sequence and DROP SEQUENCE for information on dropping and re-creating a sequence
Use SCALE to enable sequence scalability. When SCALE is specified, a numeric offset is affixed to the beginning of the sequence which removes all duplicates in generated values.
If you specify EXTEND with SCALE the generated sequence values are all of length (x+y) , where x is the length of the scalable offset (default value is 6), and y is the maximum number of digits in the sequence (maxvalue/minvalue) .
When you use SCALE it is highly recommended that you not use ORDER simultaneously on the sequence.
NOEXTEND is the default setting for the SCALE clause. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence (maxvalue/minvalue) . This setting is useful for integration with existing applications where sequences are used to populate fixed width columns.
Modifying a Sequence: Examples
This statement sets a new maximum value for the customers_seq sequence, which was created in "Creating a Sequence: Example" :
For some reason, people in the past have inserted data without using sequence.NEXTVAL. So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use in the table.
How can I update the next value so that it is usable? Right now, I'm just inserting over and over until it's successful ( INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL) ), and that syncs up the nextval.
The problem with dropping and recreating the sequence is that you have to re-apply any grants on it (not to mention that it temporarily invalidates any views or PL/SQL that refers to it).
@JeffreyKemp Exactly, so after I did that, I came up with the routines in the self-answer I posted here and accepted.
Синтаксис:
sequence_name имя последовательности, которую вы хотите удалить.
Читайте также: