【資料泵】EXPDP導出表結構(真實案例)

BLOG文檔結構圖

因工作需要現需要把一個生産庫下的中繼資料（表定義，索引定義，函數定義，包定義，存儲過程）導出到測試庫上，本來以為很簡單的，可是做的過程發現很多的問題，現記錄如下，希望有同樣需要的朋友不要再走彎路了。

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① EXPDP和IMPDP如何導出導入中繼資料，包括表定義，索引定義，函數定義，包定義，存儲過程（重點）

② 表的初始化定義參數initial，及如何批量修改該參數

③ 如何導出DMP檔案中的DDL語句（重點）

④ 10g和11g預設情況下有哪些使用者及其作用

⑤ linux中的批量替換sed指令

⑥ sqlldr和spool指令

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

源庫：10.2.0.1 AIX

目标庫：11.2.0.3 RHEL6.5

oracle安裝好後有很多的系統預設使用者，比如sys和system，對于這2個使用者裡的中繼資料我們就沒有必要再重新導出嘛，不然導入的時候還提示錯誤，看着實在不好。

官網資訊：

All databases created by the Database Configuration Assistant (DBCA) include the <code>SYS</code>, <code>SYSTEM</code>, <code>SYSMAN</code>, and <code>DBSNMP</code> database accounts. In addition, Oracle Database provides several other administrative accounts. Before using these accounts, you must unlock them and reset their passwords.

11g預設使用者比較多：

User Name

Description

See Also

ANONYMOUS

Enables HTTP access to Oracle XML DB.

<a>Oracle XML DB Developer's Guide</a>

APEX_030200

The account owns the Application Express schema and metadata.

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e11947/toc.htm">Oracle Application Express Application Builder User's Guide</a>

APEX_PUBLIC_USER

The minimally privileged account used for Application Express configuration with Oracle HTTP Server and mod_plsql.

APPQOSSYS

Used for storing and managing all data and metadata required by Oracle Quality of Service Management.

None

The account that owns the Business Intelligence schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e10831/toc.htm">Oracle Database Sample Schemas</a>

CTXSYS

The Oracle Text account.

<a href="http://docs.oracle.com/cd/E11882_01/text.112/e24436/toc.htm">Oracle Text Reference</a>

DBSNMP

The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.

Oracle Enterprise Manager Grid Control Installation and Basic Configuration

DIP

The account used by the Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.

DVSYS

There are two roles associated with this account. The Database Vault owner role manages the Database Vault roles and configurations. The Database Vault Account Manager is used to manage database user accounts.

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e23090/toc.htm">Oracle Database Vault Administrator's Guide</a>

Note: Part of Oracle Database Vault user interface text is stored in database tables in the DVSYS schema. By default, only the English language is loaded into these tables. You can use Oracle Database Vault Configuration Assistant to add more languages to Oracle Database Vault. For the necessary steps, see Appendix C in Oracle Database Vault Administrator's Guide

EXFSYS

The account owns the Expression Filter schema.

FLOWS_FILES

The account owns the Application Express uploaded files.

The account that owns the Human Resources schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

The account that owns the Information Transport schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

LBACSYS

The Oracle Label Security administrator account.

<a href="http://docs.oracle.com/cd/E11882_01/network.112/e10745/toc.htm">Oracle Label Security Administrator's Guide</a>

MDDATA

The schema used by Oracle Spatial for storing geocoder and router data.

<a>Oracle Spatial Developer's Guide</a>

MDSYS

The Oracle Spatial and Oracle Multimedia Locator administrator account.

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/toc.htm">Oracle Spatial Developer's Guide</a>

MGMT_VIEW

An account used by Oracle Enterprise Manager Database Control.

The account that owns the Order Entry schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

ORDPLUGINS

The Oracle Multimedia user. Plug-ins supplied by Oracle and third-party plug-ins are installed in this schema.

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10776/toc.htm">Oracle Multimedia Reference</a>

ORDSYS

The Oracle Multimedia administrator account.

ORDDATA

This account contains the Oracle Multimedia DICOM data model.

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10778/toc.htm">Oracle Multimedia DICOM Developer's Guide</a>

OUTLN

The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e40540/toc.htm">Oracle Database Concepts</a>

ORACLE_OCM

This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.

<a href="http://www.oracle.com/pls/topic/lookup?ctx=db112&id=CCRIA">Oracle Configuration Manager Installation and Administration Guide</a>

