Data pump export/import(hereinafter referred to as Export/Import for ease of reading)是一種將元數據和數據導出到系統文件集/從系統文件集導入數據的服務端工具;導出的文件可以移動到其它服務器上,但只能使用impdp導入;使用前要先建立目錄(directory)和授權:
conn / as sysdba
CREATE DIRECTORY PUMP_DIR AS '/u01/backup';
創建後即可查詢出目錄信息,包括目錄名稱、所有者、目錄位置:
SQL> column directory_path format a50
SQL>select * from dba_directories where directory_name='PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS PUMP_DIR /u01/backup
刪除已有的目錄
drop directory pump_dir;
向某些用戶授權使用目錄,可以授權read或write權限,或者兩個權限同時授予:
grant read,write on directory pump_dir to frdc;
撤銷權限
revoke read,write on directory pump_dir from frdc;
EXPDP USERID='sywu/sywu' job_name=export_tb parallel=3 tables=(tb01,tb02,tb03) dumpfile=pump_dir:dw_20150602.dmp logfile=pump_dir:exptb.log version='11.2.0.1.0' exclude=''
userid 表示數據庫連接信息,可以是as sysdba權限,非sysdba權限用戶可以省略;
job_name 表示導出任務名,未指定默認格式為: SYS_EXPORT_TABLE _*;
parallel 表示並行數,默認為1;
tables 表示導出的表名,可以指定所有者owner.tab,導出分區表的某個分區owner.tab:part01;
dumpfile 表示導出的dump文件名,格式目錄名:dump文件名,注:某些版本可能不兼容報錯(ORA-39145),可以把目錄獨立出來用directory參數指定(directory=pump_dir);
version 表示導入目的地數據庫版本,在非同一版本數據庫之間導出導入數據這個比較重要;
exclude 表示導入排除的對象,DATABASE_EXPORT_OBJECTS表中記錄所有的(數據庫級別)排除模式,SCHEMA_EXPORT_OBJECTS表中記錄schema級別排除對象模式,TABLE_EXPORT_OBJECTS表中記錄table級別排除對象模式;
有時候只想導出特定的數據,比如id=10或id in(10,20,30),這種情況下可以用條件限定導出數據(注意字符轉義)
expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:\"where object_id in\(10,20,30\)\" dumpfile=query_tab.dump directory=pump_dir
Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:09:24 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, OLAP, Data Mining and Real Application Testing options
Starting "OPS$SYWU"."SYS_EXPORT_TABLE_01": userid=ops$sywu/******** tables=tb01 query=tb01:"where object_id in(10,20,30)" dumpfile=query_tab.dump directory=pump_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 88 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "OPS$SYWU"."TB01" 25.70 KB 192 rows
Master table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is:
/u01/backup/query_tab.dump
Job "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:09:32
或者通過rownum限定行的方式導出數據(注意字符轉義)
expdp userid='ops$sywu/sywu' tables=tb01 query=tb01:\"where rownum\<10\" dumpfile=query_tab.dump directory=pump_dir
Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:14:28 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, OLAP, Data Mining and Real Application Testing options
Starting "OPS$SYWU"."SYS_EXPORT_TABLE_01": userid=ops$sywu/******** tables=tb01 query=tb01:"where rownum<10" dumpfile=query_tab.dump directory=pump_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 88 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "OPS$SYWU"."TB01" 11.41 KB 9 rows
Master table "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$SYWU.SYS_EXPORT_TABLE_01 is:
/u01/backup/query_tab.dump
Job "OPS$SYWU"."SYS_EXPORT_TABLE_01" successfully completed at 17:14:36
有些情況下,為了滿足測試要求,我們可能需要整庫導出或整個schema下的數據導出,但又不需要所有數據,so 同樣可以使用限定行數的方式限定所有表數據行導出數據
expdp userid='ops$sywu/sywu' SCHEMAS='ops$sywu' query=\"where rownum\<10\" dumpfile=schema_sywu.dump directory=pump_dir
Export: Release 11.2.0.3.0 - Production on Tue Jun 2 17:18:34 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, OLAP, Data Mining and Real Application Testing options
Starting "OPS$SYWU"."SYS_EXPORT_SCHEMA_01": userid=ops$sywu/******** SCHEMAS=ops$sywu query="where rownum<10" dumpfile=schema_sywu.dump directory=pump_dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 104 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "OPS$SYWU"."TB01" 11.41 KB 9 rows
. . exported "OPS$SYWU"."TB02" 6.015 KB 9 rows
. . exported "OPS$SYWU"."T_EXCE" 5.820 KB 2 rows
Master table "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$SYWU.SYS_EXPORT_SCHEMA_01 is:
/u01/backup/schema_sywu.dump
Job "OPS$SYWU"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:18:55
有些情況下,導出的數據需要通過網絡或通過媒介傳輸到其它目的地,考慮媒介的大小和網絡情況,每次只能限定傳輸,為了達到這個目的可以將數據導出到多個文件,然後再分批或獨立發送;比如評估的數據大小是80M,我想把文件導出為4個文件,每個文件的大小為20M,so 我像這樣導出數據:
expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log
Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:03: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, OLAP, Data Mining and Real Application Testing options
Starting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=20971520 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 104 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "OPS$SYWU"."TB01" 74.64 MB 844416 rows
. . exported "OPS$SYWU"."TB02" 452.3 KB 13448 rows
. . exported "OPS$SYWU"."T_EXCE" 5.820 KB 2 rows
Master table "OPS$SYWU"."EXPORTTB_BYSIZE" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$SYWU.EXPORTTB_BYSIZE is:
/u01/backup/size_each_sywu.dump
/u01/backup/file02.dmp
/u01/backup/file03.dmp
/u01/backup/file04.dmp
Job "OPS$SYWU"."EXPORTTB_BYSIZE" successfully completed at 18:03:24
filesize 表示每個文件的大小,單位為:bytes
dumpfile 表示導出的4個dump文件名,如果實際導出數據大小大於指定的文件數據(dumpfile)乘以每個文件大小(filesize)之合,則導出停止並報錯
ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes
Job "OPS$SYWU"."SYS_EXPORT_SCHEMA_07" stopped due to fatal error at 17:57:31
有個地方要注意:在導出數據時,數據庫會在導數用戶下根據job_name名稱創建一張表,比如job_name=exporttb_bysize,在導數過程中數據庫創建一張名EXPORTTB_BYSIZE的表,該表記錄了導數的信息,導數成功完成後自動刪除,如果導數未成功,比如空間不足或實際大小大於指定大小,則job停止,該表不會自動刪除。
為了說明問題,我將filesize改為每個文件10m模擬錯誤發生,最後觀察狀態,然後解決錯誤;
expdp userid='ops$sywu/sywu' job_name=exporttb_bysize SCHEMAS='ops$sywu' filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log
Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:21:07 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, OLAP, Data Mining and Real Application Testing options
Starting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 104 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes
Job "OPS$SYWU"."EXPORTTB_BYSIZE" stopped due to fatal error at 18:21:26
指定文件大小乘以文件個數小於實際數據量大小,導出報錯停止,僅僅是停止;
ls -lh /u01/backup
-rw-r--r--. 1 oracle asmadmin 1.8K Jun 2 17:18 export.log
-rw-r--r--. 1 oracle asmadmin 1.5K Jun 2 18:21 exportt01.log
-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 file02.dmp
-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 file03.dmp
-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 file04.dmp
-rw-r-----. 1 oracle asmadmin 10M Jun 2 18:21 size_each_sywu.dump
此時查看數據庫,發現一張於job_name名稱相同的表EXPORTTB_BYSIZE,表中記錄了導數時的信息;查詢job的狀態為:NOT RUNING;
select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EXPORTTB_BYSIZE TABLE
TB01 TABLE
TB02 TABLE
T_EXCE TABLE
select job_name,state from dba_datapump_jobs;
JOB_NAME STATE
------------------------------ ------------------------------
EXPORTTB_BYSIZE NOT RUNNING
繼續完成導數可以通過添加dump文件或使用系統默認的文件
expdp userid='ops$sywu/sywu' attach=EXPORTTB_BYSIZE
Export: Release 11.2.0.3.0 - Production on Tue Jun 2 22:28:37 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, OLAP, Data Mining and Real Application Testing options
Job: EXPORTTB_BYSIZE
Owner: OPS$SYWU
Operation: EXPORT
Creator Privs: FALSE
GUID: 178798A215814641E053FE1813ACD41C
Start Time: Tuesday, 02 June, 2015 22:28:38
Mode: SCHEMA
Instance: sydb
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log
State: IDLING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/backup/size_each_sywu.dump
size: 10,485,760
bytes written: 10,485,760
Dump File: /u01/backup/file02.dmp
size: 10,485,760
bytes written: 10,485,760
Dump File: /u01/backup/file03.dmp
size: 10,485,760
bytes written: 10,485,760
Dump File: /u01/backup/file04.dmp
size: 10,485,760
bytes written: 10,485,760
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Object Schema: OPS$SYWU
Object Name: TB01
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 3
Completed Bytes: 41,844,736
Percent Done: 45
Worker Parallelism: 1
Export> add_file=file05.dmp
Export> start_job
開始任務後,導數繼續執行
select job_name,state from dba_datapump_jobs;
JOB_NAME STATE
------------------------------ ------------------------------
EXPORTTB_BYSIZE EXECUTING
select job_name,state from dba_datapump_jobs;
JOB_NAME STATE
------------------------------ ------------------------------
EXPORTTB_BYSIZE COMPLETING
在添加dump file(add_file)時,添加的文件最終大小取決於添加了多少dump file,在上例中僅添加了一個file05.dmp,so 剩余的數據量將全部歸納到該文件中;如果未添加文件oracle會自己分配和創建一個dump文件;
[oracle@sywu backup]$ ls -ltrh
-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 file02.dmp
-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 file03.dmp
-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 file04.dmp
-rw-r-----. 1 oracle asmadmin 10M Jun 2 22:30 size_each_sywu.dump
-rw-r-----. 1 oracle asmadmin 36M Jun 2 22:30 file05.dmp
-rw-r--r--. 1 oracle asmadmin 2.4K Jun 2 22:30 exportt01.log
[oracle@sywu backup]$ cat exportt01.log
Export: Release 11.2.0.3.0 - Production on Tue Jun 2 18:42:17 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, OLAP, Data Mining and Real Application Testing options
Starting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 104 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes
Job "OPS$SYWU"."EXPORTTB_BYSIZE" stopped due to fatal error at 18:42:36
Job EXPORTTB_BYSIZE has been reopened at Tuesday, 02 June, 2015 22:28
Restarting "OPS$SYWU"."EXPORTTB_BYSIZE": userid=ops$sywu/******** job_name=exporttb_bysize SCHEMAS=ops$sywu filesize=10485760 dumpfile=size_each_sywu.dump,file02.dmp,file03.dmp,file04.dmp directory=pump_dir logfile=exportt01.log
. . exported "OPS$SYWU"."TB01" 74.64 MB 844416 rows
. . exported "OPS$SYWU"."TB02" 452.3 KB 13448 rows
. . exported "OPS$SYWU"."T_EXCE" 5.820 KB 2 rows
Master table "OPS$SYWU"."EXPORTTB_BYSIZE" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$SYWU.EXPORTTB_BYSIZE is:
/u01/backup/size_each_sywu.dump
/u01/backup/file02.dmp
/u01/backup/file03.dmp
/u01/backup/file04.dmp
/u01/backup/file05.dmp
Job "OPS$SYWU"."EXPORTTB_BYSIZE" completed with 1 error(s) at 22:30:38
對於導出schema下的數據,建議使用sysdba用戶導出,因為隨著系統的復雜和環境不一,某些時候依賴的包、對象、存儲過程可能是屬於另一個用戶的,導出時被導出schema未必具有完全的權限,同時還要考慮導入的目的地環境;當然萬事沒有絕對,完全取決於應用;
expdp userid='sys/oracle as sysdba' job_name=export_schema SCHEMAS='ops$sywu' directory=pump_dir dumpfile=schema_sywu.dmp logfile=schema_sywu.log
整庫導出必須使用sysdba權限;
expdp userid='sys/oracle as sysdba' job_name=export_schema FULL=Y directory=pump_dir dumpfile=full_dbexport.dmp logfile=full_dbexport.log
column username format a10
column opname format a20
column units format a10
select
round(sofar/totalwork*100,2) percent_completed,
t.sid,t.SERIAL#,t.USERNAME,t.OPNAME,t.TOTALWORK,t.UNITS,t.START_TIME,t.MESSAGE
from
v$session_longops t
where
sofar <> totalwork
order by target,sid;
PERCENT_COMPLETED SID SERIAL# USERNAME OPNAME TOTALWORK UNITS START_TIME MESSAGE
----------------- ---------- ---------- ---------- -------------------- ---------- ---------- -------------- ---------------------------------------------------------------------------
70.88 128 17213 FRDC Table Scan 2533381 Blocks 02-6月 -15 Table Scan: FRDC.H_BASE_MAIN05_T: 1795601 out of 2533381 Blocks done
12.58 128 17213 FRDC Table Scan 2533381 Blocks 02-6月 -15 Table Scan: FRDC.H_BASE_MAIN05_T: 318705 out of 2533381 Blocks done
70.74 128 17213 FRDC Table Scan 2533381 Blocks 02-6月 -15 Table Scan: FRDC.H_BASE_MAIN05_T: 1792046 out of 2533381 Blocks done
29.06 4 57701 FRDC Index Fast Full Scan 2127 Blocks 13-5月 -15 Index Fast Full Scan: FRDC.O_BASE_DW: 618 out of 2127 Blocks done
0 5 280 SYS Rowid Range Scan 4294967296 Blocks 02-6月 -15 Rowid Range Scan: FRDC.O_BASE_DW_T: 22055 out of 0 Blocks done
0 427 54951 FRDC EXPORT_TB 7900 MB 02-6月 -15 EXPORT_TB: EXPORT : 0 out of 7900 MB done
$ impdp userid='uname/pwd' directory=pump_dir dumpfile=file01.dmp job_name='' log_file='' remap_schema='scott:sywu' remap_table='tb01:tb02' remap_tablespace='tbs01:ts01' tables='' schemas='' table_exists_action='' tablespaces='' exclude='' sqlfile=''
上面有的參數這裡就不重復了,因為dump方式導出數據使用了xml格式,所以導入時很容易實現對象重新定義操作,這裡主要說幾個經常使用的參數:
remap_schema 表示重新定義schema,將舊的schema定義為新的schema,格式old:new,old:new....;
remap_table 表示重新定義表名,格式oldtbname:newtbname,....;
remap_tablespace 表示重新定義表空間名,格式old:new....;
tables 表示要導入的表名,如果未指定則導入dumpfile裡的所有表;
schemas 表示要導入的schema對象,如果未指定並且操作用戶有權限則導入dumpfile裡的所有schema;
table_exists_action 表示當導入表時如果表已經存在時的操作,參數值可以為:append(追加)、replace(替換數據庫中存在的)、truncate(刪除已經存在的數據,導入dumpfile裡面的數據)、skip(不做任何操作);
tablespaces 表示導入的表空間名;
exclude 表示導入排除的對象,DATABASE_EXPORT_OBJECTS表中記錄所有的(數據庫級別)排除模式,SCHEMA_EXPORT_OBJECTS表中記錄schema級別排除對象模式,TABLE_EXPORT_OBJECTS表中記錄table級別排除對象模式;
sqlfile 表示不導入數據僅生成導數ddl語句記錄在該文件中;
導入數據時要確認導入的用戶是否存在,是否有權限訪問表空間、是否具有讀寫directory的權限;
$ impdp userid='ops$sywu/sywu' schemas='ops$sywu' directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp
Import: Release 11.2.0.3.0 - Production on Sat Jun 6 13:39:43 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, OLAP, Data Mining and Real Application Testing options
Master table "OPS$SYWU"."IMPORT_OPSSYWU" successfully loaded/unloaded
Starting "OPS$SYWU"."IMPORT_OPSSYWU": userid=ops$sywu/******** schemas=ops$sywu directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "OPS$SYWU"."TB01" 74.64 MB 844416 rows
. . imported "OPS$SYWU"."TB02" 452.3 KB 13448 rows
. . imported "OPS$SYWU"."T_EXCE" 5.820 KB 2 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "OPS$SYWU"."IMPORT_OPSSYWU" successfully completed at 13:39:55
導入數據時重定義schema和tablespaces,重定義schema建議使用sysdba權限導入數據:
create tablespace tbs02 datafile '/u01/oradata/sydb/tbs02.dbf' size 10m autoextend on uniform size 4m;
create user sywu identified by sywu default tablespace tbs02 quota unlimited on tbs02;
grant create session,resource to sywu;
grant read,write on directory pump_dir to sywu;
exit;
$ impdp userid="'sys/oracle as sysdba'" remap_schema='ops$sywu:sywu' remap_tablespace=tbs01:tbs02 directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp
Import: Release 11.2.0.3.0 - Production on Sat Jun 6 13:51:48 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, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."IMPORT_OPSSYWU" successfully loaded/unloaded
Starting "SYS"."IMPORT_OPSSYWU": userid="sys/******** AS SYSDBA" remap_schema=ops$sywu:sywu remap_tablespace=tbs01:tbs02 directory=pump_dir job_name=import_opssywu dumpfile=export_opssywu.dmp
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SYWU"."TB01" 74.64 MB 844416 rows
. . imported "SYWU"."TB02" 452.3 KB 13448 rows
. . imported "SYWU"."T_EXCE" 5.820 KB 2 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."IMPORT_OPSSYWU" successfully completed at 13:52:08
其它還有限定行數的操作,操作類似上面導出操作;
The end(2015-06-06)
