Oracle batch update sequence Storage

Oracle batch update sequence Storage

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 ))

And s. CONSTRAINT_TYPE = 'P'/* CONSTRAINT_TYPE: P: primary key, R: foreign key, C: non-null constraint/CHECK ;*/

--- If the primary key is composed of multiple fields 'id1, id2', the query will display two rows: (T. ID1 SEQ_T and T. ID2 SEQ_T)

)

--... A sequence is shared by two tables/two fields...

UNION

SELECT 'etl _ CS_CUST_INFO_MID ',

'Batch _ no', -- if the data type is VARCHAR, TO_NUMBER must be converted to obtain MAX (field)

'Seq _ ETL_CS_CUST_INFO_MID'

From dual) C,

DBA_TAB_COLUMNS C1

WHERE C1.OWNER = UPPER (USERNAME)

AND C1.COLUMN _ NAME = C. COLUMN_NAME

AND C1.TABLE _ NAME = C. TABLE_NAME)

/**

--- Provide the table name/sequence with the table length greater than 26 .. Then associate DBA_CONS_COLUMNS to find the corresponding primary key field... and query with the table length less than 26 for UNION ALL

CS_BEAR_ALLOWANCE_AND_INJ_DET ---> SEQ_CS_BEAR_ALLOWANCE_INJ_DET

CS_BEAR_ALLOWANCE_AND_INJ_DETS ---> SEQ_CS_BEAR_ALLOWANCE_INJ_DETS...

*/

Union all (SELECT M1.TABLE _ NAME, COLUMN_NAME, M2.SEQUENCE _ NAME

FROM (select length (C. TABLE_NAME) AA,

C. TABLE_NAME,

C. COLUMN_NAME

FROM DBA_CONS_COLUMNS C

Where c. OWNER = UPPER (USERNAME)

AND (C. CONSTRAINT_NAME, C. TABLE_NAME) IN

(Select s. CONSTRAINT_NAME, S. TABLE_NAME

FROM DBA_CONSTRAINTS S

Where s. OWNER = UPPER (USERNAME)

And s. CONSTRAINT_TYPE = 'P') M1 -- if the primary key is NOT restricted, the column not null may be found.

JOIN (SELECT TABLE_NAME, SEQUENCE_NAME

FROM (SELECT 'cs _ BEAR_ALLOWANCE_AND_INJ_DET 'TABLE_NAME,

'Seq _ CS_BEAR_ALLOWANCE_INJ_DET 'SEQUENCE_NAME

FROM DUAL

UNION ALL

SELECT 'cs _ BEAR_ALLOWANCE_AND_INJ_DETS ',

'Seq _ CS_BEAR_ALLOWANCE_INJ_DETS'

From dual) M2

ON M1.TABLE _ NAME = M2.TABLE _ NAME

Where aa> 26) T1,

DBA_SEQUENCES SQ, -- (whether the listed sequence exists in the Database)

DBA_TABLES T -- (whether the listed table exists in the Database). The sequence used by different environments may be different. If this parameter is not added, an error may be reported.

Where sq. SEQUENCE_NAME = T1.SEQUENCE _ NAME1

And t. TABLE_NAME = T1.TABLE _ NAME

And sq. SEQUENCE_OWNER = UPPER (USERNAME)

And t. OWNER = UPPER (USERNAME );

 

-------------------- The preceding query table/Corresponding sequence/primary key field -------------

---------------------- Start to judge whether the sequence needs to be updated -------------

 

BEGIN

---------------------- SEQUENCE determines the update statement -----------------------------

--~~ Note: DBMS_OUTPUT.PUT_LINE (XX) displays the result or query.

-- Execute immediate xx; -- EXECUTE the query of XX

-- Start SEQUENCE. nextval and compare the maximum value of the primary key ..

 

FOR P_C_CONS IN C_CONS LOOP

-- Use C_CONS cursor to correspond to column values

P_TABLE_NAME: = P_C_CONS.TABLE_NAME;

P_COLUMN: = P_C_CONS.COLUMN_NAME;

P_SEQUENCE: = P_C_CONS.SEQUENCE_NAME;

 

--- Each loop is assigned 0 values ..

MAX_ID: = 0;

-- Query the maximum value of a table primary key

P_ SQL: = 'select MAX ('| P_COLUMN |') from' | P_TABLE_NAME;

-- USING MAX_ID

Execute immediate P_ SQL

INTO MAX_ID;

 

-- Query the sequence. nextval Value

P_SEQ_ SQL: = 'select' | P_SEQUENCE | '. NEXTVAL FROM dual ';

-- USING P_SEQ_ SQL

 

Execute immediate P_SEQ_ SQL

INTO P_SEQ_NUM;

 

--- Comparison of SEQUENCE. nextval and the maximum value of the primary key... (if SEQUENCE. nextval <maximum value of the primary key, update the SEQUENCE 'drop-create ')

IF P_SEQ_NUM <MAX_ID THEN

/* DBMS_OUTPUT.PUT_LINE ('drop SEQUENCE '| P_SEQUENCE );*/

 

-- Delete the original incorrect SEQUENCE

Execute immediate 'drop SEQUENCE '| P_SEQUENCE;

P_NEW_COUNT: = 0;

P_NEW_COUNT: = MAX_ID + 1; --> the current maximum value of the primary key + 1 is the SEQUENCE value to be updated to ensure that the primary key value does not conflict when it is added again;

P_ SQL _SEQ: = 'create SEQUENCE '| P_SEQUENCE |

'Minvalue 1 nomaxvalue start with' | P_NEW_COUNT |

'Crement BY 1 CACHE 20'; -- create the correct SEQUENCE statement

/* Print the sequence creation statement */

/* DBMS_OUTPUT.PUT_LINE ('create SEQUENCE '| P_SEQUENCE |

'Minvalue 1 nomaxvalue start with' |

P_NEW_COUNT | 'crement BY 1 CACHE 20 ');*/

-- Execute the create sequence statement

Execute immediate P_ SQL _SEQ;

-- Print the table and sequence corresponding to the error sequence from the previous value to the current value;

DBMS_OUTPUT.PUT_LINE ('table corresponding to the error sequence: '| P_TABLE_NAME |'

'| P_SEQUENCE |' by '|

P_SEQ_NUM | 'Update to '| P_NEW_COUNT | ';');

End if;

End loop;

END P_SYNCSEQ;
 

-- Steps:

-- Edit storage... --> Call storage (Call change_varchar2 (username => 'u1'); or begin... to pass the value)

-- Output result:

-- Drop sequence SEQ_T1 -- comment out the printed part in this article. If you want to see the effect, comment/**/open it.

-- Create sequence SEQ_T1 MINVALUE 1 nomaxvalue start with 1004 increment by 1 CACHE 20 -- comment out the print part in this article. If you want to see the effect, comment.

-- Table corresponding to the error sequence: T1

SEQ_T1 is updated from 1000 to 1004;

Good luck ~