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

--序列增加区分
--删除未使用序列表
DECLARE V_CNT INT;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_BIZ_DETAIL_ID';
  IF V_CNT=1 THEN 
    BEGIN
	  EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_INTELLECT_BIZ_DETAIL_ID';
    END;
  END IF;
END;
/
---------------------------------------------------------------
 
--添加SEQ_INTELLECT_BIZ_ID_0
DECLARE V_CNT INT;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_BIZ_ID_0';
  IF V_CNT=1 THEN 
    BEGIN
	  EXECUTE 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;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_INTELLECT_BIZ_ID_1';
  IF V_CNT=1 THEN 
    BEGIN
	  EXECUTE 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);
BEGIN
  SELECT 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;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_PRODUCER_LOG_ID_0';
  IF V_CNT=1 THEN 
    BEGIN
	  EXECUTE 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;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_PRODUCER_LOG_ID_1';
  IF V_CNT=1 THEN 
    BEGIN
	  EXECUTE 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);
BEGIN
  SELECT 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;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_BIZ_LOG_DETAIL_ID_0';
  IF V_CNT=1 THEN 
    BEGIN
	  EXECUTE 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;
BEGIN
  SELECT COUNT(*) INTO V_CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_BIZ_LOG_DETAIL_ID_1';
  IF V_CNT=1 THEN 
    BEGIN
	  EXECUTE 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);
BEGIN
  SELECT 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;