- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: How to export SEQUENCES through ORACLE 8.1.7
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2005 03:19 AM
тАО10-28-2005 03:19 AM
does it exist any options to export the sequences in ORACLE 8.x?
How can I do otherwise?
thanks
regards
Enrico
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2005 03:40 AM
тАО10-28-2005 03:40 AM
Re: How to export SEQUENCES through ORACLE 8.1.7
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2005 03:50 AM
тАО10-28-2005 03:50 AM
Re: How to export SEQUENCES through ORACLE 8.1.7
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2005 04:07 AM
тАО10-28-2005 04:07 AM
Re: How to export SEQUENCES through ORACLE 8.1.7
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2005 06:30 AM
тАО10-28-2005 06:30 AM
Re: How to export SEQUENCES through ORACLE 8.1.7
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-29-2005 11:01 AM
тАО10-29-2005 11:01 AM
SolutionThat'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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-30-2005 01:53 PM
тАО10-30-2005 01:53 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-30-2005 03:37 PM
тАО10-30-2005 03:37 PM
Re: How to export SEQUENCES through ORACLE 8.1.7
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-31-2005 10:04 PM
тАО10-31-2005 10:04 PM
Re: How to export SEQUENCES through ORACLE 8.1.7
to add on previous post, I think you would have to add the cache size to last number + 1.
Regards
Jean-Luc