課程目標：

移動資料的方式
建立和使用目錄物件
使用SQL*Loader
使用外部表
Data Pump

1、移動資料：通用架構

DBMS_DATAPUMP：包含API，用於批次資料和後設資料的匯出到匯入

Direct Path API（DPAPI）：使用Direct Path API進行載入和解除安裝資料，最小化資料轉換和解析

DBMS_METADATA：處理後設資料的載入和解除安裝，使用XML儲存資料庫物件的定義

External Table API：使用ORACLE_DATAPUMP和ORACLE_LOADER驅動使用外部表

SQL*Loader：與外部表整合

EXPDP和IMPDP：呼叫DBMS_DATAPUMP進行資料泵操作

Other clients

2、Data Pump：概覽

目前主要使用Data Pump進行資料的匯入匯出，透過呼叫DBMS_DATAPUMP包來進行。Oracle提供了3種工具：

expdp
impdp
EM

支援4種資料移動方法：

資料檔案複製
直接路徑，避開SGA，從磁碟直接讀取資料檔案
外部表
網路連線

可以從多個位置監控Data Pump任務，可以重啟中止的任務。

3、Data Pump：好處

相比exp和imp，Data Pump的好處有：

可以使用EXCLUDE，INCLUDE和CONTENT參加進行細粒度的物件和資料選項

可以使用VERSION引數為匯出的檔案指定資料庫版本以便用於先前版本的資料庫

可以使用PARALLEL引數指定並行度

可以使用ESTIMATE_ONLY引數估計匯出資料到佔用多少空間而不需要實際執行

可以使用Network模式，在分散式環境中透過資料連結匯入匯出

可以使用REMAP，在匯入的時候更改目標資料檔名稱，模式名稱和表空間名稱

可以使用SAMPLE引數指定匯出資料的百分比

可以使用COMPRESSION進行壓縮，節約空間

可以對後設資料，資料，欄位進行加密，指定加密演算法，安全型別等

使用非壓縮的CLOB格式匯出XML型別欄位

可以使用Legacy模式支援exp和imp指令碼

4、Data Pump目錄物件

透過目錄物件指定作業系統目錄，目錄物件屬於SYS使用者，名稱唯一。

由於Data Pump是一個伺服器端的程式，所有需要透過目錄物件指定dump檔案目錄。

透過EM檢視目錄物件。

使用SQL查詢目錄物件。

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/oracletest1/state

SYS DATA_PUMP_DIR /u01/app/oracle/admin/stone/dpdump/

SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

9 rows selected.

5、建立目錄物件

使用EM建立目錄物件。

指定目錄物件的名稱和路徑，路徑需要先建立，可以點選“測試檔案系統”測試路徑是否存在。

使用SQL建立目錄物件。

SQL> create directory test1 as '/home/oracle';

Directory created.

SQL> grant read,write on directory test1 to hr;

Grant succeeded.

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS TEST /home/oracle

SYS SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

SYS TEST1 /home/oracle

SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/oracletest1/state

SYS DATA_PUMP_DIR /u01/app/oracle/admin/stone/dpdump/

SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

11 rows selected.

6、Data Pump匯出和匯入客戶端：概覽

使用expdp匯出資料庫資料到作業系統的dump檔案，再使用impdp匯入dump檔案到目標資料庫。在伺服器端進行，不在客戶端進行。

透過使用Network模式，源資料庫到目標資料庫直接載入。避免了在檔案系統上建立轉儲檔案，可以最大限度地減少匯出和匯入操作的總消耗時間。

使用Master Table（MT）來維護匯出操作，開始匯出操作時建立該表，匯出完成後寫入到dump檔案中，匯入時先載入MT到資料庫當前模式用於按順序建立匯入物件，匯入完成後會刪除MT。

7、Data Pump工具：介面和模式

可以透過以下方式使用Data Pump：

命令列
引數檔案：除了prafile引數不能指定，可以指定其他所有引數
互動命令：透過[Ctrl]+[C]進入
Web介面：使用EM

可以指定不同的模式：

全庫
模式
表
表空間
可傳輸表空間

8、使用EM匯出資料

可以使用EM匯出和匯入資料。

授予hr使用者select_catalog_role和exp_full_database系統許可權，使用hr賬戶登入。

