Oracle batch update sequence Storage

Oracle SEQUENCE (SEQUENCE) ASCHEMAOBJECTTHATGENERATESASERIALLISTOFUNIQUENUMBERSFORTABLECOLUMNS. TOUSEA

Sequence of Oracle (SEQUENCE) a schema object that generates a serial list of unique numbers for table columns. TO USE

Preface:

Sequence of Oracle (SEQUENCE) a schema object that generates a serial list of unique numbers for table columns. to use a sequence, your schema must contain the sequence or you must have been granted the select object privilege for another user is sequence. once a sequence is defined, it can be accessed and incremented by multiple users (who have select object privilege for the sequence containing the sequence) with no waiting. the database does not wait for a transaction that has incremented a sequence to complete before that sequence can be incremented again.

Simply put: sequences are generally used to automatically generate primary key values incrementally ..

But is there any situation that will cause the maximum value of the primary key to be greater than the maximum value of the primary key when seq_...nextval is called?

Scenario:

Primary Key table-> T table '100w' data synchronization to table T1 (Synchronization: TRUNCATE/DELETE or IMPDP... and other methods [manual data modification])

For example, the T table corresponds to SEQ_T.NEXTVAL = 100 W;

Table T1 corresponds to SEQ_T.NEXTVAL = 10 W;

Truncate table T1;

Insert table T1 SELECT * from t;

Data Synchronization, but the SEQ_T.NEXTVAL = 10 W sequence corresponding to table T1 remains unchanged;

At this point, when T1 calls the sequence INSERT to table T1, an error is reported (ORA-00001: A violation of the unique constraint (LOTTERY. PK_T1 ))

(If many tables are synchronized in batches, many table sequences may need to be updated to the maximum value of the primary key + 1 to ensure that no error is reported.

(You can synchronize the SEQUENCE of the source database. ① or solve the problem as follows. ② ))

① The PLSQL tool's compare user objects can solve the problem of synchronizing sequence objects to other environments...

② Our environment sequence is generally composed of SEQ _ table names, so it is easier to write and update storage in batches ~

The following storage is applicable to environments that are commonly used in the preceding scenarios. The production environment is rarely used to import new data in batches, synchronize user data, or synchronize table data...

-- Batch update sequence storage --
Create or replace procedure P_SYNCSEQ (USERNAME VARCHAR2/* input the user who wants to check/update the sequence */) IS

/*

** @ AUTHOR Mao haiqing

ORACLE batch update SEQUENCE

Note:

Batch update SEQUENCE,

Next value of the update sequence = maximum value of the primary key + 1

--- When the sequence is created, the attribute NOMAXVALUE = the maximum value is 10 to the power of 28

Ideas:

1. Find the primary key column of each table and what is the maximum value of the primary key in this table?

2. Find the SEQUENCE value corresponding to the table and compare it with the maximum value of the table primary key.

If the next value of SEQUENCE is greater than the maximum value of the table's primary key, no update is performed;

Otherwise, update is required (update method in 2)

1) Delete the SEQUENCE and create a new SEQUENCE. The starting value is the maximum value of the table's primary key plus 1. -- this article selects this scheme ~

(Disadvantage: Fortunately, the drop sequence... program also happens to call functions and stored procedures dependent on it will become invalid.

However, after the create sequence statement is executed, it will re-compile the call... no error will be reported ~)

2) alter sequence... increment by... NOCACHE;

SELECT... nextval from dual;

Alter sequence .. increment by 1 CACHE 20;

... Sequence. nextval can also be implemented using the user_sequences.last_number field. Because the earliest storage, this write is not changed ~... Understanding ~

The maximum length of SEQUENCE and table names is 30.

SEQUENCE specification name SEQ _ + Table name -- this specification is only convenient for management and maintenance.

If the table name length is greater than 26 and "SEQ _" is greater than 30 of the SEQUENCE length limit

If the table name length is greater than 26, the corresponding sequence is certainly not a standard name (SEQ _ table name), and since there are not many such sequences, these are processed one by one.

You can comment out execute immediate before updating, and test the effect first to avoid an error when execute immediate drop. is created. As a result, the call sequence is not created and the sequence cannot be verified.

Required permissions:
-- Create sequence permission --

-- Grant/Revoke system privileges
Grant create sequence to LOTTERY;
-- Query permission --
-- Grant/Revoke object privileges
Grant select on DBA_CONSTRAINTS to LOTTERY;
Grant select on DBA_CONS_COLUMNS to LOTTERY;
Grant select on DBA_SEQUENCES to LOTTERY;
Grant select on DBA_TABLES to LOTTERY;
Grant select on DBA_TAB_COLUMNS to LOTTERY;
-- Or --
-- Grant/Revoke system privileges
Grant select any dictionary to LOTTERY;

*/

-- Variable

MAX_ID NUMBER (12 );

P_SEQ_NUM NUMBER (12 );

P_TABLE_NAME VARCHAR2 (50 );

P_COLUMN VARCHAR2 (50 );

P_SEQUENCE VARCHAR2 (50 );

P_ SQL VARCHAR2 (500 );

P_SEQ_ SQL VARCHAR2 (5000 );

P_ SQL _SEQ VARCHAR2 (30000 );

P_NEW_COUNT NUMBER (12 );

-- Query tables/sequences with a table length less than 26

-- Cursor

CURSOR C_CONS IS -- query a table/sequence with a table length less than 26

SELECT T1.TABLE _ NAME TABLE_NAME,

T1.COLUMN _ NAME COLUMN_NAME,

T1.SEQUENCE _ NAME1 SEQUENCE_NAME

FROM (select c. TABLE_NAME,

CASE

WHEN C1.DATA _ TYPE = 'number' THEN

C. COLUMN_NAME

ELSE

'To _ NUMBER ('| C. COLUMN_NAME | ')'

END COLUMN_NAME,

C. SEQUENCE_NAME1

FROM (select c. TABLE_NAME,

C. COLUMN_NAME,

'Seq _ '| C. TABLE_NAME SEQUENCE_NAME1

FROM DBA_CONS_COLUMNS C -- table column information corresponding to the user's constraints

Where c. OWNER = UPPER (USERNAME)

AND (C. CONSTRAINT_NAME, C. TABLE_NAME) IN

(Select s. CONSTRAINT_NAME, S. TABLE_NAME

FROM DBA_CONSTRAINTS S -- user's object constraint information

Where s. OWNER = (UPPER (USERNAME ))