1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

create or replace

procedure reset_seq( p_seq_name in varchar2 )

is

    l_val number;

begin

    execute immediate

    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate

    'alter sequence ' || p_seq_name || ' increment by -' || l_val ||

                                                          ' minvalue 0';

    execute immediate

    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate

    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';

end;

1

2

3

4

5

6

7

8

9

10

SELECT  a.sequence_name 序列名称,

a.min_value 序列最小值,

to_char(to_number( a.max_value)) 序列最大值,

a.last_number 序列当前值,

CASE  a.last_number WHEN 1 THEN '--不需要重置;' ELSE

'CALL seq_reset(''' || a.sequence_name || ''');' END 重置序列脚本,

'DROP SEQUENCE ' || a.sequence_name 删除序列脚本,

'RENAME ' || a.sequence_name || ' TO {newname}'  重命名脚本

FROM USER_SEQUENCES a WHERE a.last_number<>1

ORDER BY a.sequence_name ASC;