SQL> grant select_catalog_role to hr;

Grant succeeded.

SQL> grant exp_full_database to hr;

Grant succeeded.

SQL> grant write,read on directory test to hr;

Grant succeeded.

SQL> grant execute on DBMS_STATS to hr;

Grant succeeded.

SQL> conn sysman/123456

Connected.

SQL> execute MGMT_USER.MAKE_EM_USER('HR');

PL/SQL procedure successfully completed.

9、Data Pump匯出示例：基本選項

透過“匯出作業中的最大執行緒數”指定並行度，對應命令列parallel引數。應該小於等於dump檔案數。

估算匯出作業將佔用的磁碟空間 (以位元組計)。只針對表中的行資料, 而不包括後設資料。可以透過塊大小乘以塊數量進行進行計算，也可以使用最近的統計資訊。對應命令列estimate引數。

可以選擇生成日誌檔案到目錄物件指定的路徑下，對應命令列logfile引數。

10、Data Pump匯出示例：高階選項

使用高階選項指定匯出內容，對應content引數，include引數和exclude引數。

匯出的時候可以使用閃回查詢匯出以前版本的資料。

可以透過查詢選項指定匯出匯入資料滿足的條件。對應query引數。

11、Data Pump匯出示例：檔案

指定dump檔案的目錄和名稱，對應dumpfile引數。可以指定多個，如果不指定，預設使用expdat.dmp名稱。可以使用%U來產生檔案序列號，%D產生日期。

可以指定最大檔案大小，對應filesize引數，如果指定了%U，則自動生成新檔案，如果沒有指定，則需要手動新增檔案。

預設不會覆蓋同名的檔案，會提示錯誤並停止，可以使用引數reuse_dumpfile=y指定覆蓋。

Oracle提供了一個叫DATA_PUMP_DIR的目錄物件用於匯出。如果設定了ORACLE_BASE，則預設路徑為/admin/DB_UNIQUE_NAME/dpdump，如果沒有設定ORACLE_BASE,設定了ORACLE_HOME，則預設路徑為/admin/DB_UNIQUE_NAME/dpdump。

12、Data Pump匯出示例：排程

建立任務計劃。

13、Data Pump匯出示例：複查

使用命令匯出資料。

[ ~]$ expdp hr/hr tables=hr.emp_exp dumpfile=hr_emp.dmp directory=test logfile=hr_emp.log;

14、Data Pump匯入示例：impdp

使用命令列匯入資料。在另外一個資料庫建立目錄物件並授予許可權

SQL> create directory test as '/home/oracle';

Directory created.

SQL> grant read,write on directory test to hr;

Grant succeeded.

[ ~]$ impdp hr/hr directory=test dumpfile=hr_emp.dmp parallel=1 content=all tables="emp_exp" logfile=hr_emp.log

15、Data Pump匯入示例：轉換

匯入的時候可以進行轉換，包括：

REMAP_DATAFILE：轉換資料檔名稱，跨平臺的時候可以使用

REMAP_TABLESPACE：可以將物件匯入到指定的表空間

REMPA_SCHEMA：可以將物件匯入到指定的模式

REMAP_TABLE：匯入時重新命名錶

REMAP_DATA：匯入時更改資料，一般用於重新生成主鍵

[ ~]$ impdp hr/hr directory=test dumpfile=hr_emp.dmp parallel=1 content=all tables="emp_exp" logfile=hr_emp.log remap_table=emp_exp:empexp

16、使用EM監控Data Pump任務

可以使用EM監控所有Data Pump任務，可以執行、停止和暫停任務。

17、使用Data Pump傳統模式遷移資料

允許在Data Pump中使用之前的exp和imp指令碼。會把出現在expdp/impdp命令中的exp/imp引數，自動轉換為expdp/impdp所支援的引數上。

18、Data Pump傳統模式

exp匯出的檔案只能由imp匯入。expdp匯出的檔案只能由impdp匯入。當在expdp/impdp命令中有exp/imp引數時，進入到傳統模式。規則如下：

相同引數用法不改變
相似的引數結果有可能不一樣
替代的引數被忽略
引數混用會失敗

19、Data Pump傳統模式

傳統模式下：

