Thanks for the question, Geraldo.

Asked: November 09, 2018 - 12:53 pm UTC

Last updated: November 12, 2018 - 9:46 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Hello, Ask TOM Team.

We are designing a new database and there are lots of tables with identity column. The sequence used by each table is a named sequence (we are not using the system-generated sequence). The default values of these tables is like "SEQUENCE_NAME.NEXTVAL" All these table are joined by these identity columns across the whole database. Based on this information:

1. What would happen if I make an EXPDP only of the tables without the sequences and import them to another database? Will the sequence continue in order?

2. What would happen if we decide to move these tables (because they are too huge) to historical tables? Will there be issues?

Thanks in advanced.

Regards,

and Chris said...

1. Are you using identity columns or default sequences?

If it's an identity column, then when you (data pump) import it, the database will create a new identity sequence for you. This will continue where the export left off.

First create the table:

create table t (
  c1 int generated always as identity
);

begin 
  insert into t values ( default );
  insert into t values ( default );
  insert into t values ( default );
  insert into t values ( default );
end;
/
commit;

select max ( c1 ) from t;

MAX(C1)   
        4

Then expdp/impdp (remapping to a new table because it's going back in the same schema):

bash-4.2$ expdp userid=chris/chris tables=t dumpfile=chris_t.dmp

Export: Release 12.2.0.1.0 - Production on Fri Nov 9 07:21:23 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "CHRIS"."SYS_EXPORT_TABLE_01":  userid=chris/******** tables=t dumpfile=chris_t.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
. . exported "CHRIS"."T"                                 5.093 KB       4 rows
Master table "CHRIS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHRIS.SYS_EXPORT_TABLE_01 is:
  /ade/b/2757588126/oracle/rdbms/log/chris_t.dmp
Job "CHRIS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Nov 9 07:21:51 2018 elapsed 0 00:00:26

bash-4.2$
bash-4.2$
bash-4.2$ impdp userid=chris/chris dumpfile=chris_t.dmp remap_tables=t:t_new
LRM-00101: unknown parameter name 'remap_tables'

bash-4.2$ impdp userid=chris/chris dumpfile=chris_t.dmp remap_table=t:t_new

Import: Release 12.2.0.1.0 - Production on Fri Nov 9 07:22:28 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "CHRIS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CHRIS"."SYS_IMPORT_FULL_01":  userid=chris/******** dumpfile=chris_t.dmp remap_table=t:t_new
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CHRIS"."T_NEW"                             5.093 KB       4 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "CHRIS"."SYS_IMPORT_FULL_01" successfully completed at Fri Nov 9 07:22:52 2018 elapsed 0 00:00:22

Then check out the new table:

select max ( c1 ) from t_new;

MAX(C1)   
        4 

insert into t_new values ( default );

select max ( c1 ) from t_new;

MAX(C1)   
        5 

select table_name, column_name, data_default
from   user_tab_cols
where  table_name in ( 'T', 'T_NEW' );

TABLE_NAME   COLUMN_NAME   DATA_DEFAULT                      
T            C1            "CHRIS"."ISEQ$$_145657".nextval   
T_NEW        C1            "CHRIS"."ISEQ$$_145746".nextval

But if you use a default sequence, this isn't carried over:

drop table t cascade constraints purge;
drop table t_new cascade constraints purge;
create sequence s;
create table t (
  c1 int default s.nextval
);

begin 
  insert into t values ( default );
  insert into t values ( default );
  insert into t values ( default );
  insert into t values ( default );
end;
/
commit;

select max ( c1 ) from t;

MAX(C1)   
        4

Then run the export:

bash-4.2$ expdp userid=chris/chris tables=t dumpfile=chris_t.dmp

Export: Release 12.2.0.1.0 - Production on Fri Nov 9 07:39:39 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "CHRIS"."SYS_EXPORT_TABLE_01":  userid=chris/******** tables=t dumpfile=chris_t.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CHRIS"."T"                                 5.085 KB       4 rows
Master table "CHRIS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHRIS.SYS_EXPORT_TABLE_01 is:
  /ade/b/2757588126/oracle/rdbms/log/chris_t.dmp
Job "CHRIS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Nov 9 07:40:04 2018 elapsed 0 00:00:24

Now drop the sequence (because I'm importing back to the same schema; need to clean it) and:

bash-4.2$ impdp userid=chris/chris dumpfile=chris_t.dmp remap_table=t:t_new

Import: Release 12.2.0.1.0 - Production on Fri Nov 9 07:40:25 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "CHRIS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CHRIS"."SYS_IMPORT_FULL_01":  userid=chris/******** dumpfile=chris_t.dmp remap_table=t:t_new
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"CHRIS"."T_NEW" failed to create with error:
ORA-02289: sequence does not exist

Failing sql is:
CREATE TABLE "CHRIS"."T_NEW" ("C1" NUMBER(*,0) DEFAULT "CHRIS"."S"."NEXTVAL") SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "CHRIS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Nov 9 07:40:30 2018 elapsed 0 00:00:04

Doesn't look liked that worked.

Let's check:

select max ( c1 ) from t_new;

ORA-00942: table or view does not exist

:(

So if you're using normal sequences, you need to include it in the export or create it manually on the new database.

2. Yes.

What issues?

¯\_(ツ)_/¯

It depends on how exactly you're doing this!

Rating

(3 ratings)

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

Comments

Reeview

Geraldo Peralta, November 09, 2018 - 4:01 pm UTC

Hahahaha. Ok. There is no problem. I tought Oracle would create a sequence automatically. But this is Ok. I was expecting this. :)

1. With issues I meant that the sequences could lose its values.

2. If I'm using normal sequences and import tables with its sequences to a new schema/database, would the sequence keep its value? Because if I create new sequences for those tables the secunetial values are going to be lost for the those rows.

November 09, 2018 - 4:24 pm UTC

The sequence values will be the same on import. You can create new sequences beginning at any value though. Just set the start with property:

create sequence s start with 42;

select s.nextval from dual;

NEXTVAL   
       42

Looking for the same suggestion

Venkat, November 09, 2018 - 6:45 pm UTC

Hi Chris,

"If it's an identity column, then when you (data pump) import it, the database will create a new identity sequence for you. This will continue where the export left off. "

This is exactly what I am looking for. I am creating a new table in an Application environment where I am tired of going after triggers used to generate the primary key column. I don't prefer this personally though. So I understand using identity column doesn't have any drawback even we have to export and import to new database.

Then my current Oracle version is 12.1, I see a known issue as the sequence has to be explicitly dropped along with the table. I did this exercise and noticed oracle creates an internal sequence which can't be dropped unless as a sysdba as system user, am I correct?

November 12, 2018 - 9:46 am UTC

Yes, you can't drop the sequence for an identity column. Dropping the table removes it:

create table t (
  c1 int generated as identity
);

select column_name, data_default 
from   user_tab_cols
where  table_name = 'T';

COLUMN_NAME   DATA_DEFAULT                      
C1            "CHRIS"."ISEQ$$_146981".nextval 

select last_number from user_sequences
where  sequence_name = 'ISEQ$$_146981';

LAST_NUMBER   
            1 

drop sequence iseq$$_146981;

ORA-32794: cannot drop a system-generated sequence

drop table t cascade constraints purge;

select last_number from user_sequences
where  sequence_name = 'ISEQ$$_146981';

no rows selected

Review

Geraldo Peralta, November 11, 2018 - 11:28 pm UTC

Thanks for your accurate answers.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Reeview

Looking for the same suggestion

Review

More to Explore

Data Pump

Oracle Sequence and EXPDP/Historical Data

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Reeview

Looking for the same suggestion

Review

More to Explore

Data Pump