当前位置： 首页 > MIGRATE > 正文

impdp导入数据后序列的处理

huangtingzhong 发布于 2014-10-06 13:37MIGRATE抢沙发

下面测试来至于群ORACLE数据库超级群 里面一位兄弟，在impdp导入数据后，sequence的值无变化，估计是由于在导入的时候对象应该存在，导致impdp忽略已经存在的对象。目前impdp只提供对表已经存在的处理方式(table_exists_action),如果是其它已经存在的对象，会报ORA-31684的提示信息，不会中断impdp导入进程，希望在以后的版本中能提供其它对象已经存在的处理方式。

1，测试环境

www.htz.pw > select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

www.htz.pw > host uname -a
windows32 luoping 2.6.1 7601 i686-pc Intel unknown MinGW

2，创建测试序列

www.htz.pw > create user htz identified by oracle;
User created.
www.htz.pw > grant dba to htz;
Grant succeeded.
www.htz.pw > create sequence htz.test_sequence start with 1 increment by 1 cache 100;
Sequence created.
www.htz.pw > create table htz.htz (id1 number);
Table created.
www.htz.pw > begin
  2    for i in 1 .. 1000 loop
  3      insert into htz.htz values (htz.test_sequence.nextval);
  4    end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
www.htz.pw > commit;
Commit complete.
www.htz.pw > select htz.test_sequence.currval from dual;
   CURRVAL
----------
      1000
3，生成序列的DDL语句

这里使用了dbms_metadata.get_ddl与使用查询dba_sequences的方式来生成创建索引的语句

3.1 get_ddl方式

www.htz.pw > set echo off lines 2000 pages 0 verify off heading off long 1024 feed off  termout  off trimout  off trimspool off
www.htz.pw > col getddl for a1024
www.htz.pw > spool d:\spool.txt
www.htz.pw > select dbms_metadata.get_ddl(upper('sequence'),
  2                               SEQUENCE_NAME,
  3                               SEQUENCE_OWNER) || ';' as getddl
  4    from dba_sequences
  5   where sequence_owner in ('HTZ', 'SYS');
可以生成如下的sequence的所有的脚本
d:\wendang\SkyDrive\rs2\sql>grep -v "^$" d:\spool.txt|grep "CREATE SEQUENCE"
   CREATE SEQUENCE  "HTZ"."TEST_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1001 CACHE 100 NOORDER  NOCYCLE ;
   CREATE SEQUENCE  "SYS"."APPLY$_DEST_OBJ_ID"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;
   CREATE SEQUENCE  "SYS"."APPLY$_ERROR_HANDLER_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
   CREATE SEQUENCE  "SYS"."APPLY$_SOURCE_OBJ_ID"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;
   CREATE SEQUENCE  "SYS"."AQ$_ALERT_QT_N"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER  NOCYCLE ;
   CREATE SEQUENCE  "SYS"."AQ$_AQ$_MEM_MC_N"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE ;
3.2 查询dba_sequences视图
www.htz.pw > set echo off lines 2000 pages 0 verify off heading off long 1024 feed off  termout  off trimout  off trimspool off
www.htz.pw > col getddl for a1024
www.htz.pw > spool d:\spool.txt
www.htz.pw > select 'CREATE SEQUENCE  ' || sequence_owner || '.' || sequence_name ||
  2         ' MINVALUE ' || min_value || ' MAXVALUE ' || max_value ||
  3         ' INCREMENT BY ' || increment_by || ' START WITH ' || last_number ||
  4         ' CACHE ' || cache_size || ' ' ||
  5         decode(CYCLE_FLAG, 'N', 'NOCYCLE', 'Y', 'CYCLE') || ' ' ||
  6         decode(order_flag, 'Y', 'ORDER', 'N', 'NOORDER') || ';' as getddl
  7    from dba_sequences
  8   where sequence_owner in ('HTZ', 'SYS');
CREATE SEQUENCE  HTZ.TEST_SEQUENCE MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1001 CACHE 100 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.APPLY$_DEST_OBJ_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 0 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.APPLY$_ERROR_HANDLER_SEQUENCE MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.APPLY$_SOURCE_OBJ_ID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 0 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.AQ$_ALERT_QT_N MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.AQ$_AQ$_MEM_MC_N MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.AQ$_AQ_PROP_TABLE_N MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.AQ$_CHAINSEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.AQ$_IOTENQTXID MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 1000 NOCYCLE NOORDER;
CREATE SEQUENCE  SYS.AQ$_KUPC$DATAPUMP_QUETAB_N MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOCYCLE NOORDER;
4,expdp生成dump文件

expdp按htz用户来导出

d:\wendang\SkyDrive\rs2\sql>expdp htz/oracle directory=test dumpfile=test.dmp nologfile

