Increased sequences after export schema/user

[thomasschulz2]

Hi,

I noticed, that after exporting the catalog data of an user or a schema, all corresponding sequences are increased by one. Normally I would expect, that an export doesn't change any data. Maybe this is a bug?

Are there others possibilities to export the sequences without changing them?

Tested versions: 7.6.03.15 and 7.6.05.09 on linux (64 bit)

Regards,

Thomas

[lbreddemann · Accepted solution]

Hi Thomas,

> I noticed, that after exporting the catalog data of an user or a schema, all corresponding sequences are increased by one. Normally I would expect, that an export doesn't change any data. Maybe this is a bug?

How did you check this?

Via

select <sequence>.currval from dual?

or via

select sequence_name, last_number from sequences where sequence_name ='<name of your sequence>' ?

Anyhow, when using sequences you should never expect the numbers to be without gaps.

Usually you want to use sequences to have a locking-free mechanism of getting numbers, e.g. for IDs.

They don't rollback if the session that got a number from it rolls back and if the sequence is cached (what should be done for highly accessed sequences), than the cached numbers will be gone with the next restart.

So with sequences you will get gaps.

If you cannot allow this is your application than you'll have to implement a number range table like the infamous SAP NRIV where you actually store your current highest number.

Concerning the export: I was not able to reproduce it.

Can you describe in more detail how you did the export?

> Are there others possibilities to export the sequences without changing them?

Although this is rather meaningless: you may use the "Export DDL" function in DB Studio that is available via right mouse button, when you display the sequence definition.

Anything else would require some custom SQL statements - but not today anmyore...:-)

regards,

Lars

[thomasschulz2 · Accepted solution]

Hi Wolfgang and Lars.

> Hi Thomas,

>

> > I noticed, that after exporting the catalog data of an user or a schema, all corresponding sequences are increased by one. Normally I would expect, that an export doesn't change any data. Maybe this is a bug?

>

> How did you check this?

>

> Via

>

> select <sequence>.currval from dual?
> 

> or via

>

>

> select sequence_name, last_number from sequences where sequence_name ='<name of your sequence>' ?
> 

>

Via

select sequence_name, last_number from sequences

I've tried your first variant with

select <sequence>.currval from dual

now and get unchanged values after export. But with

select <sequence>.nextval from  dual

I get the same increased values as with a select from the sequences table.

>

> Anyhow, when using sequences you should never expect the numbers to be without gaps.

> Usually you want to use sequences to have a locking-free mechanism of getting numbers, e.g. for IDs.

> They don't rollback if the session that got a number from it rolls back and if the sequence is cached (what should be done for highly accessed sequences), than the cached numbers will be gone with the next restart.

>

> So with sequences you will get gaps.

OK - this is no problem for us.

> Concerning the export: I was not able to reproduce it.

> Can you describe in more detail how you did the export?

>

Here are the DDLs:

CREATE TABLE "TESTTAB" ("FIELD1" Integer)
CREATE SEQUENCE TESTSEQ

Export:

export schema testuser catalog outstream 'testuser.cat'

or

export user catalog outstream 'testuser.cat'

> > Are there others possibilities to export the sequences without changing them?

>

> Although this is rather meaningless: you may use the "Export DDL" function in DB Studio that is available via right mouse button, when you display the sequence definition.

>

> Anything else would require some custom SQL statements - but not today anmyore...:-)

>

> regards,

> Lars

No problem.

Regards,

Thomas

Edited by: Thomas Schulz on Jan 26, 2009 7:00 PM

[thomasschulz2 · Accepted solution]

Hi Lars,

>

> Although this is rather meaningless: you may use the "Export DDL" function in DB Studio that is available via right mouse button, when you display the sequence definition.

my version of DB Studio (7.7.04.26 on Linux 64bit with 32bit Java) offers only "Export SQL", which saves the DDL without the start value ("... start with <value not accessible> ...").

Regards,

Thomas

[lbreddemann · Accepted solution]

Hi Thomas,

> my version of DB Studio (7.7.04.26 on Linux 64bit with 32bit Java) offers only "Export SQL", which saves the DDL without the start value ("... start with <value not accessible> ...").

you're right. You've to fill in the start number there yourself - not very automatic...

Anyhow, you may use this statement instead:

select 'CREATE SEQUENCE "' || schemaname || '"."' || sequence_name ||'" increment by ' ||
        increment_by || ' start with ' || last_number || ' minvalue ' || min_value ||
        ' maxvalue ' || max_value ||' '|| decode(cycle_flag, 'N', 'nocylce', 'Y', 'cycle') ||
        ' cache ' || cache_size
from sequences

regards,

Lars

[thomasschulz2 · Accepted solution]

Hi Lars,

this works for our export (small correction: nocycle instead nocylce).

Thanks a lot for your help.

Nevertheless we are wondering, why an export increase the sequence values.

Regards,

Thomas

[lbreddemann · Accepted solution]

> this works for our export (small correction: nocycle instead nocylce).

> Thanks a lot for your help.

Arghs --- always those typos...

> Nevertheless we are wondering, why an export increase the sequence values.

Sure - since Wolfgang Auer is already reading this thread, I'm sure you'll get a good answer for that sooner or later.

I guess this has something to do with the way that sequences are stored internally - but only the developer can tell that for sure.

regards,

Lars