Oracle序列和EXPDP/历史数据

你好，问一下汤姆团队。

我们正在设计一个新的数据库，并且有很多带有标识列的表。每个表使用的序列是一个命名序列 (我们没有使用系统生成的序列)。这些表的默认值类似于 “SEQUENCE_NAME.NEXTVAL”，所有这些表都由这些标识列在整个数据库中连接。根据这些信息:

1.如果我只对没有序列的表进行EXPDP并将其导入到另一个数据库，会发生什么？序列会按顺序继续吗？

2.如果我们决定将这些表 (因为它们太大) 移动到历史表中会发生什么？会有问题吗？

提前感谢。

问候，

1.您是否使用标识列或默认序列？

如果它是一个标识列，那么当您 (数据泵) 导入它时，数据库将为您创建一个新的标识序列。这将在出口中断的地方继续。

首先创建表:

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

然后expdp/impdp (重新映射到一个新表，因为它返回相同的模式):

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

然后查看新表:

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   

但是，如果您使用默认序列，则不会继续:

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 

然后运行导出:

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

现在删除序列 (因为我导入回相同的模式; 需要清理它) 和:

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

看起来不起作用。

让我们检查一下:

select max ( c1 ) from t_new;

ORA-00942: table or view does not exist

:(

因此，如果您使用的是正常序列，则需要将其包含在导出中或在新数据库上手动创建它。

2.是的。

什么问题？

'\ _(ツ)_/''

这取决于你到底是怎么做的!