Export: Release 11.2.0.3.0 - Production on Mon Oct 6 10:25:42 2014

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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "HTZ"."SYS_EXPORT_SCHEMA_01":  htz/******** directory=test dumpfile=test.dmp nologfile
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "HTZ"."HTZ"                                 12.70 KB    1000 rows
Master table "HTZ"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HTZ.SYS_EXPORT_SCHEMA_01 is:
  E:\TEST\TEST.DMP
Job "HTZ"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:26:09
4.1 生成ddl语句

生成dump文件中所有的ddl语句

d:\wendang\SkyDrive\rs2\sql>impdp htz/oracle directory=test dumpfile=test.dmp sqlfile=ddl.sql

Import: Release 11.2.0.3.0 - Production on Mon Oct 6 10:27:18 2014

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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HTZ"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "HTZ"."SYS_SQL_FILE_FULL_01":  htz/******** directory=test dumpfile=test.dmp sqlfile=ddl.sql
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
Job "HTZ"."SYS_SQL_FILE_FULL_01" successfully completed at 10:27:20

会生成如下的DDL语句
-- CONNECT HTZ
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
 CREATE USER "HTZ" IDENTIFIED BY VALUES 'S:CCD8F4CC7CE4D8E642530AD01DADE4B6F505B99C4EE21D8B41ACDE516F5D;F0220981DBF870F4'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "HTZ";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
 GRANT "DBA" TO "HTZ";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
 ALTER USER "HTZ" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT HTZ

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'HTZ', inst_scn=>'1464993');
COMMIT;
END;
/
-- new object type path: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
 CREATE SEQUENCE  "HTZ"."TEST_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1001 CACHE 100 NOORDER  NOCYCLE ;
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "HTZ"."HTZ"
   (        "ID1" NUMBER
   ) 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" ;
5，向HTZ表插入数据，使用序列

这里使用序列是为了与impdp导入数据库对比序列的变化

www.htz.pw > begin
  2    for i in 1 .. 1000 loop
  3      insert into htz.htz values (htz.test_sequence.nextval);
  4    end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
www.htz.pw > select count(*) from htz.htz;

  COUNT(*)
----------
      2000
www.htz.pw > select htz.test_sequence.currval from dual;
   CURRVAL
----------
      2000
6 impdp导入数据
d:\wendang\SkyDrive\rs2\sql>impdp htz/oracle directory=test dumpfile=test.dmp table_exists_action=truncate

Import: Release 11.2.0.3.0 - Production on Mon Oct 6 10:30:46 2014

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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "HTZ"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HTZ"."SYS_IMPORT_FULL_01":  htz/******** directory=test dumpfile=test.dmp table_exists_action=truncate
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HTZ" 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:"HTZ"."TEST_SEQUENCE" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "HTZ"."HTZ" 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 "HTZ"."HTZ"                                 12.70 KB    1000 rows
Job "HTZ"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:30:48

www.htz.pw > @sequence.sql

All Sequences in Database

Enter Search Sequence Owner (i.e. DEPT|ALL(DEFAULT)) : htz
Enter Search Sequence Name (i.e. DEPT|ALL(DEFAULT)) :

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- -----------
HTZ                            TEST_SEQUENCE                           1 1.0000E+28            1 NO      NO                   100        2001
这里看到last_number的值为2001,说明impdp导入数据后，序列的信息没有发生变化
7 序列的处理

7.1 利用dmp文件中DDL语句重建序列

d:\wendang\SkyDrive\rs2\sql>grep "CREATE SEQUENCE" e:\test\ddl.sql
 CREATE SEQUENCE  "HTZ"."TEST_SEQUENCE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1001 CACHE 100 NOORDER  NOCYCLE ;
这里也可以利用之前查询出来的序列的DDL语句来重建序列
7.2 生成drop语句
[root@www.htz.pw soft]#awk '/CREATE SEQUENCE/{printf ("DROP SEQUENCE %s;\n",$3)}' ddl.sql
DROP SEQUENCE "HTZ"."TEST_SEQUENCE";

www.htz.pw > DROP SEQUENCE "HTZ"."TEST_SEQUENCE";

Sequence dropped.
不过建议是从impdp日志文件中生成序列的drop语句，那样避免删除不重复的序列
[root@www.htz.pw soft]#awk '/ORA-31684/ {print $0}' /soft/import.log |awk -F: '/SEQUENCE/ {print $3}'|awk '{printf ("DROP SEQUENCE %s;\n",$1)}'
7.3 创建序列
www.htz.pw > @e:\test\ddl1.sql

Sequence created.

www.htz.pw > @sequence.sql

All Sequences in Database

Enter Search Sequence Owner (i.e. DEPT|ALL(DEFAULT)) : htz
Enter Search Sequence Name (i.e. DEPT|ALL(DEFAULT)) :

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- -----------
HTZ                            TEST_SEQUENCE                           1 1.0000E+28            1 NO      NO                   100        1001

测试结束，这里只测试序列的情况，其它除表以后的，其它的对象都会存在同样的问题。

赏

您可能还会对这些文章感兴趣！

impdp导入数据后序列的处理：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter