關於sequence問題的緊急處理

今天早上收到郵件，說有一個很緊急的問題，是關於sequence的。
錯誤日誌裡面還有ORA的錯誤
-----
        ... 7 more
Caused by: java.sql.SQLException: ORA-08004: sequence TRX_1SQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

猛一看就是sequence的值越界了。
導致這個問題的原因主要有兩個：
1）設定的maxvalue值過小了。
2）sequence的cycle模式沒有啟用

從庫裡檢視sequence的狀態。

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
   TRX_1SQ                             1  999999999            1 N N        100  1000000000

從上面的結果可以看出，sequence的cycle模式沒有啟用，sequence值越界了。
修復問題可以有兩種思路。就是設定maxvalue,或者選擇開啟cycle模式。
先來看看設定maxvalue，問題就來了，需要設定多大，這個得找開發確認，而開發也不確定最大能設定多大。問題又跑到了dba這邊，
關於maxvalue的值，官方文件是這麼描述的。最大28位。
MAXVALUE Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.

有了這個思路，貌似問題簡單了很多。從資料庫層面來說似乎行得通了,設定一個最大值即可。從業務上是不是支援更大的數值呢，這個看似需要開發來確認，但是和dba也是有一些關聯的，我找到sequence相關的表，大概有5張左右的表使用了那個sequence, 檢視對應的表列，可以看到有些欄位是支援的，有些欄位卻是NUMBER(9)，很明顯設定maxvalue會帶來更多的問題。

因為相關的幾個表都是核心表，如果修改表的精度也是有潛在風險的。
這個時候就需要找開發確認，是不是應該開啟cycle模式，對於現有資料是否有影響了。很快得到產品線的回饋，有同樣一個問題在別的專案發生過，需要開啟cycle模式。
對於dba來說，需要做的工作基本就是測試和驗證了。
從生產備份庫中使用dbms_metadata.get_ddl得到sequence的建立語句

SQL> CREATE SEQUENCE  "TRX_1SQ"  MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH 1000000000 CACHE 100 NOORDER  NOCYCLE

    /

Sequence created.

--復現問題

SQL> select trx_1sq.nextval from dual;

select csm_trx_1sq.nextval from dual

       *

ERROR at line 1:

ORA-08004: sequence CSM_TRX_1SQ.NEXTVAL exceeds MAXVALUE and cannot be

instantiated

 

--修復問題

SQL> alter sequence CSM_TRX_1SQ cycle;

Sequence altered.

 --驗證問題

SQL> select csm_trx_1sq.nextval from dual;

   NEXTVAL

----------

         1

SQL> /

   NEXTVAL

----------

         2

 

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

------------------------------ ---------- ---------- ------------ - - ---------- -----------

    TRX_1SQ                             1  999999999            1 Y N        100         101

當然了，如果要設定maxvalue的話。有一個細節需要注意的。
如果設定為28位，最高位是沒有問題的。
SQL> alter sequence csm_trx_1sq maxvalue 9999999999999999999999999999;
Sequence altered.

SQL> select *from user_sequences where sequence_name='TRX_1SQ';
SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
    TRX_1SQ                             1 1.0000E+28            1 Y N        100           3

但是如果你設定了30位，也是不會報錯的，提示執行成功。但是檢視sequence的最大長度，卻還是28位。
SQL> alter sequence csm_trx_1sq maxvalue 999999999999999999999999999999;
Sequence altered.

SQL> select *from user_sequences where sequence_name='CSM_TRX_1SQ';
SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
    TRX_1SQ                             1 1.0000E+28            1 Y N        100           3