189 8069 5689

从SQLFile文件分析OracleDataPump数据导入行为

 

创新互联长期为数千家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为东山企业提供专业的做网站、成都网站建设东山网站改版等技术服务。拥有10年丰富建站经验和众多成功案例,为您定制开发。

在之前的文章《三个使用数据泵(Data Pump)的小技巧》(http://space.itpub.net/17203031/viewspace-768245)里面,我们介绍了使用SQLFILE参数,可以输出Data Pump数据泵生成的DDL语句和对应的系列SQL语句。本篇,我们针对之前的结果,分析一下SQLFILE生成文件,从而了解一下Oracle Data Pump是怎么导入数据的。

 

1、环境介绍和背景介绍

 

我们依然选择Oracle 11g进行试验。

 

 

SQL> select * from v$version;

 

BANNER

-------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE       11.2.0.1.0        Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

 

作为上篇的续文,依然使用上篇的directory对象和dmp文件。

 

 

SQL> select directory_name, directory_path from dba_directories where directory_name='DUMPTEST';

 

DIRECTORY_NAME                DIRECTORY_PATH

------------------------------ --------------------

DUMPTEST                      /dumptest

 

[oracle@bspdev dumptest]$ ls -l

-rw-r----- 1 oracle oinstall 109694976 Aug 12 03:34 scott.dmp

-rw-r----- 1 oracle oinstall 109690880 Aug 12 05:32 scott_par.dmp

 

 

调用impdp语句进行数据导入,生成SQLFile

 

 

[oracle@bspdev dumptest]$ impdp \"/ as sysdba\" directory=dumptest dumpfile=scott.dmp sqlfile=scott_all.sql

 

Import: Release 11.2.0.1.0 - Production on Thu Aug 15 05:02:47 2013

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=dumptest dumpfile=scott.dmp sqlfile=scott_all.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/TABLE/TABLE

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

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 05:02:59

 

 

[oracle@bspdev dumptest]$ ls -l | grep scott_all

-rw-r--r-- 1 oracle oinstall    71324 Aug 15 05:02 scott_all.sql

 

 

我们通过FTP获取到sql文件,进行分析。生成的文件体积较大,下面分为若干段结构进行分析。

 

2、会话修改和跟踪时间片段

 

trace文件中,我们首先看到的是一系列的alter session命令片段。

 

 

-- CONNECT SYS

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 ';

 

 

能够使用Data Pump的用户,要求具有DATAPUMP_IMP_FULL_DATABASE角色权限。这些事件显然属于Oracle内部的环境准备。

 

我们通过一些方法,可以知道10000-10999时间编号的方法。

 

 

ORA-10150: import exceptions

ORA-10904: Allow locally managed tablespaces to have user allocation

ORA-10407: enable datetime TIME datatype creation

ORA-10851: Allow Drop command to drop queue tables

ORA-22830: 使 VARRAY 列能创建为 OCT 的事件

ORA-25475:与流Stream相关的事件

 

 

通过这些等待事件的设置,主要是处于将工作保证,对一些系统环境进行重置。防止潜在问题的出现和便于导入过程。

 

3、用户创建和权限设置

 

Data Pump是会自动的创建出用户信息,并且给用户赋予相应的权限。首先是用户创建。

 

 

-- new object type path: SCHEMA_EXPORT/USER

-- CONNECT SYSTEM

 CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:CB685EC52372362B49B7EC43AB0B04BCAF2C71F283C5A558FF8E430F5365;F894844C34402B67'

     DEFAULT TABLESPACE "USERS"

     TEMPORARY TABLESPACE "TEMP";

 

 

注意,用户密码是以密文方式显示出来,保证了原有数据。同时Default TablespaceTempory Tablespace都是明确的指定出来。这也就是为什么我们在使用Data Pump导入数据的时候,用户可以不创建,但是表空间一定要规划好。如果表空间规划有问题,用户创建失败,后面所有的对象就是一连串的失败信息,导入也就没有意义了。

 

用户创建之后,就可以根据系统权限、角色权限和对象权限的类型进行权限赋予。

 

 

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT EXECUTE ANY PROCEDURE TO "SCOTT";

 

GRANT CREATE VIEW TO "SCOTT";

 

GRANT SELECT ANY TABLE TO "SCOTT";

 

GRANT UNLIMITED TABLESPACE TO "SCOTT";

 

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

 GRANT "CONNECT" TO "SCOTT";

 

 GRANT "RESOURCE" TO "SCOTT";

 

 GRANT "SELECT_CATALOG_ROLE" TO "SCOTT";

 

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

 ALTER USER "SCOTT" DEFAULT ROLE ALL;

 

 

注意:一个常见的问题是,Oracle Data Pump回去创建用户的赋予权限。但是对角色Role,如果事先没有,Oracle是会报错的。

 

4Schema处理

 

在下面,我们看到了调用pl/sql匿名块过程,调用oracle SYS用户下的存储过程。

 

 

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT SCOTT

 

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'WILSON', inst_scn=>'3501442');

