Oracle imp和exp的使用
?
?
??? 入行太晚，沒接觸過早期的Oracle版本，所以一直都是用PLSQL Developer做數(shù)據(jù)的exp和imp。現(xiàn)在要寫定制腳本了才發(fā)現(xiàn)，自己居然對命令行模式下的這兩個命令很不了解，于是只要再來學一遍基礎。了解了一遍之后發(fā)現(xiàn)這兩個工具的有些細節(jié)還是很值得注意一下的，下面看一下詳細的用法：
?
?
一、exp工具的使用
?
??? 按照慣例，首先先來看一下自帶的help文檔：
?
[oracle@misdwh db_1]$ exp help=y
?
Export: Release 10.2.0.1.0 - Production on 星期三 6月 24 15:59:14 2009
?
Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
?
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
?
???? Example: EXP SCOTT/TIGER
?
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
?
???? Format:? EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
???? Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
?????????????? or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
?
USERID must be the first parameter on the command line.
?
Keyword??? Description (Default)????? Keyword????? Description (Default)
--------------------------------------------------------------------------
USERID???? username/password????????? FULL???????? export entire file (N)
BUFFER???? size of data buffer??????? OWNER??????? list of owner usernames
FILE?????? output files (EXPDAT.DMP)? TABLES?????? list of table names
COMPRESS?? import into one extent (Y) RECORDLENGTH length of IO record
GRANTS???? export grants (Y)????????? INCTYPE????? incremental export type
INDEXES??? export indexes (Y)???????? RECORD?????? track incr. export (Y)
DIRECT???? direct path (N)??????????? TRIGGERS???? export triggers (Y)
LOG??????? log file of screen output? STATISTICS?? analyze objects (ESTIMATE)
ROWS?????? export data rows (Y)?????? PARFILE????? parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS? export constraints (Y)
?
OBJECT_CONSISTENT??? transaction set to read only during object export (N)
FEEDBACK???????????? display progress every x rows (0)
FILESIZE???????????? maximum size of each dump file
FLASHBACK_SCN??????? SCN used to set session snapshot back to
FLASHBACK_TIME?????? time used to get the SCN closest to the specified time
QUERY??????????????? select clause used to export a subset of a table
RESUMABLE??????????? suspend when a space related error is encountered(N)
RESUMABLE_NAME?????? text string used to identify resumable statement
RESUMABLE_TIMEOUT??? wait time for RESUMABLE
TTS_FULL_CHECK?????? perform full or partial dependency check for TTS
VOLSIZE????????????? number of bytes to write to each tape volume
TABLESPACES????????? list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE???????????? template name which invokes iAS mode export
?
Export terminated successfully without warnings.
?
??? 發(fā)現(xiàn)參數(shù)還挺多的，但是平常用到的就沒有這么多了，也就那么5、6個參數(shù)比較重要，下面隨便舉幾個例子看一下就可以了：
?
1、導出全庫備份數(shù)據(jù)
?
$ exp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/full_db.dmp full=y
?
注意：如果服務端和客戶端之間的字符集不同，會有報錯，提示如下：
EXP-00091: Exporting questionable statistics.
對實際的數(shù)據(jù)影響不大，如果看著不舒服的話，可以通過修改客戶端的字符集來消除這個錯誤：
WINNT> set NLS_LANG=AMERICAN_AMERICA.UTF8
LINUX> export NLS_LANG=AMERICAN_AMERICA.UTF8
?
2、導出某用戶下的所有objects
?
$ exp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/rman_db.dmp owner=rman,wangxiaoqi log=/u01/oracle/devMISowb/exp/exp.log
?
3、導出某幾張表
?
$ exp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/tables_db.dmp tables=rman.bp,wangxiaoqi.t1_k
?
注：很奇怪的事情，按照help里說的，幾個列舉的用戶/表名應該是要放在括號里的，但是我每次加了括號就報錯，沒有括號就對了，非常奇怪，錯誤提示如下：
-bash: syntax error near unexpected token `('
?
4、導出某個tablespace中的內容
?
$ exp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/tbs_db.dmp tablespaces=RECOVERY_TBS
?
5、使用SQL導出table的一個子集
?
$ exp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/sub_query.dmp tables=rman.bp query=\"where bp_key='266'\"
?
注：query中不是完整的sql，僅是子句，且“"”符號前需要加入“\”轉義
?
6、使用提示模式進行導出
?
$ exp wangxiaoqi/wangxiaoqi
?
在這個模式下，exp會提示你輸入所有的參數(shù)進行導出。
?
?
?
二、imp工具的使用
?
??? 同樣先看一下help文檔，與exp非常相似：
?
[oracle@misdwh exp]$ imp help=y
?
Import: Release 10.2.0.1.0 - Production on Wed Jun 24 16:51:50 2009
?
Copyright (c) 1982, 2005, Oracle.? All rights reserved.
?
?
You can let Import prompt you for parameters by entering the IMP
command followed by your username/password:
?
???? Example: IMP SCOTT/TIGER
?
Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:
?
???? Format:? IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
???? Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
?????????????? or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
?
USERID must be the first parameter on the command line.
?
Keyword? Description (Default)?????? Keyword????? Description (Default)
--------------------------------------------------------------------------
USERID?? username/password?????????? FULL???????? import entire file (N)
BUFFER?? size of data buffer???????? FROMUSER???? list of owner usernames
FILE???? input files (EXPDAT.DMP)??? TOUSER?????? list of usernames
SHOW???? just list file contents (N) TABLES?????? list of table names
IGNORE?? ignore create errors (N)??? RECORDLENGTH length of IO record
GRANTS?? import grants (Y)?????????? INCTYPE????? incremental import type
INDEXES? import indexes (Y)????????? COMMIT?????? commit array insert (N)
ROWS???? import data rows (Y)??????? PARFILE????? parameter filename
LOG????? log file of screen output?? CONSTRAINTS? import constraints (Y)
DESTROY??????????????? overwrite tablespace data file (N)
INDEXFILE????????????? write table/index info to specified file
SKIP_UNUSABLE_INDEXES? skip maintenance of unusable indexes (N)
FEEDBACK?????????????? display progress every x rows(0)
TOID_NOVALIDATE??????? skip validation of specified type ids
FILESIZE?????????????? maximum size of each dump file
STATISTICS???????????? import precomputed statistics (always)
RESUMABLE????????????? suspend when a space related error is encountered(N)
RESUMABLE_NAME???????? text string used to identify resumable statement
RESUMABLE_TIMEOUT????? wait time for RESUMABLE
COMPILE??????????????? compile procedures, packages, and functions (Y)
STREAMS_CONFIGURATION? import streams general metadata (Y)
STREAMS_INSTANTIATION? import streams instantiation metadata (N)
VOLSIZE??????????????? number of bytes in file on each volume of a file on tape
?
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set
?
Import terminated successfully without warnings.
??? 再來舉幾個imp的用法例子：
?
1、簡單的全庫導入
?
$ imp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/full_db.dmp full=y
?
注：數(shù)據(jù)導入時有可能會報錯。主要原因有以下幾種：
??? A. 導入的BOJECTS原不屬于當前連接的用戶的
??? B. 導入的BOJECTS已經存在
??? C. 原用戶未找到
??? D. 導入庫與導入文件的字符集不同
?
2、導入到指定的用戶下：
?
$ imp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/rman_db.dmp fromuser=rmantouser=wangxiaoqi
?
注：即將rman用戶下的所有對象均導入到wangxiaoqi中
?
3、
忽略/插入數(shù)據(jù)
?
$ imp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/rman_db.dmp ignore=y
?
注：ignore=y的意思是不管原objects是否存在，均將直接插入到相應對象(并且如果導入的對象里面有其他的對象，如約束，索引等，會在數(shù)據(jù)插入后被創(chuàng)建)。
?
4、導入指定的表
?
$ imp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/rman_db.dmp tables=bp,bs
?
5、導入時忽略約束、索引、行
?
$ imp wangxiaoqi/wangxiaoqi file=/u01/oracle/devMISowb/exp/rman_db.dmp tables=bp,bs constraints=N indexs=N rows=N
?
注意：導入的用戶必須要有create相應的導入objects的權限
?
?
三、總結
?
??? 上面舉的例子基本上夠用了，需要注意的是導入、導出時的字符集的關系，這在我之前的文章里已經記載過詳細的說明，就不再記了。對于其他比較少用的子句，就等以后用到了再研究一下吧，應該不太難理解。
?
??? OK，到此結束。
?
?
?
?
?