The new Oracle table uses sequence as the insert value. The initial value is not the first, oraclesequence

The new Oracle table uses sequence as the insert value. The initial value is not the first, oraclesequence

This problem occurs when you use oracle11g to insert data:

 

1 -- CREATE test TABLE -- 2 create table tbl_test (3 test_id number primary key, 4 test_name VARCHAR2 (20) 5 ); 6 7 -- CREATE a SEQUENCE for tbl_test -- 8 create sequence seq_test 9 increment by 1 -- add 10 start with 1 each time -- count 11 from 1; 12 13 -- INSERT test data -- 14 insert into tbl_test VALUES (seq_test.nextval, 'test'); 15 COMMIT; 16 17 -- Query table data 18 SELECT * FROM tbl_test;

 

Display result:

 

 

Cause:

· When we use the sequence as the data insertion, if we use the "delay segment" technology, we will skip the first value of the sequence.

· Oracle versions 11.2.0.1 provide the "Create delay segments" feature:

That is

When we create a new table and sequence ),
When an insert statement is inserted, the first value (1) is skipped in the sequence ).
Therefore, the result is that the inserted Sequence Value starts from 2 (the second value of the sequence), rather than 1.

There are two ways to solve this problem:
Change the "delay segment creation" feature of the database to false (corresponding permissions are required)

ALTER SYSTEM SET deferred_segment_creation=FALSE;

Or
Execute seqment immediately when creating a table, for example:

CREATE TABLE tbl_test(    test_id NUMBER PRIMARY KEY,     test_name VARCHAR2(20)) SEGMENT CREATION IMMEDIATE;

 

The above two methods can solve the previous problem.

In oracle, how does one obtain the sequence in use or in use for a table?

It seems that they are not associated by nature, but they are easy to encounter such problems at work.
From the perspective of sequence, it is only related to users.
You can refer to the following steps:
1. Obtain information about all sequences from the System View.
Pay attention to related permissions.
Select sequence_name, min_value, max_value, increment_by, last_number from all_sequences
If you can exclude user A's table data and use user B's sequence to generate data, you can add
Where sequence_owner = ''; ----- suitable for users
Or extract from USER_SEQUENCES.
2. Extract the maximum value of sensitive fields from the concerned table
Select max (id) from test;
3. compare the two. Or connect the two queries to query them. If the table is not inserted and has not been deleted recently, the max (id) + 1 = last_number;
If you have to figure it out, we will protect the table data and refuse to delete the data. When we find that the data is inserted, we will observe the insertion frequency and the change of the sequence column value after the transaction is committed, then we can determine the serial number. Based on the premise that it is necessary to be accurate, use the discharge method to back up the closest sequence from mild suspicion to high suspicion, delete and restore ..... this will certainly make sense.

There is also the best way to deal with this situation in the future. Create an insert-based trigger on the table. The trigger contains the preceding query idea and returns the sequence name of max (id) + 1 = last_number .... It will never be wrong.
.
Limited knowledge. I hope to learn more advanced methods here.
Hope to help you ..

How to Create a sequence in an oracle table

In oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.
1. Create Sequence
First, you must have the create sequence or create any sequence permission,
Create sequence emp_sequence
Increment by 1 -- add several
Start with 1 -- count from 1
NOMAXVALUE -- do not set the maximum value
NOCYCLE -- always accumulate without repeating
CACHE 10;

Once emp_sequence is defined, you can use CURRVAL, NEXTVAL
CURRVAL = returns the current sequence Value
NEXTVAL = increase the sequence value, and then return the sequence Value
For example:
Emp_sequence.CURRVAL
Emp_sequence.NEXTVAL

Where sequence can be used:
-SELECT statements that do not contain subqueries, snapshot, and VIEW
-The INSERT statement is in the subquery.
-In the value of the NSERT statement
-UPDATE in SET

See the following example:
Insert into emp VALUES
(Em1_q. nextval, 'Lewis ', 'cler', 7902, SYSDATE, 1200, NULL, 20 );

SELECT empseq. currval from dual;

Note that:
-The first NEXTVAL returns the initial value. The subsequent NEXTVAL automatically increases the value of your defined increment by and then returns the added value. CURRVAL always returns the value of the current SEQUENCE, but CURRVAL can be used only after the first NEXTVAL initialization; otherwise, an error will occur. NEXTVAL increases the SEQUENCE value once. Therefore, if you use multiple NEXTVAL values in the same statement, their values are different. Understand?

-If the CACHE value is specified, ORACLE can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence.

2. Alter Sequence
You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.
Alter sequence example
Alter sequence emp_sequence
Increment by 10
Max value 10000
CYCLE -- start from scratch after 10000
NOCACHE;

Initialization parameters that affect Sequence:
SEQUENCE_CACHE_ENTRIES = set to be cached at the same time ...... the remaining full text>