Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Baqir.

Asked: November 05, 2002 - 4:57 pm UTC

Last updated: August 21, 2020 - 7:54 am UTC

Version: 9.2.0.1

Viewed 50K+ times! This question is

You Asked

export is done on table level using 9.2.0.1
exp user/password tables=emp,foo file=test.dmp
during import sequences never got imported. This is the default behavior of oracle. I would appreciate if you please advise on the followings:
1. How to import sequences in table level export?
2. How to get the same sequence value at the time of export?

Thanks


and Tom said...

1) why would sequences -- which are not in any way shape or form related to tables -- by exported in a table level export?

sequences are objects -- just like a table, procedure, view, package, etc. if you export A TABLE, that is all you get -- the TABLE.

2) you would export a database or schema, that will get the sequences.

Rating

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?

Tom Kyte
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.

Tom Kyte
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???







Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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> / 

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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



Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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


Tom Kyte
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.

Tom Kyte
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
Tom Kyte
May 14, 2012 - 11:52 pm UTC

you could use this technique:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597


to alter the sequence and set the increment by up, select from it, and then reset the increment by

but frankly, it would be easier to drop/create

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?
Connor McDonald
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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library