一些引數會做對映，功能一樣
引數對映後功能有變化
Data Pump沒有的引數會報錯，volsize是exp指定磁帶卷大小

20、管理檔案位置

由於exp/imp是基於客戶端的，所有需要指定完整的路徑名稱，而Data Pump是基於伺服器段的，使用目錄物件指定檔案位置，預設的目錄物件是DATA_PUMP_DIR。

可以為某個使用者建立專門的目錄物件DATA_PUMP_DIR_schema-name，並授權，當使用者沒有EXP_FULL_DATABASE許可權，沒有使用directory引數指定目錄物件時，就是用這個目錄物件。

21、SQL*Loader：概覽

使用SQL*Loader載入外部檔案到表。

會使用到下面的檔案：

Input data files（輸入資料檔案）：源資料所在檔案
Control file（控制檔案）：文字檔案，指定輸入資料檔案的位置以及解析方式等，分三部分：
- 包括會話級別的資訊，包括全域性選項，記錄的格式，載入的資料等
- 表資訊，表的名字和欄位
- 可選部分，包含輸入資料
Log file（日誌檔案）：儲存日誌記錄資訊
Bad file（問題記錄檔案）：儲存包含錯誤的記錄
Discard file（丟棄檔案）：儲存被拒絕和無法插入的記錄

22、使用SQL*Loader載入資料

在EM使用使用SQL*Loader載入資料

[ ~]$ cat sqlload.dat

Ellen Abel

Sundar Ande

Mozhe Atkinson

David Austin

Hermann Baer

Shelli Baida

Amit Banda

Elizabeth Bates

Sarah Bell

David Bernstein

SQL> create table empldr as select first_name,last_name from employees where 1=0;

Table created.

SQL> select * from empldr;

FIRST_NAME LAST_NAME

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

Ellen Abel

Sundar Ande

Mozhe Atkinson

David Austin

Hermann Baer

Shelli Baida

Amit Banda

Elizabeth Bates

Sarah Bell

David Bernstein

10 rows selected.

[ ~]$ cat sqlload.CTL

LOAD DATA

APPEND

INTO TABLE HR.EMPLDR

(

FIRST_NAME POSITION(1:20) CHAR,

LAST_NAME POSITION(21:45) CHAR

)

[ ~]$ sqlldr control='/home/oracle/sqlload.CTL'

Username:hr

Password:

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Dec 30 17:41:10 2015

Commit point reached - logical record count 10

23、SQL*Loader控制檔案

使用控制檔案：

指定其他檔案位置
指定資料格式
配置：記憶體管理，記錄的拒絕規則
如何操作載入的資料

控制檔案例子：

1 -- This is a sample control file

2LOAD DATA

3INFILE ’SAMPLE.DAT’

4BADFILE ’sample.bad’

5DISCARDFILE ’sample.dsc’

6APPEND

7INTO TABLE emp

8WHEN (57) = ’.’

9TRAILING NULLCOLS

10(hiredate SYSDATE,

deptno POSITION(1:2) INTEGER EXTERNAL(3)

NULLIF deptno=BLANKS,

job POSITION(7:14) CHAR TERMINATED BY WHITESPACE

NULLIF job=BLANKS "UPPER(:job)",

mgr POSITION(28:31) INTEGER EXTERNAL

TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,

ename POSITION(34:41) CHAR

TERMINATED BY WHITESPACE "UPPER(:ename)",

empno POSITION(45) INTEGER EXTERNAL

TERMINATED BY WHITESPACE,

sal POSITION(51) CHAR TERMINATED BY WHITESPACE

"TO_NUMBER(:sal,’$99,999.99’)",

comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’

":comm * 100"

)

（1）第一行為註釋，可以位於任何地方，“--”開頭。

（2）LOAD DATA表示這是一個新資料載入的開始，如果要繼續之前的載入，可以使用CONTINUE LOAD DATA

（3）INFILE指定資料所在檔名稱

（4）BADFILE指定問題記錄儲存檔案

（5）DISCARDFILE指定丟棄記錄儲存檔案

（6）APPEND用於載入資料到一個已經有資料的表，如果是空表，使用INSERT關鍵字

（7）INTO TABLE指定匯入資料到哪個表

