加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

EXPDP/IMPDP中触发器对SEQUENCE的影响

(2013-07-10 17:17:13)
标签：

expdp

impdp

触发器

trigger

sequence

 分类： Oracle
测试环境：oracle 10.2.0.4  win 7 64bit
          oracle 11.2.0.3  RHEL4.2 32bit
结论：1、从schema1中导入到schema2中，sequence的值会发生变化，但是变化是在导出过程中产生的，在导入          时会把变化值导入到schema2中。完成后两个schema的值还是一致的。
      2、从schema1中导出，然后导入schema1中（table_exists_action=truncate)，oracle10g在导入（注意          只是impdp时不变，expdp会发生变化的）前后不会发生变化，而oracle11g环境中是会发生变化的。
      3、触发器禁用与否对sequence没有影响。
      4、sequence值具体什么原因产生变化的，本人没做细致研究，望知晓的同学给予指导，谢谢！
         补充：值变化与cache有关，这里设置cache为10，每次expdp/impdp的时候cache中的值会被舍弃，重                新导入一组cache。关闭cache可以解决该问题。

########################################################
Oracle 10g中的测试：
########################################################
1、创建sequence

CREATE  SEQUENCE sequence1 
INCREMENT  BY   1  
 START  WITH   1 　
 NOMAXVALUE　　　   
 NOCYCLE　　　　 
CACHE  10;  

2、创建测试表

create table test(id number,wid number);
create table test1(id number);

3、创建触发器

Create or replace trigger trigger_test
before insert or update or delete
On test
Begin
    Insert into test1 values(1);
End;

4、开始测试，往test表中多次插入数据：
SQL> insert into test(id,wid)values(1,sequence1.nextval);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test;
 
        ID        WID
---------- ----------
         1          5
         2          6
         1         11
         1         23
         1         25
 
SQL> select * from test1;
 
        ID
----------
         1
        22
         1
        24
这个时候可以看出sequence的值为25.下一个值为26.

5、导出schema数据
C:\Users\ZhangNai>expdp system/password schemas=SEQUENCE1 logfile=expdp.log directory=DP_DIR dumpfile=expdp1.dmp
C:\Users\ZhangNai>impdp system/password remap_schema=SEQUENCE1:SEQUENCE2 logfile=impdp.log directory=DP_DIR dumpfile=expdp1.dmp
导入另一个schema，完成之后，进行查询：
SQL> select * from test;
 
        ID        WID
---------- ----------
         1          5
         2          6
         1         11
         1         23
         1         25
 
SQL> select * from test1;
 
        ID
----------
         1
        22
         1
        24
 
SQL> select SEQUENCE1.nextval from dual;
 
   NEXTVAL
----------
        31

查询源schema的nextval结果：
SQL> select SEQUENCE1.nextval from dual;
 
   NEXTVAL
----------
        31
        
6、从查询结果看，在数据完成EXPDP/IMPDP之后，源库和目标库的sequence是保持不表的，但是在EXPDP/IMPDP中
sequence值会发生变化，这个变化在源库和目标库保持一致。为了验证含有sequence语句的触发器对sequence是否有影响，将原有
触发器trigger_test删除，重新创建触发器：
create table test1(id number);
Create or replace trigger trigger_test1
before insert or update or delete
On test
Begin
    Insert into test1 values(sequence1.nextval);
End;

然后重复上述的验证，结果和前面验证的结果一样：在数据完成EXPDP/IMPDP之后，源库和目标库的sequence是保持不表的，但是在EXPDP/IMPDP中
sequence值会发生变化，这个变化在源库和目标库保持一致。


7、在源schema：sequence1中进行多次数据插入：
SQL> insert into test(id,wid)values(1,sequence1.nextval);
 
1 row inserted
 
SQL> /
 
1 row inserted
 
SQL> /
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> select * from test;
 
        ID        WID
---------- ----------
         1          5
         2          6
         1         11
         1         23
         1         25
         1         33
         1         35
         1         37
         1         39
 
9 rows selected
 
