在之前的文章《三個使用資料泵（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

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 Tablespace和Tempory 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是會報錯的。

4、Schema處理

在下面，我們看到了呼叫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失敗。所以，保證表空間存在也是一個必要條件；

ü 這裡面只包括了資料定義，不包括索引、參照約束；

6、Index建立

之後就是建立索引物件。針對不同的索引型別，集中對所有索引進行建立。嚴格的說，索引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;

8、Index統計量“匯入”

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,ch1,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檔案分析Oracle Data Pump資料匯入行為

相關文章