OWBSYS

The account used by Oracle Warehouse Builder as its default repository. You must unlock this account after installing the Oracle Database and before launching the Warehouse Builder Repository Assistant.

<a href="http://docs.oracle.com/cd/E11882_01/owb.112/e17130/toc.htm">Oracle Warehouse Builder Installation and Administration Guide</a>

OWBSYS_AUDIT

This account is used by the Warehouse Builder Control Center Agent to access the heterogeneous execution audit tables in the OWBSYS schema.

The account that owns the Product Media schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas.

SCOTT

An account used by Oracle sample programs and examples.

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e25494/toc.htm">Oracle Database Administrator's Guide</a>

The account that owns the Sales History schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas during an Enterprise Edition installation.

SI_INFORMTN_SCHEMA

The account that stores the information views for the SQL/MM Still Image Standard.

SPATIAL_CSW_ADMIN_USR

The Catalog Services for the Web (CSW) account. It is used by the Oracle Spatial CSW cache manager to load all record type metadata, and record instances from the database into the main memory for the record types that are cached.

SPATIAL_WFS_ADMIN_USR

The Web Feature Service (WFS) account. It is used by the Oracle Spatial WFS cache manager to load all feature-type metadata, and feature instances from the database into main memory for the feature types that are cached.

SYS

The account used to perform database administration tasks.

SYSMAN

The account used to perform Oracle Enterprise Manager database administration tasks.

SYSTEM

Another account used to perform database administration tasks.

WMSYS

The account used to store the metadata information for Oracle Workspace Manager.

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/toc.htm">Oracle Database Workspace Manager Developer's Guide</a>

XDB

The account used for storing Oracle XML DB data and metadata.

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/toc.htm">Oracle XML DB Developer's Guide</a>

10g下比較少：

CTXSYS

The Oracle Text account

<a href="http://docs.oracle.com/cd/B19306_01/text.102/b14218/toc.htm">Oracle Text Reference</a>

DBSNMP

The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database

<a href="http://docs.oracle.com/cd/B19306_01/em.102/b40103/toc.htm">Oracle Enterprise Manager Grid Control Installation and Basic Configuration</a>

LBACSYS

The Oracle Label Security administrator account

<a href="http://docs.oracle.com/cd/B19306_01/network.102/b14267/toc.htm">Oracle Label Security Administrator's Guide</a>

MDDATA

The schema used by Oracle Spatial for storing Geocoder and router data

<a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/toc.htm">Oracle Spatial User's Guide and Reference</a>

MDSYS

The Oracle Spatial and Oracle interMedia Locator administrator account

DMSYS

DMSYS

The Oracle Data Mining account.

<a href="http://docs.oracle.com/cd/B19306_01/datamine.102/b14338/toc.htm">Oracle Data Mining Administrator's Guide</a>

<a href="http://docs.oracle.com/cd/B19306_01/datamine.102/b14339/toc.htm">Oracle Data Mining Concepts</a>

OLAPSYS

MANAGER

The account used to create OLAP metadata structures. It owns the OLAP Catalog (CWMLite).

<a href="http://docs.oracle.com/cd/B19306_01/olap.102/b14349/toc.htm">Oracle OLAP Application Developer's Guide</a>

ORDPLUGINS

The Oracle interMedia user. Plug-ins supplied by Oracle and third party format plug-ins are installed in this schema.

<a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14302/toc.htm">Oracle interMedia User's Guide</a>

ORDSYS

The Oracle interMedia administrator account

OUTLN

<a href="http://docs.oracle.com/cd/B19306_01/server.102/b14211/toc.htm">Oracle Database Performance Tuning Guide</a>

SI_INFORMTN_SCHEMA

The account that stores the information views for the SQL/MM Still Image Standard

SYS

CHANGE_ON_INSTALL

The account used to perform database administration tasks

<a href="http://docs.oracle.com/cd/B19306_01/server.102/b14231/toc.htm">Oracle Database Administrator's Guide</a>

SYSMAN

The account used to perform Oracle Enterprise Manager database administration tasks. Note that SYS and SYSTEM can also perform these tasks.

SYSTEM

SELECT D.tablespace_name, SUM(D.initial_extent)/1024/1024 initial_extent

FROM DBA_SEGMENTS D

WHERE D.owner IN

(SELECT a.username

FROM DBA_USERS A

WHERE A.account_status = 'OPEN'

AND A.username NOT IN

('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'))