COMMIT;

END;

/

 

 

这个方法是一个内部方法,代码是被wrap过的。笔者不知道这个方法的作用。只能猜测是和Data Pump工作过程中Schema初始化有关的操作。其中参数还包括SCN编号。

 

5、数据表DDL创建

 

下面就是数据表DDL语句,所有数据对象DDL,都是全文显示。其中包括了Segment信息和Table Annotation

 

 

-- new object type path: SCHEMA_EXPORT/TABLE/TABLE

-- CONNECT SYS

CREATE TABLE "SCOTT"."DEPT"

  ("DEPTNO" NUMBER(2,0),

        "DNAME" VARCHAR2(14 BYTE),

        "LOC" VARCHAR2(13 BYTE)

  ) 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" ;

 

CREATE TABLE "SCOTT"."EMP"

  ("EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10 BYTE),

        "JOB" VARCHAR2(9 BYTE),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0)

  ) 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" ;

 

 

这里面注意几个问题:

 

ü 数据表创建过程中,使用的DDL语句是“全文”的。也就是包括了定义一个数据表的全部参数,其中有一些我们平时很少接触,或者其他版本不能支持的特性。如果遇到了兼容性问题,可以在SQLFile中直接修改;

ü 表空间,我们说的是数据表段的表空间是再此指定的。如果表空间不存在,Data Pump是不会创建数据表,并且报错。进而后面的数据Import失败。所以,保证表空间存在也是一个必要条件;

ü 这里面只包括了数据定义,不包括索引、参照约束;

 

6Index创建

 

之后就是创建索引对象。针对不同的索引类型,集中对所有索引进行创建。严格的说,索引Index也是一种段结构,段结构参数,如初始extent大小,都是需要定义出来的。

 

 

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX

-- CONNECT SCOTT

CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")

 PCTFREE 10 INITRANS 2 MAXTRANS 255

 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" PARALLEL 1 ;

 

 ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL;

 

CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")

 PCTFREE 10 INITRANS 2 MAXTRANS 255

 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" PARALLEL 1 ;

 

 ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;

 

 

如果没有并行设置,索引在之后还要设置上noparallel

 

7、组件约束设置

 

索引创建之后,创建约束对象,包括主键关系。

 

 

-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

-- CONNECT SYS

ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

 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" ENABLE;

 

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

 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" ENABLE;

 

 

 

8Index统计量“导入”

 

Oracle Data Pump在导入的时候,是连带将统计量“导入”进去。虽然Oracle可以选择数据表数据插入之后,现去收集统计量,但是还是选择将统计量导入进去。

 

首先Data Pump导入索引的统计量。

 

 

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

DECLARE I_N VARCHAR2(60);

 I_O VARCHAR2(60);

 c DBMS_METADATA.T_VAR_COLL;

 df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

BEGIN

 DELETE FROM "SYS"."IMPDP_STATS";

  i_n := 'PK_DEPT';

 i_o := 'SCOTT';

  INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES ('I',5,2,I_N,NULL,NULL,I_O,4,1,4,1,1,1,0,4,NULL,NULL,NULL,NULL,TO_DATE('2012-06-23 01:37:56',df),NULL);

 

 DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');

 DELETE FROM "SYS"."IMPDP_STATS";

END;

/

 

 

上面是一个PL/SQL匿名块。Oracle导入导出一个统计量,都是需要一个中间数据表。在这个过程中,我们看到了Oracle用一个sys.impdp_stats数据表。先将其清空,之后插入一条数据。最后调用dbms_stats.import_index_stats方法将数据表导入到系统中。

 

