Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Sequence Value after Export
Problem with Sequence Value after Export [message #201112] Thu, 02 November 2006 10:51 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi ,

We have issue in Test DB when we took an full export from PRODUCTION and imported in Test Environment, the id columns and many tables have values say for example sale_id#=5590100 where as the sequence value which generates the sequence number for this column id has nextval as 5590040.

so when application tries to insert a new record says Unique constraint voilated because value is already there in tables and it generates the nextval.

can get a list off all sequences whose values is less than column value so that i can reset the sequence numbers.

I know there is no relation between tables and sequences but is there any way to resolve this issue instead of looking 75 sequences at a time and resetting them each one.

Please help me with any ideas on this.

Thanks
Re: Problem with Sequence Value after Export [message #201117 is a reply to message #201112] Thu, 02 November 2006 11:18 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you can extract all the starting points for each sequence from test and the update the sequences in prod.

CREATE OR REPLACE PROCEDURE reset_seq (p_seq_name IN VARCHAR2, p_start_num IN NUMBER DEFAULT 1)
/*
|| Use this proc to reset a specified Sequence to a specified Starting number.
|| Allows sequences to be set on one environment to match those on another
|| environment without dropping re-creating them (which invalidates packages
|| and loses grants on related synonyms).
|| usage:
||    SELECT 'begin util.reset_seq(''' || sequence_name || ''', ' || last_number || ' ); end;'
||         || CHR (10)|| '/' || CHR (10)
||    FROM user_sequences;
||
||   begin util.reset_seq('test_seq', 100); end;
||   /
||
*/
AUTHID CURRENT_USER
IS
   l_val   NUMBER;
   l_gap   NUMBER;
BEGIN
   
   -- get the current val
   EXECUTE IMMEDIATE 'select ' || p_seq_name || '.nextval from dual'
      INTO l_val;
   l_gap := l_val - (p_start_num - 1);

   IF l_gap > l_val
   THEN
      l_gap := l_val;
   END IF;

   IF l_gap != 0
   THEN
      EXECUTE IMMEDIATE 'alter sequence ' || p_seq_name || ' increment by '
                        || TO_CHAR (-1 * l_gap) || ' minvalue 0';
      
      -- adjust from old to new value
      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 IF;
END;
/



Assuming the PK column name, table name and sequence name are similarly named, you can also query the tables for max values and then use those to reset the target sequences to.

CREATE OR REPLACE function get_max_id( p_tname in varchar2, p_colname in varchar2 ) return number
as
    x    number default NULL;
begin
    execute immediate 'select max('||p_colname||') from ' || p_tname INTO x;
    return x;
end;
/

SELECT 'begin util.reset_seq(''' || sequence_name || ''', ' || 'get_max_id(''' || b.table_name
       || ''', ''' || c.column_name || ''')+1' || ' ); end;' || CHR (10) || '/' || CHR (10)
  FROM user_sequences a, user_tables b, user_tab_columns c
 WHERE b.table_name || '_SEQ' = a.sequence_name 
 AND b.table_name = c.table_name 
 AND c.column_id = 1;
	
-- e.g.
--begin util.reset_seq('DOC_SEQ', get_max_id('DOC', 'DOC_ID')+1 ); end;
--/

[Updated on: Thu, 02 November 2006 11:19]

Report message to a moderator

Re: Problem with Sequence Value after Export [message #201118 is a reply to message #201112] Thu, 02 November 2006 11:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
How are the sequences defined?
http://www.orafaq.com/forum/m/122997/42800/?srch=nocache#msg_122997

Andrew was faster than me Smile
I was about to the mention the same suggestion here
http://www.orafaq.com/forum/m/22447/42800/?srch=reset_seq#msg_22447

[Updated on: Thu, 02 November 2006 11:23]

Report message to a moderator

icon14.gif  Re: Problem with Sequence Value after Export [message #201122 is a reply to message #201118] Thu, 02 November 2006 11:57 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Andrew and Mahesh,

It worked,

Thanks Again for prompt Support.


Regards
Re: Problem with Sequence Value after Export [message #201356 is a reply to message #201122] Fri, 03 November 2006 12:00 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

One more thing on sequences is
I am trying to add 100000 to all the sequences values,
i am trying with this but doesn't work.
Aim to reset the values of all sequences to nextval+100000

select 'alter sequence  ' || sequence_name||' increment by 100000 from dual;' from user_sequences


How can i do in single or 2 statements. Please.


Thanks
Re: Problem with Sequence Value after Export [message #201360 is a reply to message #201356] Fri, 03 November 2006 13:09 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You don't need DUAL here:
SELECT 'alter sequence ' || sequence_name || ' increment by 10000;' FROM user_sequences;


However, as soon as you select first NEXTVAL from the table, set INCREMENT BY back to previous value (was it 1 or some other value).

To select next value, use such a statement:
SELECT 'select ' || sequence_name || '.nextval from dual;' FROM user_sequences;

To return increment to 1 (for example), get back to first SELECT:
SELECT 'alter sequence ' || sequence_name || ' increment by 1;' FROM user_sequences;
Previous Topic: Retrieving java permissions
Next Topic: sql outer join help needed.
Goto Forum:
  


Current Time: Sat Dec 03 05:59:42 CST 2016

Total time taken to generate the page: 0.14630 seconds