（8）WHEN語句指定每條記錄需要滿足的域值條件，WHEN (57) = '.'表示記錄的第57個字元為'.'

（9）TRAILING NULLCOLS語句不存在的欄位為NULL

（10）剩下的是欄位列表和格式

24、載入方法

常規匯入透過使用 INSERT語句來匯入資料。Direct匯入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接將資料匯入到資料檔案中，可以提高匯入資料的效能。

傳統路徑（conventional path）：SQLLDR 會利用SQL插入載入資料。

直接路徑（direct path）：採用這種模式，SQLLDR不使用SQL；而是直接格式化資料庫塊，而繞過整個SQL引擎和UNDO生成，同時還可能避開REDO生成。要在一個沒有任何資料的庫中充分載入資料，最快的方法就是採用並行直接路徑載入。總是在表的最高水位之上插入資料，這種方式消除了用於搜尋部分填充塊的時間。

25、外部表

外部表是存在在作業系統上的檔案，資料庫透過訪問驅動可以直接讀取，但是外部表只讀，不支援DML和建立索引。

有2種訪問驅動：

ORACLE_LOADER：只能用於讀取外部表和載入到資料庫，使用文字檔案作為資料來源。
ORACLE_DATAPUMP：既可以載入資料到資料庫，也可以匯出資料到外部檔案，使用二進位制檔案，與impdp和expdp格式一樣。

26、外部表好處

可以直接讀取外部表資料，無需載入到資料庫，也可以使用ORACLE_DATAPUMP訪問驅動將複雜查詢的結果匯出到外部檔案。

27、使用ORACLE_LOADER定義外部表

使用SQL語言建立外部表後設資料，ORACLE_LOADER訪問驅動使用SQL*Loader語法定義外部表，不會建立外部檔案。

如果資料量很大，可以啟用並行DML：

SQL> alter session enable parallel dml;

Session altered.

28、使用ORACLE_DATAPUMP進行外部表操作

使用外部表匯出多表連線查詢結果到檔案。可以指定並行度，數量不超過檔案數量。匯出的檔案可以使用impdp匯入。

SQL> create table ext_emp_query_results1(first_name,last_name,department_name)

2 organization external(

3 type oracle_datapump

4 default directory test

5 location('emp11.exp','emp12.exp','emp13.exp'))

6 PARALLEL 3

7 as

8 select e.first_name,e.last_name,d.department_name

9 from employees e,departments d

10 where e.department_id=d.department_id

11 and d.department_name in ('Marketing','Purchasing');

Table created.

[ ~]$ ls emp*.exp

emp11.exp emp12.exp emp13.exp

29、使用外部表

30、資料字典

[DBA|ALL|USER]_EXTERNAL_TABLES: 外部表屬性

[DBA|ALL|USER]_EXTERNAL_LOCATIONS: 外部表位置

[DBA|ALL|USER]_TABLES: 表資訊

[DBA|ALL|USER]_TAB_COLUMNS: 表列資訊

[DBA|ALL]_DIRECTORIES: 目錄物件資訊

SQL> select table_name,TYPE_NAME,DEFAULT_DIRECTORY_NAME from user_external_tables;

TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_NAME

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

EXT_EMP_QUERY_RESULTS1 ORACLE_DATAPUMP TEST

SQL> select * from user_external_locations;

TABLE_NAME LOCATION DIR DIRECTORY_NAME

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

EXT_EMP_QUERY_RESULTS1 emp11.exp SYS TEST

EXT_EMP_QUERY_RESULTS1 emp12.exp SYS TEST

EXT_EMP_QUERY_RESULTS1 emp13.exp SYS TEST

31、相關習題：

（1）You want to move all objects of the APPS user in the test database to the DB_USR schema of the production database.
Which option of IMPDP would you use to accomplish this task?
A. FULL
B. SCHEMAS
C. TRANSFORM
D. REMAP_SCHEMA
E. REMAP_TABLESPACE

答案：D

（2）You plan to move data from a flat file to a table in your database. You decide to use SQL*Loader direct path load method to perform this task. The table in which you plan to load data in an important table having various integrity constraint defined on it.
Which constraints will remain enabled by default during this operation? (Choose all that apply.)
A. CHECK
B. UNIQUE
C. NOT NULL
D. PRIMARY KEY
E. FOREIGN KEY

