【資料泵】EXPDP匯出表結構(真實案例)
【資料泵】EXPDP匯出表結構(真實案例)
BLOG文件結構圖
因工作需要現需要把一個生產庫下的後設資料（表定義，索引定義，函式定義，包定義，儲存過程）匯出到測試庫上，本來以為很簡單的，可是做的過程發現很多的問題，現記錄如下，希望有同樣需要的朋友不要再走彎路了。
一.1 導讀
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① EXPDP和IMPDP如何匯出匯入後設資料，包括表定義，索引定義，函式定義，包定義，儲存過程（重點）
② 表的初始化定義引數initial，及如何批量修改該引數
③ 如何匯出DMP檔案中的DDL語句（重點）
④ 10g和11g預設情況下有哪些使用者及其作用
⑤ linux中的批量替換sed命令
⑥ sqlldr和spool命令
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
一.2 實驗環境介紹
源庫：10.2.0.1 AIX
目標庫：11.2.0.3 RHEL6.5
一.3 執行匯出工作
一.3.1 確定需要匯出的使用者
oracle安裝好後有很多的系統預設使用者，比如sys和system，對於這2個使用者裡的後設資料我們就沒有必要再重新匯出嘛，不然匯入的時候還提示錯誤，看著實在不好。
官網資訊：
All databases created by the Database Configuration Assistant (DBCA) include the SYS, SYSTEM, SYSMAN, and DBSNMP 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. |
|
|
APEX_030200 |
The account owns the Application Express schema and metadata. |
|
|
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 |
|
BI |
The account that owns the Business Intelligence schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas. |
|
|
CTXSYS |
The Oracle Text account. |
|
|
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. |
None |
|
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. |
|
|
|
||
|
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. |
None |
|
FLOWS_FILES |
The account owns the Application Express uploaded files. |
|
|
HR |
The account that owns the Human Resources schema included in the Oracle Sample Schemas. It is available only if you loaded the sample schemas. |
|
|
IX |
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. |
|
|
MDDATA |
The schema used by Oracle Spatial for storing geocoder and router data. |
|
|
MDSYS |
The Oracle Spatial and Oracle Multimedia Locator administrator account. |
|
|
MGMT_VIEW |
An account used by Oracle Enterprise Manager Database Control. |
None |
|
OE |
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. |
|
|
ORDSYS |
The Oracle Multimedia administrator account. |
|
|
ORDDATA |
This account contains the Oracle Multimedia DICOM data model. |
|
|
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. |
|
|
ORACLE_OCM |
This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager. |
Oracle Configuration Manager Installation and Administration Guide |
|
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. |
Oracle Warehouse Builder Installation and Administration Guide |
|
OWBSYS_AUDIT |
This account is used by the Warehouse Builder Control Center Agent to access the heterogeneous execution audit tables in the OWBSYS schema. |
Oracle Warehouse Builder Installation and Administration Guide |
|
PM |
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. |
|
|
SH |
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. |
Oracle Enterprise Manager Grid Control Installation and Basic Configuration |
|
SYSTEM |
Another account used to perform database administration tasks. |
|
|
WMSYS |
The account used to store the metadata information for Oracle Workspace Manager. |
|
|
XDB |
The account used for storing Oracle XML DB data and metadata. |
10g下比較少：
|
CTXSYS |
CTXSYS |
The Oracle Text account |
|
|
DBSNMP |
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 |
|
LBACSYS |
LBACSYS |
The Oracle Label Security administrator account |
|
|
MDDATA |
MDDATA |
The schema used by Oracle Spatial for storing Geocoder and router data |
|
|
MDSYS |
MDSYS |
The Oracle Spatial and Oracle interMedia Locator administrator account |
|
|
DMSYS |
DMSYS |
The Oracle Data Mining account. |
|
|
|
|||
|
OLAPSYS |
MANAGER |
The account used to create OLAP metadata structures. It owns the OLAP Catalog (CWMLite). |
|
|
ORDPLUGINS |
ORDPLUGINS |
The Oracle interMedia user. Plug-ins supplied by Oracle and third party format plug-ins are installed in this schema. |
|
|
ORDSYS |
ORDSYS |
The Oracle interMedia administrator account |
|
|
OUTLN |
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. |
|
|
SI_INFORMTN_SCHEMA |
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 |
|
|
SYSMAN |
CHANGE_ON_INSTALL |
The account used to perform Oracle Enterprise Manager database administration tasks. Note that SYS and SYSTEM can also perform these tasks. |
Oracle Enterprise Manager Grid Control Installation and Basic Configuration |
|
SYSTEM |
MANAGER |
Another account used to perform database administration tasks. |
一.3.2 確定需要匯出的使用者在哪些表空間，及其表初始化時佔用的表空間大小
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
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'))
;
由此可以知道，建立這些後設資料大約需要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
一.3.3 確定需要匯出的使用者中有哪些無效的物件、及總共需要匯出的物件數量
這一步也很重要，決定著最終匯出結果的正確性驗證。
SELECT d.OWNER,count(1)
FROM dba_objects 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.OWNER;
SELECT d.OWNER,
d.status,count(1)
FROM dba_objects 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.OWNER, d.status;
SELECT d.OWNER,
d.OBJECT_NAME,
d.OBJECT_TYPE,
d.status
FROM dba_objects d
WHERE d.status = 'INVALID'
and 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'));
一.3.4 expdp資料泵利用content=metadata_only匯出後設資料
匯出命令，注意這裡不匯出資料只匯出定義我們採用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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
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傳遞到目標端，我這裡就直接傳到我的虛擬機器上吧。
一.4 執行匯入工作
一.4.1 先建立相應的表空間
根據上邊的工作我們知道需要在測試庫上建立以下的表空間，我這裡都設定的是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;
一.4.2 執行匯入語句
一般情況下，如果儲存夠的話，我們把相應的表空間設定大一點之後這裡直接執行匯入語句就可以了，但是我是在本機的虛擬機器裡執行的，由前邊的情況我們可以知道大約需要49G的空間，這個顯然不太合適，哪該怎麼辦呢？我能想到的辦法只有如下2種，如果大家還有好的辦法可以給我留言。
① 在源庫上修改表的定義後然後再執行匯出命令
② 從已經匯出來的dmp檔案中抽取其中的DDL語句，然後將DDL語句匯入到資料庫中，update掉其中的STORAGE(INITIAL 引數後再將語句匯出到sql文字中執行sql語句，這樣可以解決表的定義問題。
③ 從已經匯出來的dmp檔案中抽取其中的DDL語句，然後利用linux的sed批量替換功能替換掉不正確的引數。
顯然，第一種比較方便，也比較快，但是不實用，由於是生產庫，引數不能隨便修改，我們就採用第二或第三種辦法，也可以多演示一種impdp的用法。
一.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
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, 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
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
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"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "FXDM_ETL" IDENTIFIED BY VALUES 'EA010AEA839BFA14'
DEFAULT TABLESPACE "DW_USER"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "NRDM_ETL" IDENTIFIED BY VALUES '54A4A046AEE8B31E'
DEFAULT TABLESPACE "DW_USER"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "NRDM" IDENTIFIED BY VALUES '1AE3DF7368DF560D'
DEFAULT TABLESPACE "SD_CNY_F_01"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "DW_ETL" IDENTIFIED BY VALUES '91635F9C0744E7EC'
DEFAULT TABLESPACE "DW_USER"
TEMPORARY TABLESPACE "TEMP";
CREATE USER "SOR" IDENTIFIED BY VALUES 'BA3A6C912E6BFF14'
DEFAULT TABLESPACE "DWII_SOR_F_01"
TEMPORARY TABLESPACE "TEMP";
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。省略
[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';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
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';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
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';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
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';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
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';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
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';
EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
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;
DBMS_STATS.IMPORT_TABLE_STATS('"DPA"','"BNCHMK_OPTN_DLT_PARAM_F"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');
DELETE FROM "SYS"."IMPDP_STATS";
END;
/
[oracle@rhel6_lhr dpdump]$
可以看到生成的SQL語句開始部分是ddl的建使用者語句，最後是匯入表的統計資訊部分。
一.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
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
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
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
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
匯入到資料庫後，我們就可以非常方便的來處理表中的資料了，如下：
SELECT * FROM imp_sql_lhr a where a.text like '%STORAGE(INITIAL%' and a.text not like '%STORAGE(INITIAL 65536%' ;
執行更新語句：
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
一.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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
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>
SQL>
SQL> @lhrsql20150515.sql_bk2;
Session altered.
Session altered.
Session altered.
。。。。。。。。。。。。。。。。。。。。。。。。。。。 省略
SQL> exit
Disconnected from 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
一.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
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
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
Processing object type SCHEMA_EXPORT/USER
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
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
ORA-31684: Object type DB_LINK:"SOR"."COG_DB" already exists
ORA-31684: Object type DB_LINK:"DPA"."COG_DB" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
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
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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
[oracle@rhel6_lhr dpdump]$
[oracle@rhel6_lhr dpdump]$
[oracle@rhel6_lhr dpdump]$
一.4.2.5 刪除錯誤使用者下的包、存過和函式
執行如下的指令碼來刪除相應的錯誤物件：
BEGIN
FOR CUR IN (SELECT 'DROP ' || D.OBJECT_TYPE || ' ' || D.OBJECT_NAME SQLT
FROM dba_objects d
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;
一.5 資料校驗
執行如下指令碼和源庫作比較，檢視資料是否完整。
SELECT d.OWNER,count(1)
FROM dba_objects 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.OWNER;
SELECT d.OWNER,
d.status,count(1)
FROM dba_objects 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.OWNER, d.status;
SELECT d.OWNER,
d.OBJECT_NAME,
d.OBJECT_TYPE,
d.status
FROM dba_objects d
WHERE d.status = 'INVALID'
and 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'));
一.6 總結
到此所有的處理算是基本完畢，過程很簡單，但是不同的場景處理方式有很多種，我們應該學會靈活變通，核心即expdp和impdp但是需要做很多的處理。
一.7 aboout me
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1657828/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取碼：af2d
QQ：642808185 若加QQ請註明你所正在讀的文章標題
創作時間地點：2015-05-15 10:00~ 2015-05-16 19:00 於唐鎮金唐公寓宿舍
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21754115/viewspace-1692636/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【impdp】IMPDP中的TRANSFORM引數--【資料泵】EXPDP匯出表結構(真實案例)後傳ORM
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- Oracle expdp資料泵遠端匯出Oracle
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 使用資料泵工具expdp工具匯出資料
- 資料泵匯出匯入表
- Oracle10g 資料泵匯出命令 expdp 使用總結Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- Oracle使用資料泵匯出匯入表Oracle
- shell，ant指令碼實現自動資料泵(exp.expdp)匯出匯入資料指令碼
- expdp匯出表的部分資料
- Mysql匯出表結構、表資料MySql
- 資料泵 EXPDP 匯出工具的使用
- DB2資料庫匯出表結構與匯入、匯出表資料DB2資料庫
- 資料泵匯出匯入
- 資料泵避免個別表資料的匯出
- DB2匯出表結構、表資料小結DB2
- 資料泵的匯入匯出
- 資料泵避免個別表資料的匯出（二）
- mysql mysqldump只匯出表結構或只匯出資料的實現方法MySql
- Mysql匯出表結構及表資料 mysqldump用法MySql
- 利用資料泵匯出查詢結果（二）
- 利用資料泵匯出查詢結果（一）
- Oracle資料泵－schema匯入匯出Oracle
- rhel5 mysqldump使用_匯出表結構_資料_結構及資料MySql
- expdp遠端匯出資料
- Oracle expdp匯出多表或表中的部分資料Oracle
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- 資料泵匯出匯入資料標準文件
- Oracle使用資料泵在異機之間匯出匯入表Oracle
- oracle10g expdp資料泵的bug,按schema匯出，匯入impdp時無jobOracle
- python實現oracle資料泵匯出功能PythonOracle
- Oracle資料泵的匯入和匯出Oracle
- 資料泵取匯出和匯入（一）
- 針對資料泵匯出 (expdp) 和匯入 (impdp)工具效能降低問題的檢查表
- expdp 使用QUERY 匯出部分資料。
- 限定filesize的資料泵匯入匯出操作案例
- 細緻入微：如何使用資料泵匯出表的部分列資料