GROUP BY D.tablespace_name

ORDER BY initial_extent desc ;

SELECT SUM(D.initial_extent)/1024/1024 initial_extent

;

由此可以知道，建立這些中繼資料大約需要49G的空間，如果涉及到資料的話，還需要判斷資料占用空間，這裡一定需要判斷這個，不然執行導入的時候會因為表空間不足而不能導入，我第一次導入的時候就是因為這裡沒有判斷導緻花費了很長的時間，我一直擴充表空間，但是就是就表空間不足的錯誤(ORA-01659),想想建表不會花這麼大的空間的吧，最後檢視了表的定義才知道，原來表初始化的時候就很大，這個問題後邊還需要再處理一下的，不然測試庫沒法導入，當然存儲夠的話就另當别論了。

ORA-39171: Job is experiencing a resumable wait.

ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace DWII_SOR_F_01

這一步也很重要，決定着最終導出結果的正确性驗證。

SELECT d.OWNER,count(1)

FROM dba_objects d

WHERE d.OWNER in (SELECT a.username

group by d.OWNER;

SELECT d.OWNER,

d.status,count(1)

group by d.OWNER, d.status;

d.OBJECT_NAME,

d.OBJECT_TYPE,

d.status

WHERE d.status = 'INVALID'

and d.owner in (SELECT a.username

('SYS', 'SYSTEM', 'MGMT_VIEW', 'SYSMAN', 'DBSNMP'));

導出指令，注意這裡不導出資料隻導出定義我們采用content=metadata_only來處理：

expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER,DPA,CNYDM,ONL1,LHR,TEST1,FXDM,DWII_ETL,DWUSER1,SOR,DW_ETL,NRDM,NRDM_ETL,FXDM_ETL,LCM2,CNY_ETL

由于是事後寫文檔，是以這裡隻貼出導出中繼資料的日志：

;;;

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 15 May, 2015 13:05:54

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "LHR"."SYS_EXPORT_SCHEMA_01": lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515.log content=metadata_only schemas=TEST,SQCHECK,DWUSER,DPA,CNYDM,ONL1,LHR,TEST1,FXDM,DWII_ETL,DWUSER1,SOR,DW_ETL,NRDM,NRDM_ETL,FXDM_ETL,LCM2,CNY_ETL

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/DB_LINK

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

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/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/VIEW/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Master table "LHR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for LHR.SYS_EXPORT_SCHEMA_01 is:

/oracle/product/10.2.0/db_1/rdbms/log/lhrsql20150515.dmp

Job "LHR"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:09:49

接下來就是把導出來的檔案利用ftp工具或scp傳遞到目标端，我這裡就直接傳到我的虛拟機上吧。

根據上邊的工作我們知道需要在測試庫上建立以下的表空間，我這裡都設定的是20M，實際情況下應該根據上邊查詢出來的結果來設定相應的大小，表空間應用包含使用者的預設表空間及使用者下對象所在的表空間：

create tablespace DWII_CNY_BK_F_01 datafile '+DATA' size 20M;

create tablespace DWII_DPA_F_01 datafile '+DATA' size 20M;

create tablespace DWII_DPA_I_01 datafile '+DATA' size 20M;

create tablespace DWII_DPA_S_01 datafile '+DATA' size 20M;

create tablespace DWII_SOR_F_01 datafile '+DATA' size 20M;

create tablespace DWII_SOR_I_01 datafile '+FRA' size 20M;

create tablespace DW_USER datafile '+FRA' size 20M;

create tablespace SQCHECK datafile '+FRA' size 20M;

create tablespace SD_CNY_D_01 datafile '+FRA' size 20M;

create tablespace SD_CNY_F_01 datafile '+FRA' size 20M;

create tablespace SD_DPA_D_01 datafile '+FRA' size 20M;

create tablespace SD_DPA_F_01 datafile '+FRA' size 20M;

create tablespace SD_SORT_T_01 datafile '+FRA' size 20M;

create tablespace DWII_FXDM_F_01 datafile '+FRA' size 20M;

create tablespace SD_SOR_T_01 datafile '+FRA' size 20M;

如果空間不夠，我們可以追加資料檔案：alter tablespace DWII_DPA_F_01 add datafile '+FRA' size 50M;

一般情況下，如果存儲夠的話，我們把相應的表空間設定大一點之後這裡直接執行導入語句就可以了，但是我是在本機的虛拟機裡執行的，由前邊的情況我們可以知道大約需要49G的空間，這個顯然不太合适，哪該怎麼辦呢？我能想到的辦法隻有如下2種，如果大家還有好的辦法可以給我留言。

① 在源庫上修改表的定義後然後再執行導出指令

② 從已經導出來的dmp檔案中抽取其中的DDL語句，然後将DDL語句導入到資料庫中，update掉其中的STORAGE(INITIAL 參數後再将語句導出到sql文本中執行sql語句，這樣可以解決表的定義問題。

③ 從已經導出來的dmp檔案中抽取其中的DDL語句，然後利用linux的sed批量替換功能替換掉不正确的參數。

顯然，第一種比較友善，也比較快，但是不實用，由于是生産庫，參數不能随便修改，我們就采用第二或第三種辦法，也可以多示範一種impdp的用法。

1.4.2.1 導出DMP檔案中的DDL語句

我們在impdp的導入指令中添加sqlfile參數後執行導入并不會真正将資料導入到資料庫，而會抽取出dmp檔案中的DDL語句，如下：

[oracle@rhel6_lhr dpdump]$ impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql

Import: Release 11.2.0.3.0 - Production on Fri May 15 15:08:03 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

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

Starting "LHR"."SYS_SQL_FILE_FULL_01": lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log sqlfile=lhrsql20150515.sql

Job "LHR"."SYS_SQL_FILE_FULL_01" successfully completed at 15:21:50

[oracle@rhel6_lhr dpdump]$ ll lhrsql20150515.sql

-rw-r--r-- 1 oracle asmadmin 65707967 May 15 15:21 lhrsql20150515.sql

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql

-- CONNECT LHR

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 "CNY_ETL" IDENTIFIED BY VALUES '4686A1050F638F44'

DEFAULT TABLESPACE "DW_USER"

TEMPORARY TABLESPACE "TEMP";

CREATE USER "LCM2" IDENTIFIED BY VALUES '48BCFDF435352212'

DEFAULT TABLESPACE "DWII_SOR_F_01"

CREATE USER "FXDM_ETL" IDENTIFIED BY VALUES 'EA010AEA839BFA14'

CREATE USER "NRDM_ETL" IDENTIFIED BY VALUES '54A4A046AEE8B31E'

CREATE USER "NRDM" IDENTIFIED BY VALUES '1AE3DF7368DF560D'

DEFAULT TABLESPACE "SD_CNY_F_01"

CREATE USER "DW_ETL" IDENTIFIED BY VALUES '91635F9C0744E7EC'

CREATE USER "SOR" IDENTIFIED BY VALUES 'BA3A6C912E6BFF14'

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。省略

[oracle@rhel6_lhr dpdump]$ tail -n 50 lhrsql20150515.sql

c := 'SPOT_EXCHNG_RATE_SRC';

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

2,.5,2,156,0,2.65784513562818E+35,2.65784513872303E+35,5,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'33303031','33303032',nv,2,nv;

c := 'SPOT_EXCHNG_RATE_SRC_NM';

2,.5,2,156,0,1.18758942587854E+36,1.20817519861257E+36,12,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'E4B8ADE997B4E4BBB7','E8AFA2E4BBB7E59D87E580BC',nv,2,nv;

c := 'FLEG_SWAP_PNT';

14,.0714285714285714,14,156,0,-64.01,16.5,4,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'3E256466','C11133',nv,2,nv;

c := 'SRC_SYS_LBL';

1,1,1,156,0,3.44097282552972E+35,3.44097282552972E+35,5,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'42454E4D','42454E4D',nv,2,nv;

END;

DECLARE

c varchar2(60);

nv varchar2(1);

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

s varchar2(60) := 'DPA';

t varchar2(60) := 'BNCHMK_OPTN_DLT_PARAM_F';

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,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';

BEGIN

NULL;

c := 'CRT_TMST';

1,1,1,156,0,2457157.57244213,2457157.57244213,11,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'7873050E0E2D14','7873050E0E2D14',nv,2,nv;

c := 'PPLN_WKDT';

1,1,1,156,0,20150422,20150422,6,0,nv,nv,

TO_DATE('2015-05-14 22:00:18',df),'C415100517','C415100517',nv,2,nv;

c := 'PPLN_TMST';

DBMS_STATS.IMPORT_TABLE_STATS('"DPA"','"BNCHMK_OPTN_DLT_PARAM_F"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

[oracle@rhel6_lhr dpdump]$

可以看到生成的SQL語句開始部分是ddl的建使用者語句，最後是導入表的統計資訊部分。

1.4.2.2 處理導出來的ddl檔案

一、方法一，利用sed指令批量替換（推薦）

這一步其實如果linux的sed指令熟悉的話可以直接替換掉sql語句中的相關行，我今天也是臨時網上搜了下，經過大量實驗，然後居然成功了，如下：

[oracle@rhel6_lhr dpdump]$ sed 's/^ STORAGE(INITIAL.*/STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645/g' lhrsql20150515.sql > lhrsql20150515.sql_bk2

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql_bk2 | grep "STORAGE(INITIAL"

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql_bk2 | grep "STORAGE(INITIAL 131072"

[oracle@rhel6_lhr dpdump]$ more lhrsql20150515.sql | grep "STORAGE(INITIAL 131072"

STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

。。。。。。。。。。。。。

可以看到替換已經成功，如果不熟悉該指令，那麼先熟悉該指令就麻煩了。

二、方法二：sqlldr導入到資料庫，利用update來更新

都是搞資料庫的，這樣做雖然麻煩點，但是絕對不會出錯，而且也是一種技巧，如果有的文本很大又很難處理的話我們就可以導入到資料庫中，然後處理。

首先建表：

create table imp_sql_lhr (id number ,text varchar2(4000)) ;

sqlldr的控制檔案内容:sqlldr_table.ctl ：

UNRECOVERABLE

load data

LENGTH CHARACTER

infile 'lhrsql20150515.sql'

APPEND imp_sql_lhr

trailing nullcols

(

id SEQUENCE(1,1),

text char(4000) "TRIM(:text)"

)

sqlldr指令：

sqlldr lhr/lhr control=sqlldr_table.ctl log=a.log parallel=y readsize=4194304 streamsize=10485760 date_cache=5000 direct=true

導入到資料庫後，我們就可以非常友善的來處理表中的資料了，如下：

執行更新語句：

UPDATE imp_sql_lhr t

SET t.text = 'STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645'

WHERE t.text LIKE '%STORAGE(INITIAL%'

AND t.text NOT LIKE '%STORAGE(INITIAL 65536 %';

最後利用spool來導出到sql文本中：

set echo on

set trimspool on

set trimout on

set linesize 4000

set pagesize 0

set sqlblanklines on

set feedback off

set serveroutput off

set term off

set echo off

spool lhrsql20150515.sql_bk2

SELECT t.text from imp_sql_lhr t order by id;

spool off

1.4.2.3 執行處理好的sql語句

該步驟比較簡單，就是把處理好的ddl語句提前執行一下，讓資料庫中包含相應的對象，這樣再執行impdp導入的時候就不會再建立這些表了。

需要注意的是：DDL語句中建立存過、函數、包的語句中是不包含對象所屬的schema的，這樣的話如果那個使用者執行sql腳本的話，這些對象就建立在那個使用者下了，這個顯然是錯誤的，不是我們期望的，那麼如何處理這個問題呢，想了想，很簡單的嘛，我們sql腳本執行完畢後，再執行一次impdp的指令就可以把這些對象重建，然後把錯誤的存過删除就可以了。

SELECT t.* from imp_sql_lhr t where t.text like 'CREATE PROCEDURE%' order by id;

[oracle@rhel6_lhr dpdump]$ sqlplus lhr/lhr

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 15 16:32:08 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

16:32:09 SQL> set echo off;

16:32:21 SQL> set serveroutput off;

16:32:30 SQL> set timing on;

16:32:40 SQL> set time on;

16:32:45 SQL> set timing off;

16:32:50 SQL> set time off;

SQL>

SQL> @lhrsql20150515.sql_bk2;

Session altered.

。。。。。。。。。。。。。。。。。。。。。。。。。。。省略

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

1.4.2.4 impdp繼續導入

[oracle@rhel6_lhr dpdump]$ impdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log parallel=4;

Import: Release 11.2.0.3.0 - Production on Fri May 15 19:05:29 2015

Master table "LHR"."SYS_IMPORT_FULL_02" successfully loaded/unloaded

Starting "LHR"."SYS_IMPORT_FULL_02": lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=lhrsql20150515_imp.log parallel=4

ORA-31684: Object type USER:"CNY_ETL" already exists

ORA-31684: Object type USER:"LCM2" already exists

ORA-31684: Object type USER:"FXDM_ETL" already exists

ORA-31684: Object type USER:"NRDM_ETL" already exists

ORA-31684: Object type USER:"NRDM" already exists

ORA-31684: Object type USER:"DW_ETL" already exists

ORA-31684: Object type USER:"SOR" already exists

ORA-31684: Object type USER:"DWUSER1" already exists

ORA-31684: Object type USER:"DWII_ETL" already exists

ORA-31684: Object type USER:"FXDM" already exists

ORA-31684: Object type USER:"TEST1" already exists

ORA-31684: Object type USER:"LHR" already exists

ORA-31684: Object type USER:"ONL1" already exists

ORA-31684: Object type USER:"CNYDM" already exists

ORA-31684: Object type USER:"DPA" already exists

ORA-31684: Object type USER:"DWUSER" already exists

ORA-31684: Object type USER:"SQCHECK" already exists

ORA-31684: Object type USER:"TEST" already exists

ORA-31684: Object type DB_LINK:"SOR"."COG_DB" already exists

ORA-31684: Object type DB_LINK:"DPA"."COG_DB" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_BDS_CL_IP_RL_ID" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_GOLD_RATE_INFO" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_MBR_API_ELGBLTY" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_MBR_MKT_ELGBLTY" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_MBR_MMKNG_ROLE" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SEQ_ORG_CD_HSTRY" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SQ_DW_IP_IP_RL" already exists

ORA-31684: Object type SEQUENCE:"SOR"."SQ_MKT_CLNDR_ID" already exists

ORA-31684: Object type SEQUENCE:"FXDM"."SEQ_GOLD_MBR_MMKNG_ROLE_D" already exists

ORA-31684: Object type SEQUENCE:"FXDM"."SEQ_GOLD_MINAMNT_CONFIG" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_BDS_MBR_D_ID" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_DL_MKT_INFO" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_GOLD_RATE_INFO" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_MBR_AUTH_BY_API_D" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_MBR_MMKNG_ROLE_D" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SEQ_ORG_D" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SQ_DW_BEST_QUOTE" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SQ_DW_BOND_MID_QUOTE_ID" already exists

ORA-31684: Object type SEQUENCE:"DPA"."SQ_DW_MEMBER_D_ID" already exists

ORA-39151: Table "DPA"."FX_DL_MKT_INFO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_FWD_CPI_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_FWD_DL_BY_SF_CP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_FWD_QT_BY_DIR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SPOT_CPI_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SPOT_DL_BY_SF_CP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SPOT_QT_BY_DIR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_CPI_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_DL_BY_SF_CP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_DL_LEG_MKT_INFO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."FX_SWAP_QT_BY_DIR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."BOND_TURNOVER_RATE_F" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."MONI_TORT_CRCLTN_AMNT_F" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "DPA"."MONI_TURNOVER_RATE_F" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "SOR"."BST_QT_HIST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "SOR"."BST_QT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

。。。。。。。。。。。。。。。。。。。。。。。。。省略

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_BOND_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_FX_FWD_SWAP_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_CCS_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_CREPO_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_FCO_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_FRA_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_IBO_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_IRS_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."QUE_RIBD_DTL" creation failed

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"SOR"."FX_BM_FXO_DL_DAY_HIST" creation failed

Job "LHR"."SYS_IMPORT_FULL_02" completed with 2165 error(s) at 19:16:19

1.4.2.5 删除錯誤使用者下的包、存過和函數

執行如下的腳本來删除相應的錯誤對象：

FOR CUR IN (SELECT 'DROP ' || D.OBJECT_TYPE || ' ' || D.OBJECT_NAME SQLT

WHERE d.OWNER = 'LHR'

AND d.CREATED >=

to_date('2015-05-15 09:25:27',

'YYYY-MM-DD HH24:MI:SS')

AND D.OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')) LOOP

EXECUTE IMMEDIATE CUR.SQLT;

END LOOP;

END;

執行如下腳本和源庫作比較，檢視資料是否完整。

到此所有的處理算是基本完畢，過程很簡單，但是不同的場景處理方式有很多種，我們應該學會靈活變通，核心即expdp和impdp但是需要做很多的處理。

本文轉自lhrbest 51CTO部落格，原文連結：http://blog.51cto.com/lhrbest/1651994，如需轉載請自行聯系原作者

【資料泵】EXPDP導出表結構(真實案例)

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List