創(chuàng)新互聯(lián)長期為數(shù)千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為東山企業(yè)提供專業(yè)的做網(wǎng)站、成都網(wǎng)站建設(shè)，東山網(wǎng)站改版等技術(shù)服務(wù)。擁有10年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。
在之前的文章《三個使用數(shù)據(jù)泵（Data Pump）的小技巧》（http://space.itpub.net/17203031/viewspace-768245）里面，我們介紹了使用SQLFILE參數(shù)，可以輸出Data Pump數(shù)據(jù)泵生成的DDL語句和對應(yīng)的系列SQL語句。本篇，我們針對之前的結(jié)果，分析一下SQLFILE生成文件，從而了解一下Oracle Data Pump是怎么導(dǎo)入數(shù)據(jù)的。
1、環(huán)境介紹和背景介紹
我們依然選擇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
作為上篇的續(xù)文，依然使用上篇的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
調(diào)用impdp語句進行數(shù)據(jù)導(dǎo)入，生成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文件，進行分析。生成的文件體積較大，下面分為若干段結(jié)構(gòu)進行分析。
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角色權(quán)限。這些事件顯然屬于Oracle內(nèi)部的環(huán)境準(zhǔn)備。
我們通過一些方法，可以知道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 列能創(chuàng)建為 OCT 的事件
ORA-25475：與流Stream相關(guān)的事件
通過這些等待事件的設(shè)置，主要是處于將工作保證，對一些系統(tǒng)環(huán)境進行重置。防止?jié)撛趩栴}的出現(xiàn)和便于導(dǎo)入過程。
3、用戶創(chuàng)建和權(quán)限設(shè)置
Data Pump是會自動的創(chuàng)建出用戶信息，并且給用戶賦予相應(yīng)的權(quán)限。首先是用戶創(chuàng)建。
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:CB685EC52372362B49B7EC43AB0B04BCAF2C71F283C5A558FF8E430F5365;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
注意，用戶密碼是以密文方式顯示出來，保證了原有數(shù)據(jù)。同時Default Tablespace和Tempory Tablespace都是明確的指定出來。這也就是為什么我們在使用Data Pump導(dǎo)入數(shù)據(jù)的時候，用戶可以不創(chuàng)建，但是表空間一定要規(guī)劃好。如果表空間規(guī)劃有問題，用戶創(chuàng)建失敗，后面所有的對象就是一連串的失敗信息，導(dǎo)入也就沒有意義了。
用戶創(chuàng)建之后，就可以根據(jù)系統(tǒng)權(quán)限、角色權(quán)限和對象權(quán)限的類型進行權(quán)限賦予。
-- 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回去創(chuàng)建用戶的賦予權(quán)限。但是對角色Role，如果事先沒有，Oracle是會報錯的。
4、Schema處理
在下面，我們看到了調(diào)用pl/sql匿名塊過程，調(diào)用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;
/
這個方法是一個內(nèi)部方法，代碼是被wrap過的。筆者不知道這個方法的作用。只能猜測是和Data Pump工作過程中Schema初始化有關(guān)的操作。其中參數(shù)還包括SCN編號。
5、數(shù)據(jù)表DDL創(chuàng)建
下面就是數(shù)據(jù)表DDL語句，所有數(shù)據(jù)對象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" ;
這里面注意幾個問題：
ü 數(shù)據(jù)表創(chuàng)建過程中，使用的DDL語句是“全文”的。也就是包括了定義一個數(shù)據(jù)表的全部參數(shù)，其中有一些我們平時很少接觸，或者其他版本不能支持的特性。如果遇到了兼容性問題，可以在SQLFile中直接修改；
ü 表空間，我們說的是數(shù)據(jù)表段的表空間是再此指定的。如果表空間不存在，Data Pump是不會創(chuàng)建數(shù)據(jù)表，并且報錯。進而后面的數(shù)據(jù)Import失敗。所以，保證表空間存在也是一個必要條件；
ü 這里面只包括了數(shù)據(jù)定義，不包括索引、參照約束；
6、Index創(chuàng)建
之后就是創(chuàng)建索引對象。針對不同的索引類型，集中對所有索引進行創(chuàng)建。嚴格的說，索引Index也是一種段結(jié)構(gòu)，段結(jié)構(gòu)參數(shù)，如初始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;
如果沒有并行設(shè)置，索引在之后還要設(shè)置上noparallel。
7、組件約束設(shè)置
索引創(chuàng)建之后，創(chuàng)建約束對象，包括主鍵關(guān)系。
-- 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統(tǒng)計量“導(dǎo)入”
Oracle Data Pump在導(dǎo)入的時候，是連帶將統(tǒng)計量“導(dǎo)入”進去。雖然Oracle可以選擇數(shù)據(jù)表數(shù)據(jù)插入之后，現(xiàn)去收集統(tǒng)計量，但是還是選擇將統(tǒng)計量導(dǎo)入進去。
首先Data Pump導(dǎo)入索引的統(tǒng)計量。
-- 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導(dǎo)入導(dǎo)出一個統(tǒng)計量，都是需要一個中間數(shù)據(jù)表。在這個過程中，我們看到了Oracle用一個sys.impdp_stats數(shù)據(jù)表。先將其清空，之后插入一條數(shù)據(jù)。最后調(diào)用dbms_stats.import_index_stats方法將數(shù)據(jù)表導(dǎo)入到系統(tǒng)中。
Impdp_stats數(shù)據(jù)表是Oracle內(nèi)部的一個工具表。
SQL> desc impdp_stats;
Name Type Nullable Default Comments
------- -------------- -------- ------- --------
STATID VARCHAR2(30) Y
TYPE CHAR(1) Y
VERSION NUMBER Y
FLAGS NUMBER Y
（篇幅原因，有省略……）
每一個索引對應(yīng)一個PL/SQL匿名塊。
9、視圖View對象創(chuàng)建
創(chuàng)建Index統(tǒng)計量之后，創(chuàng)建視圖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、外鍵約束關(guān)系
外鍵關(guān)系在視圖之后進行創(chuàng)建。
-- 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;
外鍵創(chuàng)建之后，就直接啟用。注意如果數(shù)據(jù)量很大，并且前期索引關(guān)系沒有設(shè)置好，這個過程可能持續(xù)時間很長。
11、數(shù)據(jù)表統(tǒng)計量導(dǎo)入
之后是數(shù)據(jù)表統(tǒng)計量的導(dǎo)入。和索引不同的是，一個數(shù)據(jù)表統(tǒng)計量是通過多條impdp_stats記錄來完成。從下面分別從數(shù)據(jù)表和字段信息來進行導(dǎo)入。
-- 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;
/
和索引不一樣的，由于數(shù)據(jù)表column數(shù)量不一樣，一個PL/SQL匿名塊只導(dǎo)入幾個column統(tǒng)計量。如果column數(shù)目多，可能會拆成多個匿名塊。
我們思考一個統(tǒng)計量問題：Oracle明明可以重新收集一下統(tǒng)計量，為什么還要將統(tǒng)計量數(shù)據(jù)保存在DMP文件里面占據(jù)空間。并且在數(shù)據(jù)之后導(dǎo)入到其中。
筆者認為這個是Oracle從兩個方面考量：
首先是時間上，如果數(shù)據(jù)表很大、結(jié)構(gòu)復(fù)雜，收集一次統(tǒng)計量的時間是比較長的。也就是說，統(tǒng)計量獲取的過程和數(shù)據(jù)表大小有關(guān)系。而數(shù)據(jù)統(tǒng)計量導(dǎo)入的動作，各個數(shù)據(jù)持續(xù)時間沒有什么差別。想必這個是Oracle的一個綜合考慮。
另外，從執(zhí)行計劃CBO生成的角度看，“導(dǎo)入”統(tǒng)計量也是有其合理性。有時候我們可能需要固定統(tǒng)計量，也就是希望數(shù)據(jù)統(tǒng)計量不要“及時更新”，從而固化執(zhí)行計劃。
12、結(jié)論
Oracle Datapump生成的SQLFILE可以幫助我們了解其運行細節(jié)。
重慶分公司
重慶分公司
