Operating System - HP-UX
1748204 Members
4255 Online
108759 Solutions
New Discussion юеВ

Re: How to export SEQUENCES through ORACLE 8.1.7

 
SOLVED
Go to solution
Enrico Venturi
Super Advisor

How to export SEQUENCES through ORACLE 8.1.7

Hello colleagues,
does it exist any options to export the sequences in ORACLE 8.x?
How can I do otherwise?

thanks
regards
Enrico
8 REPLIES 8
Eric Antunes
Honored Contributor

Re: How to export SEQUENCES through ORACLE 8.1.7

Hi Venturi,

The Oracle 8.0 Utilities Documentation says:

"Exporting Sequences
If transactions continue to access sequence numbers during an export, sequence numbers can be skipped. The best way to ensure that sequence numbers are not skipped is to ensure that the sequences are not accessed during the export.

Sequence numbers can be skipped only when cached sequence numbers are in use. When a cache of sequence numbers has been allocated, they are available for use in the current database. The exported value is the next sequence number (after the cached values). Sequence numbers that are cached, but unused, are lost when the sequence is imported.

"

So, the sequences are exported along with the rows and I don't think you can export just sequences...

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Enrico Venturi
Super Advisor

Re: How to export SEQUENCES through ORACLE 8.1.7

I don't want to export just the sequences; I do export with the following options:
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
COMPRESS=Y
FULL=N
CONSISTENT=Y
DIRECT=Y
TABLES= a, b, d ....
I want to export the SEQUENCES too ...
what have I to do?
thanks again
Eric Antunes
Honored Contributor

Re: How to export SEQUENCES through ORACLE 8.1.7

Well, I think the sequences are ONLY exported with the owner (IF you select OWNER=<...>).

But you can create the sequences after importing the data:

CREATE SEQUENCE .
START WITH
MAXVALUE 2000000000
MINVALUE 1
NOCYCLE
CACHE
ORDER;

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
TwoProc
Honored Contributor

Re: How to export SEQUENCES through ORACLE 8.1.7

Well, I believe that if you're going to export the full system, or a whole schema at a time you're going to get your sequencers.

I've exported my share of databases over the years, and I've always had my sequences show up on the other end after the import. I've done both types of exports (full database and schema at a time), and have not had a problem with sequencers in either case.
We are the people our parents warned us about --Jimmy Buffett
Ariel Cary
Frequent Advisor
Solution

Re: How to export SEQUENCES through ORACLE 8.1.7

Hi Venturi,

That's pretty easy. You have to export at the schema level (without rows if you dont want them).

sql> sho user
acary@arc> sho user
USER is "ACARY"
acary@arc>
acary@arc>
acary@arc> create sequence s2
2 start with 100
3 nomaxvalue;

Sequence created.

Elapsed: 00:00:00.01
acary@arc> r
1 select s2.nextval
2* from dual

NEXTVAL
----------
100

Elapsed: 00:00:00.20
acary@arc> select s2.currval
2 from dual;

CURRVAL
----------
100

Elapsed: 00:00:00.01
acary@arc>

Then, you exp your objects.

$ exp acary file=ac.dmp log=ac.log owner=acary rows=n

Export: Release 9.2.0.6.0 - Production on Sat Oct 29 18:50:50 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
[...]
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ACARY
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ACARY
About to export ACARY's objects ...
. exporting database links
. exporting sequence numbers <<< HERE ARE YOUR SEQUENCES EXPORTED.
. exporting cluster definitions
[...]
. exporting statistics
Export terminated successfully without warnings.
oracle@atlas>


Now, you can verify you're sequence is in....

$ imp acary file=ac.dmp log=ac.log show=y full=y

Import: Release 9.2.0.6.0 - Production on Sat Oct 29 18:52:57 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing ACARY's objects into ACARY
[...]
"CREATE SEQUENCE "S2" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREM" <<< HERE IS YOUR SEQUENCE...
"ENT BY 1 START WITH 120 CACHE 20 NOORDER NOCYCLE"
Import terminated successfully without warnings.
$

Notice the 'CREATE SEQUENCE' statement has a 'START WITH' clause with argument 120. This is because the sequence has already been load into memory and cached 20 values since I NEXTVAL the sequence and did not specify the cache clause, which defaults to 20. If you do not wnat to have this gap in the imp DDL, then you would create/alter your sequence with NOCACHE clause.

Regards,

Ariel
Hein van den Heuvel
Honored Contributor

Re: How to export SEQUENCES through ORACLE 8.1.7


Nice reply Ariel. Thanks. All details provided.

It also shows that if those sequence needed to be added after the fact that it is not all to difficult to just re-create them with statements like:

CREATE SEQUENCE "S2" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 120 CACHE 20 NOORDER NOCYCLE"

By looking at curval in the sequences on both the target and source databases, you can verify any changes during the move.

fwiw,
Hein.
Yogeeraj_1
Honored Contributor

Re: How to export SEQUENCES through ORACLE 8.1.7

hi Enrico,

You may follow the great reply by Ariel Cary above or you may wish to generate a script that can create the same set of sequences.

select 'create sequence ' || sequence_name || ' start with ' || to_char(last_number+1)||';'
from user_sequences

(which you would run for each schema owner with sequences)

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: How to export SEQUENCES through ORACLE 8.1.7

Enrico

to add on previous post, I think you would have to add the cache size to last number + 1.

Regards
Jean-Luc
fiat lux