答案：BCD

（3）Which two statements are true regarding the usage of the SQL*LOADER utility? (Choose two.)
A. You can load data into multiple tables during the same load session
B. You can load data from multiple files to a table during the same load session
C. You cannot perform selective data loading based on the values available in the records
D. You can use an export file generated by the EXPDP utility as an input data file to load the data
E. You can load data only if the input file is available on the disk and tape but not a named pipe

答案：AB

（4）Which two operations can be performed on an external table? (Choose two.)
A. Create a view on the table
B. Create an index on the table
C. Create a synonym on the table
D. Add a virtual column to the table
E. Update the table using the UPDATE statement
F. Delete rows in the table using the DELETE command

答案：AC

（5）Note the following points describing various utilities in Oracle Database 11g:
Which point describes the Oracle Data Pump utility?

A．1
B. 2
C. 3
D. 4
E. 1 and 3
F. 1, 2, 3 and 4

答案：A

（6）Which statement is true about loading data using the conventional path of SQL*Loader?
A. Redo is not generated while performing conventional path loads
B. Only PRIMARY KEY, UNIQUE KEY and NOT NULL constraints are checked
C. No exclusive locks are acquired when the conventional path loads are performed
D. Instead of performing transactions, SQL*Loader directly writes data blocks to the data files
E. INSERT triggers are disabled before the conventional path load and reenabled at the end of the load

答案：C

（7）database, DEVDB, to the production database, PRODDB. A database link devdb.us.oracle.com is created between PRODDB and DEVDB. You execute the following command on the PRODDB database server:
   $ impdp system/manager directory = DB_DATA
               dumpfile = schemas.dat
               schemas = hr
               flashback_time = "TO_TIMESTAMP('25-08-2007 14:35:00',
'DD-MM-YYYY HH24:MI:SS')"
The command fails displaying the following error:
   ORA-39001: invalid argument value
   ORA-39000: bad dump file specification
   ORA-31640: unable to open dump file "/home/oracle/schema/schemas.dat" for read
   ORA-27037: unable to obtain file status
What would you do to overcome the error?
A. Add the user, SYSTEM, to the schemas option.
B. Add the network_link = devdb.us.oracle.com option.
C. Change the dumpfile option to schema.dat@devdb.us.oracle.com.
D. Replace the schemas option with the network_link = devdb.us.oracle.com
E. Replace the dumpfile option with the network_link = devdb.us.oracle.com

答案：E

（8）The TRANS_SUMMARY table contains product-wise transaction details that get updated with every transaction in the system. Each row has cumulative transaction details of a single product and every product is identified by a product code, which is the primary key.
As part of the archival process, the company wants to transfer the rows in the TRANS_SUMMARY table to the TRANS_SUMMARY_DUP table at the end of every quarter of the year. Along with existing products, the company deals with many new products during every quarter.
Which method is best suited for this quarterly data transfer?
A. Using the MERGE command
B. Using the SQL*Loader utility
C. Using the correlated UPDATE command
D. Using the INSERT command to perform bulk operation

答案：A

（9）You have a large amount of historical data in a flat file. Some analysts in your organization need to query this data in the file. The file is too large to load the data in it into your current database.
Which is the most effective method to access this data in your database?
A. Use the database link.
B. Use the SQL*Loader utility.
C. Use the Oracle Data Pump utility.
D. Create an external table and leave the data in the flat file.

答案：D

（10）You are using flat files as the data source for one of your data warehousing applications. To optimize the application performance, you plan to move the data from the flat files to clustered tables in an Oracle database. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation.
Which method would you use to load data into the Oracle database?
A. Use the external table population.
B. Use the Oracle Data Pump export and import utility.
C. Use the conventional path data load of the SQL*Loader utility.
D. Use the INSERT INTO...SELECT command to load the data.

答案：Ｃ

（11）Which two statements are true regarding the Oracle Data Pump export and import operations? (Choose two.)
A. You cannot export data from a remote database.
B. You can rename tables during an import operation.
C. You can overwrite existing dump files during an export operation.
D. You can compress the data during export but not the metadata because it is not supported.

答案：BC

OCP課程39：管理Ⅰ之移動資料

相關文章