with T (seq) as ( select 1 from dual union all select 2 from dual union all select 3 from dual union all select 4 from dual union all select 6 from dual union all select 7 from dual union all select 8 from dual union all select 9 from dual ) select min(TAB_SEQ.seq) from ( select level seq from dual connect by level <= 10 ) TAB_SEQ, T where TAB_SEQ.seq = T.seq(+) and T.seq is null 대용량 테이블에서는 그리 좋은 방법인 아닐듯 하지만.. TAB_SEQ같은 정렬된 채번 테이블을 미리 생성 후 실행하면 될듯 합니다.
WITH T ( SEQ ) AS ( SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL --SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 5 FROM DUAL UNION ALL SELECT 6 FROM DUAL UNION ALL SELECT 7 FROM DUAL ) SELECT NVL(MIN(CASE WHEN SEQ <> ROWNUM THEN ROWNUM END),NVL(MAX(SEQ),0)+1) FROM (SELECT SEQ FROM T ORDER BY SEQ )