ORACLE XXX序列 goes below MINVALUE 无法实例化的处理办法

--序列增加区分
--删除未使用序列表
DECLARE V_CNT INT;
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_BIZ_DETAIL_ID';IF V_CNT=1 THEN BEGINEXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_INTELLECT_BIZ_DETAIL_ID';END;END IF;
END;
/
-----------------------------------------------------------------添加SEQ_INTELLECT_BIZ_ID_0
DECLARE V_CNT INT;
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_BIZ_ID_0';IF V_CNT=1 THEN BEGINEXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_INTELLECT_BIZ_ID_0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END;ELSE EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_INTELLECT_BIZ_ID_0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END IF;
END;
/--添加SEQ_INTELLECT_BIZ_ID_1
DECLARE V_CNT INT;
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_BIZ_ID_1';IF V_CNT=1 THEN BEGINEXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_INTELLECT_BIZ_ID_1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END;ELSE EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_INTELLECT_BIZ_ID_1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END IF;  
END;
/--删除原有的SEQ_INTELLECT_BIZ_ID
DECLARE V_CNT INT;V_NUM INT; sequence_name VARCHAR2(100);
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_BIZ_ID';IF V_CNT=1 THEN sequence_name := 'SEQ_INTELLECT_BIZ_ID';EXECUTE IMMEDIATE 'SELECT '||sequence_name||'.NEXTVAL FROM dual' INTO V_NUM;EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_INTELLECT_BIZ_ID_0 INCREMENT BY ' || V_NUM || ' MINVALUE 0';EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_INTELLECT_BIZ_ID_1 INCREMENT BY ' || V_NUM || ' MINVALUE 0';EXECUTE IMMEDIATE 'SELECT SEQ_INTELLECT_BIZ_ID_0.nextval FROM dual' INTO V_NUM;EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_INTELLECT_BIZ_ID_0 INCREMENT BY 1';EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_INTELLECT_BIZ_ID_1 INCREMENT BY 1';EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_INTELLECT_BIZ_ID';END IF;
END;
/
---------------------------------------------------------------DECLARE V_CNT INT;
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_PRODUCER_LOG_ID_0';IF V_CNT=1 THEN BEGINEXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_PRODUCER_LOG_ID_0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END;ELSE EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_PRODUCER_LOG_ID_0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END IF;  
END;
/DECLARE V_CNT INT;
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_PRODUCER_LOG_ID_1';IF V_CNT=1 THEN BEGINEXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_PRODUCER_LOG_ID_1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END;ELSE EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_PRODUCER_LOG_ID_1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END IF;  
END;
/--删除原有的SEQ_INTELLECT_PRODUCER_LOG_ID
DECLARE V_CNT INT;V_NUM INT; sequence_name VARCHAR2(100);
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_PRODUCER_LOG_ID';IF V_CNT=1 THEN sequence_name := 'SEQ_INTELLECT_PRODUCER_LOG_ID';EXECUTE IMMEDIATE 'SELECT '||sequence_name||'.NEXTVAL FROM dual' INTO V_NUM;EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_PRODUCER_LOG_ID_0 INCREMENT BY ' || V_NUM || ' MINVALUE 0';EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_PRODUCER_LOG_ID_1 INCREMENT BY ' || V_NUM || ' MINVALUE 0';EXECUTE IMMEDIATE 'SELECT SEQ_PRODUCER_LOG_ID_0.nextval FROM dual' INTO V_NUM;EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_PRODUCER_LOG_ID_0 INCREMENT BY 1';EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_PRODUCER_LOG_ID_1 INCREMENT BY 1';EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_INTELLECT_PRODUCER_LOG_ID';END IF;
END;
/--------------------------------------------------------------------DECLARE V_CNT INT;
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_BIZ_LOG_DETAIL_ID_0';IF V_CNT=1 THEN BEGINEXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END;ELSE EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END IF;  
END;
/DECLARE V_CNT INT;
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_BIZ_LOG_DETAIL_ID_1';IF V_CNT=1 THEN BEGINEXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END;ELSE EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 999999999999999999 NOCYCLE CACHE 20 NOORDER';END IF;  
END;
/--删除原有的SEQ_INTELLET_BIZ_LOG_DETAIL_ID
DECLARE V_CNT INT;V_NUM INT; sequence_name VARCHAR2(100);
BEGINSELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLET_BIZ_LOG_DETAIL_ID';IF V_CNT=1 THEN sequence_name := 'SEQ_INTELLET_BIZ_LOG_DETAIL_ID';EXECUTE IMMEDIATE 'SELECT '||sequence_name||'.NEXTVAL FROM dual' INTO V_NUM;EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_0 INCREMENT BY ' || V_NUM || ' MINVALUE 0';EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_1 INCREMENT BY ' || V_NUM || ' MINVALUE 0';EXECUTE IMMEDIATE 'SELECT SEQ_BIZ_LOG_DETAIL_ID_0.nextval FROM dual' INTO V_NUM;EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_0 INCREMENT BY 1';EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_BIZ_LOG_DETAIL_ID_1 INCREMENT BY 1';EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_INTELLET_BIZ_LOG_DETAIL_ID';END IF;
END;
/EXIT;