To fix this problem you can try to find where your sequences are used and get the MAX(value) to find the next value. Alternatively you can just add a large random number, say 1,000,000, to all your sequences. For most users this will fix the problem and is very easy to do. Here's how:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | -- Update all sequences DECLARE v_increase_by NUMBER; v_bkp_increment_by NUMBER; v_str VARCHAR2 (1000); v_count NUMBER; BEGIN v_increase_by := 1000000; FOR rec IN ( SELECT * FROM user_sequences) LOOP -- Backup current incrementation number v_bkp_increment_by := rec.increment_by; -- Alter the sequence to increase by a defined amount v_str := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_increase_by; EXECUTE IMMEDIATE v_str; -- Increase by that amount v_str := 'select ' || rec.sequence_name || '.nextval from dual' ; EXECUTE IMMEDIATE v_str INTO v_count; -- Reset the increment factor v_str := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_bkp_increment_by; EXECUTE IMMEDIATE v_str; END LOOP; END ; / |
I got this
ReplyDelete"Invalid start of code"
what did I do wrong again =)
You have to use it in a function or open it in a test window not sql window.
Deletethanks very much, worked a treat and saved me a lot of bother
ReplyDeletethanks, saved me a lot of trouble
ReplyDeleteA solution for the first option, updating based on the last primary key value:
ReplyDeleteCREATE OR REPLACE
PROCEDURE UPDATE_ALL_SEQUENCES AS
PRAGMA AUTONOMOUS_TRANSACTION;
MAX_PK NUMBER;
CURR_PK NUMBER;
BEGIN
FOR REC IN
(
SELECT
TABS.TABLE_NAME,
SEQS.SEQUENCE_NAME,
COLS.COLUMN_NAME
FROM
USER_TABLES TABS,
USER_TRIGGERS TRIGS,
USER_DEPENDENCIES DEPS,
USER_SEQUENCES SEQS,
USER_CONSTRAINTS CONS,
USER_CONS_COLUMNS COLS
WHERE
TRIGS.TABLE_NAME = TABS.TABLE_NAME AND
DEPS.NAME = TRIGS.TRIGGER_NAME AND
SEQS.SEQUENCE_NAME = DEPS.REFERENCED_NAME AND
TABS.TABLE_NAME = COLS.TABLE_NAME AND
COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME AND
COLS.OWNER = CONS.OWNER AND
CONS.CONSTRAINT_TYPE = 'P' AND
COLS.COLUMN_NAME NOT LIKE '%_FK%'
)
LOOP
EXECUTE IMMEDIATE 'SELECT MAX('|| REC.COLUMN_NAME ||') FROM ' || REC.TABLE_NAME || '' INTO MAX_PK;
IF(MAX_PK IS NULL) THEN
MAX_PK := 1;
END IF;
EXECUTE IMMEDIATE 'SELECT ' || REC.SEQUENCE_NAME ||'.NEXTVAL FROM DUAL' INTO CURR_PK;
IF(MAX_PK > CURR_PK) THEN
CURR_PK := MAX_PK - CURR_PK;
EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| REC.SEQUENCE_NAME ||' INCREMENT BY ' || CURR_PK;
EXECUTE IMMEDIATE 'SELECT '|| REC.SEQUENCE_NAME ||'.NEXTVAL FROM DUAL' INTO MAX_PK;
EXECUTE IMMEDIATE 'ALTER SEQUENCE '|| REC.SEQUENCE_NAME ||' INCREMENT BY 1';
END IF;
END LOOP;
END UPDATE_ALL_SEQUENCES;
Hi..Mr.Martin
ReplyDeleteThanks a lot.. It works well.