userimage

How do I reset a sequence in Oracle?

I have several tables in my DB.

someone used different sequence to get next value for main table.

But application is using some other sequence to insert record .

Because of this application sequence .nextvalue is less than table value.

So i want to reset the sequence based on the table column.

How can i do this.please help

userimage

We just need to pass sequence name,table_name,column_name to below procedure.

I will reset the sequence to a value based on max value of table column.

create or replace PROCEDURE RESET_SEQUENCE  (  SEQ_NAME IN VARCHAR2  ,

 TABLE_NAME IN VARCHAR2 ,  COLUMN_NAME IN VARCHAR2  ) AS

CURR_VAL NUMBER ;

MAX_VAL NUMBER;

diff number;

BEGIN

EXECUTE IMMEDIATE 'select '||SEQ_NAME||'.nextval from dual' INTO CURR_VAL;

EXECUTE IMMEDIATE 'select max('||COLUMN_NAME||') from '||TABLE_NAME INTO MAX_VAL;

DIFF := (MAX_VAL-CURR_VAL);

IF diff>0

THEN

EXECUTE IMMEDIATE 'alter sequence '||SEQ_NAME||' increment by '||(DIFF+10);

EXECUTE IMMEDIATE 'select '||SEQ_NAME||'.nextval from dual' INTO CURR_VAL;

EXECUTE IMMEDIATE 'alter sequence '||SEQ_NAME||' increment by 1'; 

ELSE NULL;

end if; 

END RESET_SEQUENCE;

Answer is