Impdp_stats数据表是Oracle内部的一个工具表。

 

 

SQL> desc impdp_stats;

Name   Type          Nullable Default Comments

------- -------------- -------- ------- --------

STATID VARCHAR2(30)  Y                        

TYPE   CHAR(1)       Y                        

VERSION NUMBER        Y                        

FLAGS  NUMBER        Y

(篇幅原因,有省略……

 

 

每一个索引对应一个PL/SQL匿名块。

 

9、视图View对象创建

 

创建Index统计量之后,创建视图view对象。

 

 

-- new object type path: SCHEMA_EXPORT/VIEW/VIEW

-- CONNECT SCOTT

CREATE FORCE VIEW "SCOTT"."XX" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME") AS

 select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME" from m

 

;

 

 

10、外键约束关系

 

外键关系在视图之后进行创建。

 

 

-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

-- CONNECT SYS

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

         REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;

 

ALTER TABLE "SCOTT"."T_CHILD2" ADD CONSTRAINT "FK_CHILD2_MASTER" FOREIGN KEY ("MID")

         REFERENCES "SCOTT"."T_MASTER" ("ID") ENABLE;

 

 

外键创建之后,就直接启用。注意如果数据量很大,并且前期索引关系没有设置好,这个过程可能持续时间很长。

 

 

11、数据表统计量导入

 

之后是数据表统计量的导入。和索引不同的是,一个数据表统计量是通过多条impdp_stats记录来完成。从下面分别从数据表和字段信息来进行导入。

 

 

-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

DECLARE

 c varchar2(60);

 nv varchar2(1);

 df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

 s varchar2(60) := 'SCOTT';

 t varchar2(60) := 'DEPT';

 p varchar2(1);

 sp varchar2(1);

 stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch2,flags) VALUES (''C'',5,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)';

BEGIN

 DELETE FROM "SYS"."IMPDP_STATS";

 INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n10,n11,n12,d1) VALUES ('T',5,2,t,p,sp,s,

              4,5,20,4,NULL,NULL,NULL,

              TO_DATE('2012-06-23 01:37:56',df));

 c := 'DEPTNO';

 EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

              4,.25,4,4,0,10,40,3,nv,nv,nv,

              TO_DATE('2012-06-23 01:37:56',df),'C10B','C129',nv,2;

 c := 'DNAME';

 EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

              4,.25,4,4,0,3.38863550087541E+35,4.32285038677786E+35,10,nv,nv,nv,

              TO_DATE('2012-06-23 01:37:56',df),'4143434F554E54494E47','53414C4553',nv,2;

 c := 'LOC';

 EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

              4,.25,4,4,0,3.44300505052090E+35,4.06405544089997E+35,8,nv,nv,nv,

              TO_DATE('2012-06-23 01:37:56',df),'424F53544F4E','4E455720594F524B',nv,2;

 

 DBMS_STATS.IMPORT_TABLE_STATS('"SCOTT"','"DEPT"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');

 DELETE FROM "SYS"."IMPDP_STATS";

END;

/

 

 

和索引不一样的,由于数据表column数量不一样,一个PL/SQL匿名块只导入几个column统计量。如果column数目多,可能会拆成多个匿名块。

 

我们思考一个统计量问题:Oracle明明可以重新收集一下统计量,为什么还要将统计量数据保存在DMP文件里面占据空间。并且在数据之后导入到其中。

 

笔者认为这个是Oracle从两个方面考量:

 

首先是时间上,如果数据表很大、结构复杂,收集一次统计量的时间是比较长的。也就是说,统计量获取的过程和数据表大小有关系。而数据统计量导入的动作,各个数据持续时间没有什么差别。想必这个是Oracle的一个综合考虑。

 

另外,从执行计划CBO生成的角度看,“导入”统计量也是有其合理性。有时候我们可能需要固定统计量,也就是希望数据统计量不要“及时更新”,从而固化执行计划。

 

12、结论

 

Oracle Datapump生成的SQLFILE可以帮助我们了解其运行细节。


本文题目:从SQLFile文件分析OracleDataPump数据导入行为
网站URL:http://cdxtjz.cn/article/jjhcss.html

其他资讯