SQL> select * from test1;
 
        ID
----------
         1
        22
         1
        24
        32
        34
        36
        38
 
8 rows selected

查看sequence值：当前值为39，next值为40
SQL> select SEQUENCE1.currval from dual;
 
   CURRVAL
----------
        39
        
8、将之前导出的dmp文件导入：
C:\Users\ZhangNai>impdp system/password remap_schema=SEQUENCE1:SEQUENCE1 logfile=impdp.log directory=DP_DIR dumpfile=expdp1.dmp table_exists_action=truncate
Import: Release 10.2.0.4.0 - 64bit Production on Wednesday, 10 July, 2013 15:51:15

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=SEQUENCE1:SEQUENCE1 logfile=impdp.log directory=DP_DIR dumpfile=expdp1.dmp table_exists_action=truncate 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SEQUENCE1" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"SEQUENCE1"."SEQUENCE1" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39153: Table "SEQUENCE1"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
ORA-39153: Table "SEQUENCE1"."TEST1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEQUENCE1"."TEST"                          5.257 KB       5 rows
. . imported "SEQUENCE1"."TEST1"                         4.937 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 15:51:23

导入完成后查询结果：

SQL> select * from test;
 
        ID        WID
---------- ----------
         1          5
         2          6
         1         11
         1         23
         1         25
 
SQL> select * from test1;
 
        ID
----------
        41
         1
        22
         1
        24
        
SQL> select SEQUENCE1.currval from dual;
 
   CURRVAL
----------
        39

Sequence值在导入前后没发生变化。



##########################################################
Oracle 11.2.0.3中的测试
#########################################################

在oracle 11g版本中，从schema1导入到schema2中与Oracle 10g版本一致，并且sequence值在导出导入过程中会发生变化，但是最终值是一致的。

在从schema1中导出，再导入schema1中时，Oracle 10g不一样。在Oracle 11g中 sequence的值在导入前后是发生变化的。测试如下：

1、准备步骤（略）
2、多次插入数据
SQL> insert into test(id,wid)values(1,sequence1.nextval);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID  WID
---------- ----------
1    1
1    2
1    3
1    4
1   13
1   15
1   17

7 rows selected.

SQL> select * from test1;

ID
----------
1
1
1
1
12
14
16

7 rows selected.

SQL> select sequence1.currval from dual;

   CURRVAL
----------
17

检查当前sequence值为17，next为18


3、expdp导出数据：
[oracle@localhost ~]$ expdp system/password schemas=ICE2 logfile=expdp.log directory=DP_DIR dumpfile=expdp1.dmp

Export: Release 11.2.0.3.0 - Production on Wed Jul 10 16:26:05 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=ICE2 logfile=expdp.log directory=DP_DIR dumpfile=expdp1.dmp 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "ICE2"."TEST"                               5.460 KB       7 rows
. . exported "ICE2"."TEST1"                              5.046 KB       7 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dp/expdp1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:26:53

导出之后查询：
SQL> select * from test;

ID  WID
---------- ----------
1    1
1    2
1    3
1    4
1   13
1   15
1   17

7 rows selected.

SQL> select * from test1;

ID
----------
1
1
1
1
12
14
16

7 rows selected.

SQL> select sequence1.currval from dual;
select sequence1.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQUENCE1.CURRVAL is not yet defined in this session

SQL> select sequence1.nextval from dual;

   NEXTVAL
----------
21
查询next值为21，即current为20。

4、继续批量插入数据
SQL> insert into test(id,wid)values(1,sequence1.nextval);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID  WID
---------- ----------
1    1
1    2
1    3
1    4
1   13
1   15
1   17
1   23
1   25
1   27

10 rows selected.

SQL> select * from test1;

ID
----------
1
1
1
1
12
14
16
22
24
26

10 rows selected.

SQL> select sequence1.currval from dual;

   CURRVAL
----------
27
查询当前current值为27.

