Re: Sequence value after data refresh

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 2 Apr 2011 10:36:02 -0700 (PDT)
Message-ID: <65b4659e-4e31-4c60-a4d2-7c0e36a0a760_at_k7g2000yqj.googlegroups.com>

On Apr 1, 5:15 pm, onedbguru <onedbg..._at_yahoo.com> wrote:
> On Mar 31, 1:56 pm, sankarKK <xta..._at_gmail.com> wrote:
>
>
>
>
>
> > hi ,
>
> > after refreshing testdb from proddb  I'm having an issue
>
> >  We have 100 records in a table in testdb. Sequence=100.
> >     200 records have been copied over from proddb. Sequence is still
> > 100.
> > When we try to insert next record, trigger tries to insert  value 101
> > for the
> > sequence, and it causes error (value not unique). So, value of
> > sequence has be
> > be taken from the db where we get data.
> > I'am confused that user has been dropped with cascade, so all objects
> > have been dropped in testdb, imported newly from proddb,
> > how sequence value is showing old nextval.
>
> > thanks
> > tcy
>
> Now let's think about this... you said that you "imported" from the
> proddb - correct?? Import will create the user and ALL of it's objects
> including the sequences with the PRODDB values - even if you only
> created an empty schema. Test it and see.
>
> Not sure the concept is that difficult as to cause such confusion.- Hide quoted text -
>
> - Show quoted text -

tcy, a couple of facts you can check: is the sequence in question owned by the schema that was dropped and recreated? If the sequence owner is different that would be one possibility. Another possibility if you were not the individual to drop the user is that instead of dropping and re-creating the user who ever prepared the account to be imported just dropped the user tables and indexes missing dropping the sequences.

HTH -- Mark D Powell -- Received on Sat Apr 02 2011 - 12:36:02 CDT