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,
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!