5、impdp导入数据（table_exists_action=truncate)
[oracle@localhost ~]$ impdp system/password remap_schema=ICE2:ICE2 logfile=impdp.log directory=DP_DIR dumpfile=expdp1.dmp table_exists_action=truncate

Import: Release 11.2.0.3.0 - Production on Wed Jul 10 16:30:54 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=ICE2:ICE2 logfile=impdp.log directory=DP_DIR dumpfile=expdp1.dmp table_exists_action=truncate 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ICE2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"ICE2"."SEQUENCE1" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "ICE2"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "ICE2"."TEST1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ICE2"."TEST"                               5.460 KB       7 rows
. . imported "ICE2"."TEST1"                              5.046 KB       7 rows
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 16:31:06

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 10 16:31:33 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn ice2/ice2;
Connected.
SQL> select * from test;

ID  WID
---------- ----------
1    1
1    2
1    3
1    4
1   13
1   15
1   17

7 rows selected.

SQL> select * from test1;

ID
----------
31
1
1
1
1
12
14
16

8 rows selected.

SQL> select sequence1.currval from dual;
select sequence1.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence SEQUENCE1.CURRVAL is not yet defined in this session


SQL> select sequence1.nextval from dual;

   NEXTVAL
----------
41

导入之后current变成40，next值为41.导入之前是current值为27.
这与oracle 10g不一致（10g在导入前后，current值保持一致）。
即便禁用触发器sequence值也是会变的：
SQL> conn ice2/ice2
Connected.
SQL> 
SQL> 
SQL> select * from test;

ID  WID
---------- ----------
1    1
1    2
1    3
1    4
1   13
1   15
1   17

7 rows selected.

SQL> select * from test1;

ID
----------
31
1
1
1
1
12
14
16

8 rows selected.

SQL> select sequence1.currval from dual;
select sequence1.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence SEQUENCE1.CURRVAL is not yet defined in this session


SQL> select sequence1.nextval from dual;

   NEXTVAL
----------
51

SQL> select sequence1.currval from dual;

   CURRVAL
----------
51

SQL> 
SQL> 
SQL> 
SQL> alter trigger trigger_test1 disable;

Trigger altered.

SQL> alter table test all triggers disable;
alter table test all triggers disable
                 *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL> alter table test disable all triggers;

Table altered.

SQL> show user 
USER is "ICE2"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ impdp system/password remap_schema=ICE2:ICE2 logfile=impdp2.log directory=DP_DIR dumpfile=expdp1.dmp table_exists_action=truncate

Import: Release 11.2.0.3.0 - Production on Wed Jul 10 17:07:08 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=ICE2:ICE2 logfile=impdp2.log directory=DP_DIR dumpfile=expdp1.dmp table_exists_action=truncate 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ICE2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"ICE2"."SEQUENCE1" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "ICE2"."TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "ICE2"."TEST1" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ICE2"."TEST"                               5.460 KB       7 rows
. . imported "ICE2"."TEST1"                              5.046 KB       7 rows
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 17:07:20

[oracle@localhost ~]$ sqlplus /  as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 10 17:08:07 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

c
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn ice2/ice2
SP2-0734: unknown command beginning "cconn ice2..." - rest of line ignored.
SQL> conn ice2/ice2
Connected.
SQL> 
SQL> 
SQL> select * from test;

ID  WID
---------- ----------
1    1
1    2
1    3
1    4
1   13
1   15
1   17

7 rows selected.

SQL> select * from test1;

ID
----------
1
1
1
1
12
14
16

7 rows selected.

SQL> select sequence1.currval from dual;
select sequence1.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence SEQUENCE1.CURRVAL is not yet defined in this session


SQL> select sequence1.nextval from dual;

   NEXTVAL
----------
61

SQL> alter table test all triggers enable;
alter table test all triggers enable
                 *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


SQL> alter trigger trigger_test1 enable;

Trigger altered.

SQL> select sequence1.currval from dual;

   CURRVAL
----------
61

SQL>  alter table test enable all triggers;

Table altered.

SQL> select sequence1.currval from dual;

   CURRVAL
----------
61

喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有