how to import sequence
Baqir Hussain, November 06, 2002 - 10:47 am UTC
We do not want to export whole schema or database except for few tables with all sequences. Is there any way to get it before or after the table level export is done?
November 06, 2002 - 11:54 am UTC
Nope. You'll just want to script the sequences
select 'create sequence ' || sequence_name || ' start with ' || last_number+1 || ';' from user_sequences where.....;
how to import sequences
A reader, November 06, 2002 - 12:51 pm UTC
Thanks for the prompt reply.
exp with tables do not import sequences. I just tried the following to import sequences and it worked:
1. exp user/passwordd tables=emp,foo indexes=n constraints=n triggers=n rows=n
2. then import with
imp sys/manager fromuser=boo touser=boo rows=n ignore=y
and sequences were imported.
I just bought your book "expert one on one" . I am going through chapter by chapter. It's an exellent. Waiting for your book on Tuning next spring 2003.
November 06, 2002 - 3:27 pm UTC
that'll get more then sequences but if you are happy with it -- cool.
Importing Sequences
mohammad taha, November 07, 2002 - 5:57 am UTC
IF "sequences are objects -- just like a table, procedure, view, package, etc. if you export A TABLE, that is all you get -- the TABLE." IS TRUE THEN
HOW
"1. exp user/passwordd tables=emp,foo indexes=n constraints=n triggers=n rows=n
2. then import with
imp sys/manager fromuser=boo touser=boo rows=n ignore=y
and sequences were imported.
Followup:
that'll get more then sequences but if you are happy with it -- cool." IS TRUE?????
ARE THEY NOT CONTRADICTORY???
November 07, 2002 - 7:48 am UTC
I didn't really read their command -- assumed what they said was true.
If you have the tables=..... on there, they are mistaken. They will NOT get sequences. Proof:
ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int );
Table created.
ops$tkyte@ORA920.US.ORACLE.COM> create sequence s;
Sequence created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select object_name, object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
S SEQUENCE
T TABLE
ops$tkyte@ORA920.US.ORACLE.COM> host exp userid=/ tables=t indexes=n constraints=n triggers=n rows=n
Export: Release 9.2.0.1.0 - Production on Thu Nov 7 07:54:11 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified tables via Conventional Path ...
. . exporting table T
Export terminated successfully without warnings.
ops$tkyte@ORA920.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA920.US.ORACLE.COM> drop sequence s;
Sequence dropped.
ops$tkyte@ORA920.US.ORACLE.COM> host imp userid=/ 'fromuser=ops$tkyte' 'touser=ops$tkyte' rows=n ignore=y
Import: Release 9.2.0.1.0 - Production on Thu Nov 7 07:54:12 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Import terminated successfully without warnings.
ops$tkyte@ORA920.US.ORACLE.COM> select object_name, object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
T TABLE
ops$tkyte@ORA920.US.ORACLE.COM>
<b>no sequence, just table..</b> I guess I just assumed they had "owner=boo" which WOULD get sequences (and other stuff)
ops$tkyte@ORA920.US.ORACLE.COM> select object_name, object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
S SEQUENCE
T TABLE
ops$tkyte@ORA920.US.ORACLE.COM> host exp userid=/ 'owner=ops$tkyte' indexes=n constraints=n triggers=n rows=n
Export: Release 9.2.0.1.0 - Production on Thu Nov 7 07:55:40 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$TKYTE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OPS$TKYTE
About to export OPS$TKYTE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OPS$TKYTE's tables via Conventional Path ...
. . exporting table T
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
ops$tkyte@ORA920.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA920.US.ORACLE.COM> drop sequence s;
Sequence dropped.
ops$tkyte@ORA920.US.ORACLE.COM> host imp userid=/ 'fromuser=ops$tkyte' 'touser=ops$tkyte' rows=n ignore=y
Import: Release 9.2.0.1.0 - Production on Thu Nov 7 07:55:46 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Import terminated successfully without warnings.
ops$tkyte@ORA920.US.ORACLE.COM> select object_name, object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
S SEQUENCE
T TABLE
Sagi, November 07, 2002 - 7:40 am UTC
Hi,
I think probably TOM must have oveseen tables= clause.
It does not export sequences at the first instance because of TABLES= parameter. I checked this for myself. Below is the output.
C:\>exp scott/tiger@rnd tables=emp,dept indexes=n rows=n constraints=n triggers=n
file=junk.dmp log=junk.exp
Export: Release 8.1.7.0.0 - Production on Thu Nov 7 12:35:32 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
Note: table data (rows) will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified tables via Conventional Path ...
<See here itself you can see Sequences not importing else it would give a message saying
. exporting sequence numbers>
. . exporting table EMP
. . exporting table DEPT
Export terminated successfully without warnings.
C:\>imp scott/tiger@rnd fromuser=scott touser=a file=junk.dmp log=junk.imp
show=y ignore=y
Import: Release 8.1.7.0.0 - Production on Thu Nov 7 12:38:13 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing SCOTT's objects into MODPLSQL
"ALTER SCHEMA = "MODPLSQL""
"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0) NOT NULL ENABLE, "ENAME" CHAR(10),"
" "JOB" CHAR(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "C"
"OMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1"
" MAXTRANS 255 LOGGING STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTE"
"NTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) T"
"ABLESPACE "USERS""
"CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR"
"CHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(I"
"NITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELIST"
"S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS""
Import terminated successfully without warnings.
But If I remove the TABLES= parameter it does export sequence.
Please correct me TOM if I am wrong. We all are waiting to see your reply.
Regards,
Sagi
November 07, 2002 - 7:49 am UTC
see above -- while you were typing this, I was typing that.
Sagi, November 07, 2002 - 8:04 am UTC
Hi Tom,
I hope with both answers of ours atleast people have the ideas a bit more clear that irrespective to Oracle Version, SEQUENCES are not exported in TABLE LEVEL.
Because Your example was in 9i and mine in 8i.
Thanx once again tom for all the good work.
Regards,
Sagi.
how to import sequence
Baqir Hussain, November 07, 2002 - 10:50 am UTC
it was just a type. The actually command I used after the fisrt export was
exp user/passwordd file=test row=n constraints=n indexes=n triggers=n consistent=y
and then import it with
imp system/manager fromuser=foo touser=foo file=test.dmp
and it imported sequences. What is going on behind the scene I don't know. But it worked.
The same last_number of the sequence was imported correctly.
November 07, 2002 - 12:33 pm UTC
sure -- but -- it'll get tons of other STUFF as well in general. (and would be a really slow way to get just the sequences from a schema if that is all you are after - me, i would use the supplied script I put above)
invalid number?
Doug C, February 19, 2003 - 11:57 pm UTC
out of curiosity - why does the line you provided consistently give me invalid number unless I put (last_number+1) in parenthesis ??
select 'create sequence '||sequence_name||' start with '|| last_number+1||';' from user_sequences
*
ERROR at line 1:
ORA-01722: invalid number
But this is ok -
1* select 'create sequence '||sequence_name||' start with '||(last_number+1)||
';' from user_sequences
SQL> /
February 20, 2003 - 7:27 am UTC
sorry about that -- it is due to the operator precedence and order of operation.
it is trying to add
'create sequence '||sequence_name||' start with '|| last_number
to
1||';'
and that fails. What we need to do is concatenate the (last_number+1) to the string instead. the parans get the order of operation right.
Import Views
Nitin, July 18, 2003 - 9:54 am UTC
We have a FULL db export. While performing the import (for restore), the process died because it was not run in background and the host connectivity was lost. We have created a parameter file with all the table names and imported all the remainder of the tables.
The only object that needs to be imported now are the views. What is the best way to import the views alone from the FULL export dump?
Can I use
IGNORE=Y ROWS=N INDEXES=N FROMUSER=ABC TOUSER=ABC
Regards
July 19, 2003 - 11:06 am UTC
that is a tricky one. imp isn't going to let you do just views. you can do "imp show=y" and grab the output -- the views will be there but they will need some amount of editing.
sorry -- there isn't an easy solution for this from the dmp file. I would be very tempted to go back to the source system and use these scripts:
------------ getaview.sql ----------------------
set heading off
set long 99999999
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
column column_name format a1000
column text format a1000
spool &1..sql
prompt create or replace view &1 (
select decode(column_id,1,'',',') || column_name column_name
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt ) as
select text
from user_views
where view_name = upper('&1')
/
prompt /
spool off
set termout on
set heading on
set feedback on
set verify on
-------------------------------------------------
---------------- getallviews.sql --------------------
set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
spool tmp.sql
select '@getaview ' || view_name
from user_views
/
spool off
set termout on
set heading on
set feedback on
set verify on
----------------------------------------
that'll extract all of the view text for you for installation in the other system.
Correction to - Import Views
Nitin, July 18, 2003 - 10:21 am UTC
I wanted to mention:
Can I use
IGNORE=N ROWS=N INDEXES=N FROMUSER=ABC TOUSER=ABC
Regards
July 19, 2003 - 11:14 am UTC
you can try with more =N's -- grants=n constraints=n
that should work with lots of error messages (warnings)
A reader, August 04, 2003 - 11:53 am UTC
"Nope. You'll just want to script the sequences
select 'create sequence ' || sequence_name || ' start with ' || last_number+1 ||
';' from user_sequences where.....;
"
Hi Tom
just to confirm what you mean is that you will re-create
the sequences with a different starting number (one
more than the last number of the original export)
by running the script generated by that select statements
you get from spooling the command, right?
This would mean that the sequence values assigned to the
"same" rows in the table would be different in the
imported schema, right? I am trying to think of any consequences here - Any comments?
Thank yo so much!!
sorry - dumb question!
A reader, August 04, 2003 - 11:57 am UTC
Obviously you are importing the table with
the old data (including the sequence column values) so
that is why you are recreating sequence with the
"last_number +1" values - for future values of the sequence.
Dumb question!
how to import sequence
eashwar, January 17, 2004 - 2:40 am UTC
am going to use one of the techniques mentioned to import a db of 3 gb size. splitting the objects seperately. however just as sequences, do we need to take the procedures/triggers also from user_source and user_triggers seperately.
January 17, 2004 - 4:56 pm UTC
if you do user level exports, you'll get the procedures and triggers....
Importing Triggers
Arun Mathur, February 23, 2004 - 1:20 pm UTC
Tom,
I did an import using the fromuser and touser options. For the most part, it worked well. However, several triggers were invalid because the table name each trigger referred to started with <old user>.table name, and <old user> is no longer present in the database. Do you have any suggestions on how I can resolve these types of errors?
Thanks once again.
Arun
February 23, 2004 - 4:47 pm UTC
you have to fix the code -- the code had hard coded references -- not too much we can do.
you'll need to read the trigger code out of the database, fix it and put it back in.
Please disregard my previous post
Arun Mathur, February 23, 2004 - 2:25 pm UTC
Thanks.
imp and tablespace creation
A reader, March 13, 2004 - 10:32 am UTC
Tom,
I have an export dump file of about 15 schemas taken with the option full=y from the db db_source. Each of these schemas were created in a different tablespace. On the db I want to do an import (db_destination) I have created all the schemas, but with the tablespace names not corresponding to db_source)
I do an import logging in as system, with the following options full = y and ignore = y. I get an error saying that the tablespace does not exist. When I do the import with the options fromuser, touser, I do not get the error
1) Before doing the import should I be creating the users with the same tablespace names as in the source db?
2) How can I know the options used when creating the export file?
Thank you Tom
March 13, 2004 - 11:14 am UTC
when you do the user level imports, import will attempt the CREATE statement and if it fails -- it will drop the "first" TABLESPACE statement in it and try again (hence the object gets created in the touser's default tablespace)
For objects that span multiple tablespaces (partitioned tables, tables with lobs, iot's with overflows and so on) -- this does not work (and you would have to pre-create the objects, empty tables to be imported into).
I would do user by user exports (and thus imports) or fromuser/touser if the entire thing was exported. The users should be pre-created with their default tablespaces set up and you should expect from time to time to have to pre-create the target objects in the target database (if the tablespaces do not exist and you have these multi-tablespace objects). You can use "imp .... INDEXFILE=foo.sql" to get the template DDL you need to pre-create these objects easily.
the options used by export are not recorded, you would have to know what options were used via the old fashioned "ask the person that did it" method....
import-tablespace
A reader, March 13, 2004 - 12:00 pm UTC
Tom,
When doing the user by user import, in what order will the users be imported? How Will fk's referencing other schemas be imported
Ex.
fromuser = user1, user2,...
touser = user1, user2
table1 in user1 references table2 in user2.
Q1) Will the FK be created in this case?
Q2) Will public synonyms be imported when doing the import with fromuser .. touser ...option?
Thank you
March 13, 2004 - 12:05 pm UTC
tables and then fkeys.
if you have fkeys that cross schemas, it'll be up to you to import the objects in the correct order when using fromuser/touser (or just do constraints=no, imp, then just import the constraints later)
need to export procedures from one schema to other new schema
vinodh, May 12, 2004 - 5:50 am UTC
Hi Tom,
Iam interested to know whether is there any way to export only procedure,functions , package from one large schema and to import that procedure,function,package to a new schema.
Thanks in advance,
vinodh
May 12, 2004 - 7:48 am UTC
no, you can easily read them out and create scripts from them.
I call this getcode.sql:
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
and this getallcode.sql:
set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
spool getallcode_INSTALL
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
it'll "export" all of them.
thanks, is there any possibility that we can take it from dmp
Vinodh, May 13, 2004 - 5:23 am UTC
Hi tom,
Thanks for for u reply , well yes i did the same.
so we can't export only procedure, package, function and import the same to other schema is it.
Thanks,
vinodh
last_number of sequence is smaller than pk of one table after import
Sean, October 31, 2005 - 6:09 pm UTC
Hi Tom,
We have a scritp to do exp of schema.
exp \'sys@cbprod as sysdba\' owner=scott compress=n consistent=y statistics=none direct=y rows=y indexes=y grants=y file=scott.DMP
After whole schema import, sometimes user complained the max pk of one table is large than the last_number of the sequence it uses. I thought that Oracle should export seqnece after table export. But it seems that it export sequence first by lookin at the log file.
Thanks so much for your help
Sean
November 01, 2005 - 10:47 am UTC
It issues a set transaction readonly statement HOWEVER...
sys is not subject to read only! You cannot do a consistent export as sys, sys is special that way.
if you use a regular account that can do read only, it would all be exported as of a given point in time.
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1005615 <code>
What about a full database export with consistent=y?
Tom Fox, November 01, 2005 - 12:31 pm UTC
We currently have OEM perform exports of our databases on a schedule in order to protect our data further.
From the link you posted above, using export with consistent=y and being connected as sys, sysdba, or sys/sysdba is not supported.
I guess you would then use SYSTEM to export an entire database with consistency. Is this the correct method?
To be honest, I've never had an issue with using sys/sysdba and consistent=y, but then again, I may have just been lucky.
November 02, 2005 - 4:47 am UTC
or a DBA of your choice.
You have just been 'lucky', the data was not point in time consistent.
I would rely on precisely one thing however to protect my data - proper backups.
Thanks for the information
Tom Fox, November 02, 2005 - 8:40 am UTC
I guess I was just a little confused how a non-SYSDBA user could export the entire database. I guess if they have the EXP_FULL_DATABASE role, then they would be able to do it.
Thanks for the pointer. I never seem to stop learning, nor do I expect the learning to stop.
What do have to say about this Importing Sequence
Girish, August 01, 2006 - 6:07 am UTC
Hi Tom,
I have a database and T1 is the one among other tables which has sequence S1 inserting into a column of T1.It has inserted 1..10 values into T1
When I export the database/schema I will be exporting sequence too.When I import this database/schema.And start
inserting will my sequence value begin at 11 or do I have to do a CURRVAL and then NEXTVAL to start inserting with 11
Please let me know
Regds
Girish
August 01, 2006 - 9:10 am UTC
it does the right thing.
ops$tkyte%ORA10GR2> create sequence s;
Sequence created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 for i in 1 .. 100
3 loop
4 for x in ( select s.nextval xx from dual )
5 loop
6 null;
7 end loop;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> !exp userid=/ 'owner=ops$tkyte'
Export: Release 10.2.0.1.0 - Production on Tue Aug 1 09:05:05 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
...
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
ops$tkyte%ORA10GR2> drop sequence s;
Sequence dropped.
ops$tkyte%ORA10GR2> !imp userid=/ full=y
Import: Release 10.2.0.1.0 - Production on Tue Aug 1 09:05:19 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
Import terminated successfully without warnings.
ops$tkyte%ORA10GR2> select s.nextval from dual;
NEXTVAL
----------
101
Michel Cadot, August 01, 2006 - 9:33 am UTC
Hi Tom,
There is a bias in your demonstration, it only worked because 100 is a multiple of 20, the default cache size.
You also get 101 and not 91 if your loop go from 1 to 90:
TEST>create sequence s;
Sequence created.
TEST>begin
2 for i in 1..90 loop
3 for x in (select s.nextval from dual) loop null; end loop;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
TEST>host exp userid=system/*** owner=TEST file=c:\temp\t.dmp
TEST>drop sequence s;
Sequence dropped.
TEST>host imp userid=system/*** full=y file=c:\temp\t.dmp
TEST>select s.nextval from dual;
NEXTVAL
----------
101
1 row selected.
TEST>select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
S 1 1.0000E+27 1 N N 20 121
1 row selected.
It only works if you inhibate the cache for the sequence before the export:
TEST>alter sequence s nocache;
Sequence altered.
TEST>host exp userid=system/*** owner=TEST file=c:\temp\t.dmp
TEST>drop sequence s;
Sequence dropped.
TEST>host imp userid=system/*** full=y file=c:\temp\t.dmp
TEST>select s.nextval from dual;
NEXTVAL
----------
102
1 row selected.
But of course you have to restore the sequence cache after the export and import...
Michel
August 01, 2006 - 10:28 am UTC
it puts the sequence at a value HIGHER. Thatwas my point. It does not start at one, it does the right thing.
I would never even dream about preserving a couple of sequence values. even if you waste 1,000,000 of them a second, it'll take trillions of years to exhaust it.
Tom's Attitude
Ihopethishelps, October 19, 2006 - 12:39 am UTC
To say that sequences are "in no way shape or form related to tables" is beyond the pale. IN MOST CASES SEQUENCES ARE USED TO DEFINE THE UNIQUE RECORD IN THE TABLE.
Oracle software is hard enough to learn and get good help on so cut the bullshit and try showing a little support even for the most ignorant. There is no shame in ignorance, but the same cannot be said for your behavior.
Maybe you should change your site to "AskTomToInsultYou.Oracle.com."
Consider this a slap. You deserve it.
October 19, 2006 - 8:14 am UTC
but they are in NO WAY SHAPE OR FORM RELATED TO THE TABLES none the less.
so you cut the $#@!%#@
just because the database you used prior to Oracle did it differently, so what, get used to it. different products work differently.
sequences are in NO WAY SHAPE OR FORM RELATED TO TABLES, period. End of discussion.
when you create a sequence, tell me, do you mention a table?
when you create a table, tell me, do you menation a sequence?
If reality is too hard for... well, I don't know.
Sometimes, just sometimes, things work the way they work - and not the way you would have made them work if you wrote it yourself.
You'll be a much happier person all around when you understand that.
To the last reviewer
Alexander the ok, October 19, 2006 - 1:41 pm UTC
I think that person needs to relax.
It's Tom's way, it's his site. If you don't like his approach, don't come here.
I don't like lobster so I don't eat it....
I don't like "reality" tv so I don't watch it..
There are plenty of forums out there.
Personally, I like Tom's attitude. It inspires confidence in what he says when it is lacking a sugar coating. Technically what he said is correct. Since this is uh well, a technical forum, seems appropriate
To us humans, yeah sequences and tables seem related, but not to Oracle.
So keep up the good work Tom.
Great sample code to get all stored procedures and function!
Rachel, November 01, 2006 - 5:27 pm UTC
Tom You are always Spot on
abhi, December 19, 2006 - 7:39 am UTC
i am late in sending response, but i am totally agree with Alexander. And most of the time, tom's advice helps me a lot, i learnt a lot from tom. Tom as always Thanks a ton for your advice, guidence and all what you put in asktom
Can we just modify the LAST_NUMBER of a sequence same as PROD during refresh?
Vivek Raj Battu, May 14, 2012 - 5:14 pm UTC
Hello Tom,
As we are trying to export the tables and sequences from PROD to TEST environment,
Instead of re-creating all the sequences, can we just change the LAST_NUMBER according to the value of PROD?
Thanks,
Vivek
Response to last reviewer
Raj Kathamuthu, May 23, 2012 - 1:23 am UTC
<p>
Instead of re-creating all the sequences, can we just change the LAST_NUMBER according to the value
of PROD?
</p>
As part of export process, I usually capture LAST_NUMBER for all sequences that are part of schema being exported
using this script generator.
select 'execute set_seq_to('''||sequence_name||''','||LAST_NUMBER||');' from seq;
Run the resultant script on TEST to reset sequneces as post import process.
Regards,
Raj K
How Deep Does a Dependency Go?
John G, August 10, 2020 - 9:28 pm UTC
I love your column/web site. It was the first place I went in Oracle's Magazine when it was still in print. I did not like the troll who complained about your attitude. I am more like Alexander in my attitude. I fully understood your explanation about sequences not mentioning a table name and visa versa. I am not very well versed on dependencies in general and the only ones I see in my tables are triggers. I do understand that if they become invalid, I am in trouble and if a sequence referenced by my trigger goes missing the trigger becomes invalid. Now my tables do not explicitly reference the trigger, however they certainly seem to depend on them. The question is, when one object can depend on another how long can a dependency chain get and still appear on the Dependencies tab of SQL Developer?
August 11, 2020 - 4:12 am UTC
The basic rule here is - what goes in DBA_DEPENDENCIES
It has parent-child pairings, so if you the chain can be followed in that view, then that will be a firm indicator of the dependency tree.
I say "firm" because in recent versions, we're better at limiting invalidations. eg, you can add a new routine to the bottom of a package spec and we don't invalidate body. Simiarly, we can add columns to a table and not invalidate a pre-existing view etc.
How Deep Does a Dependency Go?
John G, August 10, 2020 - 9:41 pm UTC
Sorry, that was supposed to be, "do not explicitly reference the sequence".
How to use expdp/impdp to bring in sequence,procedure,function from schema to another schema
Paul, August 21, 2020 - 4:49 am UTC
from the above, it shows demonstration with exp/imp, how can we use expdp/impdp to bring in sequence,procedure,function from schema to another schema.
Thanks
August 21, 2020 - 7:54 am UTC