oracle中imp命令詳解

命令：

exp 源庫usr/源庫pwd@源庫連接符 tables=dp_zyt_dgi,dp_zyt_file,dp_zyt_card file=d:\test2.dmp full=n;

imp 目標(biāo)庫usr/目標(biāo)庫pwd@目標(biāo)庫連接符 file=test.dmp log=test_imp.log ignore=Y

詳解：來源：http://blog.csdn.net/yjq8116/article/details/4025847

Oracle的導(dǎo)入實(shí)用程序(Import utility)允許從數(shù)據(jù)庫提取數(shù)據(jù)，并且將數(shù)據(jù)寫入操作系統(tǒng)文
件。imp使用的基本格式：imp[username[/password[@service]]]，以下例舉imp常用用
法。

1. 獲取幫助

imp help=y

2. 導(dǎo)入一個(gè)完整數(shù)據(jù)庫

imp system/manager file=bible_db log=dible_db full=y ignore=y

3. 導(dǎo)入一個(gè)或一組指定用戶所屬的全部表、索引和其他對象

imp system/manager file=seapark log=seapark fromuser=seapark

imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)

4. 將一個(gè)用戶所屬的數(shù)據(jù)導(dǎo)入另一個(gè)用戶

imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy

imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)

5. 導(dǎo)入一個(gè)表

imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)

6. 從多個(gè)文件導(dǎo)入

imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) log=paycheck,filesize=1G full=y

7. 使用參數(shù)文件

imp system/manager parfile=bible_tables.par

bible_tables.par參數(shù)文件：

#Import the sample tables used for the Oracle8i Database Administrator's

#Bible.

fromuser=seapark touser=seapark_copy file=seapark log=seapark_import

8. 增量導(dǎo)入(9i中已經(jīng)取消)

imp system./manager inctype= RECTORE FULL=Y FILE=A

Oracle imp/exp幫助說明

C:Documents and Settingsadministrator>exp help=y

Export: Release 9.2.0.1.0 - Production on 星期三 7月 28 17:04:43 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

通過輸入 EXP 命令和用戶名/口令，您可以

后接用戶名/口令的命令:

例程: EXP SCOTT/TIGER

或者，您也可以通過輸入跟有各種參數(shù)的 EXP 命令來控制“導(dǎo)出”

按照不同參數(shù)。要指定參數(shù)，您可以使用關(guān)鍵字:

格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)

例程: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

或 TABLES=(T1: P1,T1: P2)，如果 T1 是分區(qū)表

USERID 必須是命令行中的第一個(gè)參數(shù)。

 

關(guān)鍵字 說明(默認(rèn)) 關(guān)鍵字 說明(默認(rèn))

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

USERID 用戶名/口令 FULL 導(dǎo)出整個(gè)文件 (N)

BUFFER 數(shù)據(jù)緩沖區(qū)大小 OWNER 所有者用戶名列表

FILE 輸出文件 (EXPDAT.DMP) TABLES 表名稱列表

COMPRESS 導(dǎo)入到一個(gè)區(qū) (Y) RECORDLENGTH IO 記錄的長度

GRANTS 導(dǎo)出權(quán)限 (Y) INCTYPE 增量導(dǎo)出類型

INDEXES 導(dǎo)出索引 (Y) RECORD 跟蹤增量導(dǎo)出 (Y)

DIRECT 直接路徑 (N) TRIGGERS 導(dǎo)出觸發(fā)器 (Y)

LOG 屏幕輸出的日志文件 STATISTICS 分析對象 (ESTIMATE)

ROWS 導(dǎo)出數(shù)據(jù)行 (Y) PARFILE 參數(shù)文件名

CONSISTENT 交叉表的一致性 (N) CONSTRAINTS 導(dǎo)出的約束條件 (Y)

OBJECT_CONSISTENT 只在對象導(dǎo)出期間設(shè)置為讀的事務(wù)處理 (N)

FEEDBACK 每 x 行的顯示進(jìn)度 (0)

FILESIZE 每個(gè)轉(zhuǎn)儲文件的最大大小

FLASHBACK_SCN 用于將會話快照設(shè)置回以前狀態(tài)的 SCN

FLASHBACK_TIME 用于獲取最接近指定時(shí)間的 SCN 的時(shí)間

QUERY 用于導(dǎo)出表的子集的 select 子句

RESUMABLE 遇到與空格相關(guān)的錯(cuò)誤時(shí)掛起 (N)

RESUMABLE_NAME 用于標(biāo)識可恢復(fù)語句的文本字符串

RESUMABLE_TIMEOUT RESUMABLE 的等待時(shí)間

TTS_FULL_CHECK 對 TTS 執(zhí)行完整的或部分相關(guān)性檢查

TABLESPACES 要導(dǎo)出的表空間列表

TRANSPORT_TABLESPACE 導(dǎo)出可傳輸?shù)谋砜臻g元數(shù)據(jù) (N)

TEMPLATE 調(diào)用 iAS 模式導(dǎo)出的模板名

 

在沒有警告的情況下成功終止導(dǎo)出。

==================================================

C:Documents and Settingsadministrator>imp help=y

Import: Release 9.2.0.1.0 - Production on 星期三 7月 28 17:06:54 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 可以通過輸入 IMP 命令和您的用戶名/口令

后接用戶名/口令的命令:

例程: IMP SCOTT/TIGER

或者, 可以通過輸入 IMP 命令和各種參數(shù)來控制“導(dǎo)入”

按照不同參數(shù)。要指定參數(shù)，您可以使用關(guān)鍵字:

格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,...,vlaueN)

例程: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

或 TABLES=(T1: P1,T1: P2)，如果 T1 是分區(qū)表

USERID 必須是命令行中的第一個(gè)參數(shù)。

關(guān)鍵字 說明（默認(rèn)） 關(guān)鍵字 說明（默認(rèn)）

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

USERID 用戶名/口令 FULL 導(dǎo)入整個(gè)文件 (N)

BUFFER 數(shù)據(jù)緩沖區(qū)大小 FROMUSER 所有人用戶名列表

FILE 輸入文件 (EXPDAT.DMP) TOUSER 用戶名列表

SHOW 只列出文件內(nèi)容 (N) TABLES 表名列表

IGNORE 忽略創(chuàng)建錯(cuò)誤 (N) RECORDLENGTH IO 記錄的長度

GRANTS 導(dǎo)入權(quán)限 (Y) INCTYPE 增量導(dǎo)入類型

INDEXES 導(dǎo)入索引 (Y) COMMIT 提交數(shù)組插入 (N)

ROWS 導(dǎo)入數(shù)據(jù)行 (Y) PARFILE 參數(shù)文件名

LOG 屏幕輸出的日志文件 CONSTRAINTS 導(dǎo)入限制 (Y)

DESTROY 覆蓋表空間數(shù)據(jù)文件 (N)

INDEXFILE 將表/索引信息寫入指定的文件

SKIP_UNUSABLE_INDEXES 跳過不可用索引的維護(hù) (N)

FEEDBACK 每 x 行顯示進(jìn)度 (0)

TOID_NOVALIDATE 跳過指定類型 ID 的驗(yàn)證

FILESIZE 每個(gè)轉(zhuǎn)儲文件的最大大小

STATISTICS 始終導(dǎo)入預(yù)計(jì)算的統(tǒng)計(jì)信息

RESUMABLE 在遇到有關(guān)空間的錯(cuò)誤時(shí)掛起 (N)

RESUMABLE_NAME 用來標(biāo)識可恢復(fù)語句的文本字符串

RESUMABLE_TIMEOUT RESUMABLE 的等待時(shí)間

COMPILE 編譯過程, 程序包和函數(shù) (Y)

STREAMS_CONFIGURATION 導(dǎo)入 Streams 的一般元數(shù)據(jù) (Y)

STREAMS_INSTANITATION 導(dǎo)入 Streams 的實(shí)例化元數(shù)據(jù) (N)

下列關(guān)鍵字僅用于可傳輸?shù)谋砜臻g

TRANSPORT_TABLESPACE 導(dǎo)入可傳輸?shù)谋砜臻g元數(shù)據(jù) (N)

TABLESPACES 將要傳輸?shù)綌?shù)據(jù)庫的表空間

DATAFILES 將要傳輸?shù)綌?shù)據(jù)庫的數(shù)據(jù)文件

TTS_OWNERS 擁有可傳輸表空間集中數(shù)據(jù)的用戶

成功終止導(dǎo)入，但出現(xiàn)警告。

Oracle8i/9i EXP/IMP使用經(jīng)驗(yàn)

一、8i EXP常用選項(xiàng)

1、FULL，這個(gè)用于導(dǎo)出整個(gè)數(shù)據(jù)庫，在ROWS=N一起使用時(shí)，可以導(dǎo)出整個(gè)數(shù)據(jù)庫的結(jié)構(gòu)。
例如：

exp sys file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y

2、BUFFER和FEEDBACK，在導(dǎo)出比較多的數(shù)據(jù)時(shí)，我會考慮設(shè)置這兩個(gè)參數(shù)。例如：

exp new file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 
tables=WO4,OK_YT

3、FILL和LOG，這兩個(gè)參數(shù)分別指定備份的DMP名稱和LOG名稱，包括文件名和目錄，例子
見上面。

需要說明的是，EXP可以直接備份到磁帶中，即使用FILE=/dev/rmt0(磁帶設(shè)備名)，但是一
般我們都不這么做，原因有二：一、這樣做的速度會慢很多，二、現(xiàn)在一般都是使用磁帶庫
的，不建議直接對磁帶進(jìn)行操作。至于沒有使用磁帶庫的朋友可以考慮和UNIX的TAR結(jié)合使
用。

如果你真想使用EXP直接到磁帶，你可以參考Metalink文章“EXPORTING TO TAPE ON UNIX 
SYSTEMS”（文檔號：30428.1），該文中有詳細(xì)解釋。

4、COMPRESS參數(shù)將在導(dǎo)出的同時(shí)合并碎塊，盡量把數(shù)據(jù)壓縮到initial的EXTENT里，默認(rèn)
是N，一般建議使用。DIRECT參數(shù)將告訴EXP直接讀取數(shù)據(jù)，而不像傳統(tǒng)的EXP那樣，使用
SELECT來讀取表中的數(shù)據(jù)，這樣就減少了SQL語句處理過程。一般也建議使用。不過有些情
況下DIRECT參數(shù)是無法使用的。

5、如何使用SYSDBA執(zhí)行EXP/IMP？

這是一個(gè)很現(xiàn)實(shí)的問題，有時(shí)候我們需要使用SYSDBA來執(zhí)行EXP/IMP，如進(jìn)行傳輸表空間的
EXP/IMP，以及在9i下用SYS用戶來執(zhí)行EXP/IMP時(shí)，都需要使用SYSDBA才可。我們可以使
用下面方式連入EXP/IMP：

exp "'sys/sys as sysdba'" file=1.dmp tables=gototop.t rows=n

6、QUERY參數(shù)后面跟的是where條件，值得注意的是，整個(gè)where子句需要使用""括起來，
where子句的寫法和SELECT中相同，如果是UNIX平臺所有"和'都需要使用u26469屏蔽它們
的特殊含義：

exp gototop/gototop file=1.dmp log=1.log tables=cyx.t query="where c1=20 and 
c2=gototop"

如果是windows平臺，則使用下面的格式：

exp c/c@ncn file=c.dmp log=c.log tables=t query="""where id=1 and name='gototop'"""

二、8i IMP常用選項(xiàng)

1、FROMUSER和TOUSER，使用它們實(shí)現(xiàn)將數(shù)據(jù)從一個(gè)SCHEMA中導(dǎo)入到另外一個(gè)SCHEMA中。

2、IGNORE、GRANTS和INDEXES，其中IGNORE參數(shù)將忽略表的存在，繼續(xù)導(dǎo)入，這個(gè)對于需
要調(diào)整表的存儲參數(shù)時(shí)很有用，我們可以先根據(jù)實(shí)際情況用合理的存儲參數(shù)建好表，然后直
接導(dǎo)入數(shù)據(jù)。而GRANTS和INDEXES則表示是否導(dǎo)入授權(quán)和索引，如果想使用新的存儲參數(shù)重
建索引，或者為了加快到入速度，我們可以考慮將INDEXES設(shè)為N，而GRANTS一般都是Y。

另外一個(gè)EXP/IMP都有的參數(shù)是PARFILE，它是用來定義EXP/IMP的參數(shù)文件，也就是說，上
面的參數(shù)都可以寫在一個(gè)參數(shù)文件中，但我們一般很少使用。

三、Oracle9i EXP功能描述

Oracle9i EXP在原有的基礎(chǔ)上新增了部分新的參數(shù)，按功能主要分為以下幾個(gè)部分：

1、OBJECT_CONSISTENT - 用于設(shè)置EXP對象為只讀以保持對象的一致性。默認(rèn)是N。

2、FLASHBACK_SCN和FLASHBACK_TIME - 用于支持FLASHBACK功能而新增。

3、RESUMABLE、RESUMABLE_NAME和RESUMABLE_TIMEOUT - 用于支持RESUMABLE空間分配而新
增。

4、TTS_FULL_CHECK - 用于在傳輸表空間時(shí)使用依賴性檢查。

5、TEMPLATE - 用于支持iAS。

6、TABLESPACES - 設(shè)置表空間導(dǎo)出模式。個(gè)人覺得對于一般用戶而言，這個(gè)才是新增參數(shù)中
最實(shí)用的一個(gè)，可以讓用戶在原來的FULL、OWNER、TABLES的基礎(chǔ)上多了一種選擇，使得EXP
更加靈活。

四、不同版本的EXP/IMP問題？

一般來說，從低版本導(dǎo)入到高版本問題不大，麻煩的是將高版本的數(shù)據(jù)導(dǎo)入到低版本中，
在Oracle9i之前，不同版本Oracle之間的EXP/IMP可以通過下面的方法來解決：

1、在高版本數(shù)據(jù)庫上運(yùn)行底版本的catexp.sql；

2、使用低版本的EXP來導(dǎo)出高版本的數(shù)據(jù)；

3、使用低版本的IMP將數(shù)據(jù)庫導(dǎo)入到底版本數(shù)據(jù)庫中；

4、在高版本數(shù)據(jù)庫上重新運(yùn)行高版本的catexp.sql腳本。

但在9i中，上面的方法并不能解決問題。如果直接使用底版本EXP/IMP會出現(xiàn)如下錯(cuò)誤：

EXP-00008: ORACLE error %lu encountered

ORA-00904: invalid column name

這已經(jīng)是一個(gè)公布的BUG，需要等到Oracle10.0才能解決，BUG號為2261，你可以到METALINK
上去查看有關(guān)此BUG的詳細(xì)信息。

BUG歸BUG，我們的工作還是要做，在沒有Oracle的支持之前，我們就自己解決。在Oracle9i
中執(zhí)行下面的SQL重建exu81rls視圖即可。

CREATE OR REPLACE view exu81rls

(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)

AS select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,

decode(bitand(r.stmt_type,1), 0,'', 'SELECT,')

|| decode(bitand(r.stmt_type,2), 0,'', 'INSERT,')

|| decode(bitand(r.stmt_type,4), 0,'', 'UPDATE,')

|| decode(bitand(r.stmt_type,8), 0,'', 'DELETE,'),

r.check_opt, r.enable_flag,

DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)

from user$ u, obj$ o, rls$ r

where u.user# = o.owner#

and r.obj# = o.obj#

and (uid = 0 or

uid = o.owner# or

exists ( select * from session_roles where role='SELECT_CATALOG_ROLE')

)

/

grant select on sys.exu81rls to public;

/

五、其他問題

本文只討論了Oracle8i和9i中的EXP/IMP的一些情況，對于之前的版本，在8.0.X中，
除了QUERY參數(shù)不能用外，其它差別不大。針對沒有QUERY的情況，我們可以先在數(shù)據(jù)庫
中使用查詢條件建立臨時(shí)中間表，然后使用EXP導(dǎo)出這個(gè)中間表即可。至于Oracle7因?yàn)?br />目前使用的人較少，gototop不打算在此做詳細(xì)解釋了，如果讀者朋友有需求，你可以參考
Metalink文檔：“Overview of Export and Import in Oracle7”（文檔號：61949.1）。
關(guān)于EXP/IMP的詳細(xì)參數(shù)信息你可以通過EXP/IMP HELP=Y來獲得。

另外關(guān)于傳輸表空間的更多信息可以參考下面的Metelink文檔，本文不再詳述。

[NOTE:77523.1] Transportable Tablespaces -- An Example to setup and use.

[NOTE:100698.1] Perform tablespace point-in-time recovery using Transportable 
Tablespace.

六．對于有跨schema的index，imp時(shí)可能會報(bào)
“Segmentation fault”錯(cuò)誤。

例如schema A中建有基于schema B表的索引，在導(dǎo)入A時(shí)會報(bào)上述錯(cuò)誤，此時(shí)通過
indexes=n來屏蔽導(dǎo)入索引以防止錯(cuò)誤，事后可以用以下語句查出這樣的索引，最好將
索引放在和基表在同一個(gè)schema。

SQL>select index_name,owner,table_name,table_owner

from dba_indexes

 where owner!=table_owner;

 

七．導(dǎo)入到不同于原表空間的表空間

原來的數(shù)據(jù)在USERS表空間里面，我想把它IMP進(jìn)APP表空間，我已經(jīng)修改了目的
用戶的默認(rèn)表空間，為什么結(jié)果還是IMP到USERS表空間中了呢？

Solution：Oracle并沒有提供什么參數(shù)來指定要導(dǎo)入哪個(gè)表空間，數(shù)據(jù)默認(rèn)將導(dǎo)入到原
本導(dǎo)出時(shí)數(shù)據(jù)所在的表空間中，但是我們可以通過以下的方法來實(shí)現(xiàn)導(dǎo)入到不同的表空
間。

 

1．在IMP時(shí)候使用INDEXFILE參數(shù)

 當(dāng)給此參數(shù)指定了某一文件名，IMP的時(shí)候所有的index將不會直接導(dǎo)入到表空間中，
而是在指定的文件中生成創(chuàng)建index的腳本。

 然后用文本編輯器打開此文件，直接編輯腳本中的storage參數(shù)，修改為想要導(dǎo)入的表
空間名稱。

 然后重新執(zhí)行IMP，使用INDEXS=n參數(shù)將除Index之外的Objects導(dǎo)入。

 最后進(jìn)入SQL*PLUS，直接運(yùn)行剛才編輯的腳本，生成索引。

 該方法適用于將index以及constraints導(dǎo)入指定的表空間。

 

2.改變目的用戶的默認(rèn)表空間

 這就是上面說的經(jīng)常有人提問的方法。但是上述的問題之所以沒有成功，是因?yàn)槿鄙倭?br />下面的幾步。

 首先，收回目的用戶的"UNLIMITED TABLESPACE"權(quán)限：

 revoke unlimited tablespace from username;

 其次，取消目的用戶在原數(shù)據(jù)導(dǎo)出表空間中的配額，這樣才能迫使IMP把數(shù)據(jù)導(dǎo)入到
用戶的默認(rèn)表空間中去。

 然后，將希望導(dǎo)入的表空間設(shè)為目的用戶的默認(rèn)表空間，并添加配額。

 最后，執(zhí)行IMP。

 

3.使用TOAD

 TOAD是強(qiáng)大的Oracle數(shù)據(jù)庫管理軟件，是Quest出品的第三方軟件，我們可以使用其
中的Rebuild Multi Objects工具來實(shí)現(xiàn)將多個(gè)Object轉(zhuǎn)移到指定的表空間。

 于是我們可以不管三七二十一，先IMP，然后再用TOAD作事后的修改。

關(guān)于TOAD的使用，此處不作詳細(xì)解釋。

 

八．導(dǎo)入工具imp可能出現(xiàn)的問題

(1) 數(shù)據(jù)庫對象已經(jīng)存在

一般情況, 導(dǎo)入數(shù)據(jù)前應(yīng)該徹底刪除目標(biāo)數(shù)據(jù)下的表, 序列, 函數(shù)/過程,觸發(fā)器等;

數(shù)據(jù)庫對象已經(jīng)存在, 按缺省的imp參數(shù), 則會導(dǎo)入失敗

如果用了參數(shù)ignore=y, 會把exp文件內(nèi)的數(shù)據(jù)內(nèi)容導(dǎo)入

如果表有唯一關(guān)鍵字的約束條件, 不合條件將不被導(dǎo)入

如果表沒有唯一關(guān)鍵字的約束條件, 將引起記錄重復(fù)

 

(2) 數(shù)據(jù)庫對象有主外鍵約束

 不符合主外鍵約束時(shí), 數(shù)據(jù)會導(dǎo)入失敗

 解決辦法: 先導(dǎo)入主表, 再導(dǎo)入依存表

disable目標(biāo)導(dǎo)入對象的主外鍵約束, 導(dǎo)入數(shù)據(jù)后, 再enable它們

 

(3) 權(quán)限不夠

如果要把A用戶的數(shù)據(jù)導(dǎo)入B用戶下, A用戶需要有imp_full_database權(quán)限

 

(4) 導(dǎo)入大表( 大于80M ) 時(shí), 存儲分配失敗

 默認(rèn)的EXP時(shí), compress = Y, 也就是把所有的數(shù)據(jù)壓縮在一個(gè)數(shù)據(jù)塊上.

 導(dǎo)入時(shí), 如果不存在連續(xù)一個(gè)大數(shù)據(jù)塊, 則會導(dǎo)入失敗.

 導(dǎo)出80M以上的大表時(shí), 記得compress= N, 則不會引起這種錯(cuò)誤.

 

(5) imp和exp使用的字符集不同

 如果字符集不同, 導(dǎo)入會失敗, 可以改變unix環(huán)境變量或者NT注冊表里 
NLS_LANG相關(guān)信息.導(dǎo)入完成后再改回來.

 

(6) imp和exp版本不能往上兼容

imp可以成功導(dǎo)入低版本exp生成的文件, 不能導(dǎo)入高版本exp生成的文件

根據(jù)情況我們可以用低版本的oracle客戶端的exp導(dǎo)出數(shù)據(jù)庫，然后進(jìn)行導(dǎo)入操作。

 

（7）ROLLBACK段不夠

 Export/Import使用過程中, 如果數(shù)據(jù)量很大會出現(xiàn)'ROLLBACK段不夠'的錯(cuò)誤. 
這時(shí)要建一個(gè)足夠大的ROLLBACK段, 使它ONLINE而其他ROLLBACK段
OFFLINE. 這樣, Export/Import使用這個(gè)大ROLLBACK段, 從而避免上述現(xiàn)象.

(8)EXPORT/IMPORT對SQUENCE的影響

在兩種情況下，EXPORT/IMPORT會對SEQUENCE。

a. 如果在EXPORT時(shí)，用戶正在取SEQUENCE的值，可能造成SEQUENCE的不
一致。

b. 另外如果SEQUENCE使用CACHE，在EXPORT時(shí)，那些在CACHE中的值就
會被忽略 的，只是從數(shù)據(jù)字典里面取當(dāng)前值EXPORT。如果在進(jìn)行FULL方式的
EXPORT/IMPORT時(shí)，恰好在用sequence更新表中某列數(shù)據(jù)， 而且不是上面兩種

情況，則導(dǎo)出的是更新前的數(shù)據(jù)。如果采用常規(guī)路徑方式，每一行數(shù)據(jù)都是用
INSERT語句，一致性檢查和INSERT TRIGGER 如果采用DIRECT方式，某些約
束和trigger可能不觸發(fā)，如果在trigger中使用sequence.nextval，將會對sequence
有影響。

九．ORACLE獲取DDL的幾種常用的方法

大體的分為三種方法：

 

1．可以通過toad、plsql develop等第三方工具進(jìn)行導(dǎo)出DLL操作，用這種辦法的好
處在于操作簡單方便，但需要安裝，下面簡單介紹一下用這兩個(gè)工具獲得DLL語句
的操作。

 

2．直接通過EXP/IMP工具

 

Oracle提供的最原始最實(shí)用的導(dǎo)入導(dǎo)出工具，我們大體上可以分為三種辦法實(shí)現(xiàn)導(dǎo)
出DDL。

 

a. 通過imp指定indexfile參數(shù)，但這種辦法不爽在于有每行前會有REM

 

語法大體如下：

 

exp userid=... tables=emp rows=n file=emp.dmp

imp userid=... file=emp.dmp indexfile=emp.sql

 

b. 通過imp指定show=y，同時(shí)指定log參數(shù)，格式上也不是很爽，在格式上很美
觀的還是通過工具導(dǎo)出的比較美觀

 

語法大體如下：

 

exp userid=... tables=emp rows=n file= emp.dmp

imp userid=... file= emp.dmp show=y log=emp.sql

 

c. 利用unix下有strings命令,語法大體如下，這種方法比較野蠻：

 

exp userid=... tables=tab1 rows=n file=tab1.dmp

strings emp.dmp >emp.sql

emp.sql中就有DLL語句了

 

3．通過9i的DBMS_METADATA包得到DLL語句

 

基本上用到的語法如下：

 

a. 獲取單個(gè)的建表和建索引的語法

set heading off;

set echo off;

Set pages 999;

set long 90000;

 

spool DEPT.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

 

b.獲取一個(gè)SCHEMA下的所有建表和建索引的語法，以scott為例：

 

set pagesize 0

set long 90000

set feedback off

 

set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)

 FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)

 FROM USER_INDEXES u;

spool off;

 

c. 獲取某個(gè)SCHEMA的建全部存儲過程的語法

connect brucelau /brucelau;

spool procedures.sql

 

select

 DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name)

from

 user_objects u

where

 object_type = 'PROCEDURE';

spool off;

另：dbms_metadata.get_ddl('TABLE','TAB1','USER1')

三個(gè)參數(shù)中，第一個(gè)指定導(dǎo)出DDL定義的對象類型（此例中為表類型），第二個(gè)是
對象名（此例中即表名），第三個(gè)是對象所在的用戶名。

 

十．優(yōu)化exp和imp的性能

exp /imp 可配置一些參數(shù)，以改進(jìn)Export和Import的性能。至于性能具體能提升多

高，則取決于要導(dǎo)出或?qū)氲臄?shù)據(jù)，以及數(shù)據(jù)量的大小。

 

導(dǎo)出性能：用以下通用參數(shù)改進(jìn)Export的性能：

DIRECT：對一個(gè)Export，將DIRECT參數(shù)設(shè)為DIRECT=Y，將數(shù)據(jù)直接轉(zhuǎn)移
到Export客戶端。這樣的轉(zhuǎn)移繞過SQL命令處理層（估值緩沖區(qū)），從而避免了
無謂的數(shù)據(jù)轉(zhuǎn)換。

RECORDLENGTH：增大RECORDLENGTH參數(shù)值，來改進(jìn)性能。為該參數(shù)
推薦的值是DB_BLOCK_SIZE的一個(gè)整數(shù)倍，或者是文件系統(tǒng)I/O塊大小的一個(gè)
整數(shù)倍。更改這個(gè)參數(shù)的值會影響寫入磁盤前積累的數(shù)據(jù)量。該參數(shù)的最大值為
64千字節(jié)（64 KB）。

 

導(dǎo)入性能：用以下通用參數(shù)改進(jìn)Import的性能：

BUFFER：BUFFER參數(shù)指定了緩沖區(qū)的大小，數(shù)據(jù)行將通過這個(gè)緩沖區(qū)進(jìn)行轉(zhuǎn)
移，由此便決定了由Import導(dǎo)入的每個(gè)陣列插入中的行數(shù)。如配合COMMIT=Y
參數(shù)指定BUFFER參數(shù)，則Import會在每一個(gè)陣列插入之后進(jìn)行提交，而不是在
載入完整的表之后再默認(rèn)地提交。一個(gè)較大的BUFFER參數(shù)值有助于改善性能。
但假如BUFFER值過高，會造成OS分頁和換行，反而影響性能。

INDEXES。INDEXES參數(shù)指定是否導(dǎo)入用戶生成的索引。如指定
INDEXES=N，則將提高Import的性能，因?yàn)樵谝粋€(gè)無索引的表中導(dǎo)入數(shù)據(jù)要快得
多。注意，如果首先用INDEXFILE=返回Import，來在請求的Import模式下提取
索引創(chuàng)建命令，再將其寫入指定的文件，那么以后仍可重建索引。

使用INDEXFILE選項(xiàng)時(shí)，不會導(dǎo)入數(shù)據(jù)對象。可對輸出文件（）進(jìn)行編輯，
并將其作為一個(gè)SQL腳本使用，以便在Import之后創(chuàng)建索引。

另外還可以通過調(diào)整一下參數(shù)提高exp和imp的性能

. 修改sqlnet.ora在里面增加trace_level_client=off 
. 執(zhí)行dbmspool.sql然后SQL>begin 
SQL> dbms_shared_pool.sizes(300);

SQL> end;

SQL> /

. sql>ALTER SYSTEM FLUSH SHARED_POOL 
. sql>alter system set LOG_CHECKPOINT_INTERVAL = 
redologfilesizebytes/512bytes=1000 
也可以修改log_checkpoint_timeout=24000

. sql>alter system set fast_start_mttr_target =24000 
. sql>alter system set pga_aggregate_target=100M

沒有下commit=y所以只有通過

A.select undtsn,undoblks from v$undostat;的變化來看資料又沒有進(jìn)去了

B. 在imp時(shí)通過設(shè)定 FEEDBACK=N筆資料來顯示Imp的進(jìn)度

 

重整的時(shí)候如果要drop 掉table重建的話，一定要注意該table上有沒有FK等

 

在EXP /IMP的時(shí)候?yàn)榱思铀傩枰獙ndex=n參數(shù)加進(jìn)exp/imp執(zhí)行腳本，這樣的話在
做完imp后需要recreate index 其實(shí)資料量大的話recreate也很花時(shí)間，故可以通過在
create index 的腳本里面加入parallel 5;來提速

 

另I/O可以在Windows 的效能的地方看到

 

 

十一.IMPORT時(shí)的對象倒入順序

在倒入數(shù)據(jù)時(shí)，ORACLE有一個(gè)特定的順序，可能隨數(shù)據(jù)庫版本不同而有所變化，

但是現(xiàn)在是這樣的。

 1. Tablespaces 14. Snapshot Logs

 2. Profiles 15. Job Queues

 3. Users 16. Refresh Groups

 4. Roles 17. Cluster Definitions

 5. System Privilege Grants 18. Tables (also grants,comments,

 6. Role Grants indexes, constraints, auditing)

 7. Default Roles 19. Referential Integrity

8. Tablespace Quotas 20. POSTTABLES actions

9. Resource Costs 21. Synonyms

10. Rollback Segments 22. Views

11. Database Links 23. Stored Procedures

12. Sequences 24. Triggers, Defaults and Auditing

13. Snapshots

按這個(gè)順序主要是解決對象之間依賴關(guān)系可能產(chǎn)生的問題。TRIGGER最后導(dǎo)入，所以在
INSERT數(shù)據(jù)到數(shù)據(jù)庫時(shí)不會激發(fā)TRIGGER。在導(dǎo)入后可能會有一些狀態(tài)是INVALID的
PROCEDURE，主要是IMPORT時(shí)會影響一些數(shù)據(jù)庫對象，而IMPORT并不重新編譯
PROCEDURE，從而造成這種情況，可以重新編譯之，就能解決這個(gè)問題。

 

十二. Imp/exp的模糊導(dǎo)入導(dǎo)出

前段時(shí)間在網(wǎng)上看到一篇關(guān)于imp/exp模糊導(dǎo)入導(dǎo)出的文章，甚感興趣，今
天中午做了一個(gè)測試，感覺Oracle實(shí)在是博大，有很多東西不用的話，對
我們來說永遠(yuǎn)是一個(gè)陌生的地帶。揭去面紗，也就那么回事！

說明：使用通配符導(dǎo)出的最大優(yōu)點(diǎn)，就是當(dāng)你的庫中有很多表，但你又不想
全導(dǎo)，只導(dǎo)其中一部分的時(shí)候，你可以盡可能的少寫部分代碼，提供工作效
率！

我一般喜歡以示例的方式展現(xiàn)或表達(dá)一些東西，這樣大家可能了解/掌握的
更快以下！畢竟很多東西我們大部分同志知其然就可以了！嘿嘿。^|^

備注：該項(xiàng)特性在９i以后新增

測試過程如下：

SQL> conn study/study

已連接。

SQL>

SQL> create table toms_test_1 (no int);

表已創(chuàng)建。

SQL> create table toms_test_2 (no int);

表已創(chuàng)建。

SQL> create table toms_test_3 (no int);

表已創(chuàng)建。

SQL> insert into toms_test_1 values(1);

已創(chuàng)建 1 行。

SQL> insert into toms_test_2 values(2);

已創(chuàng)建 1 行。

SQL> insert into toms_test_3 values(3);

已創(chuàng)建 1 行。

SQL> commit;

提交完成。

Microsoft Windows XP [版本 5.1.2600]

(C) 版權(quán)所有 1985-2001 Microsoft Corp.

C:Documents and Settings>e:

E:>exp study/study tables=(study.toms_test%) file=toms_test.dmp 
rows=y

Export: Release 9.2.0.1.0 - Production on 星期三 6月 7 12:42:32 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

已導(dǎo)出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將導(dǎo)出指定的表通過常規(guī)路徑 ...

. . 正在導(dǎo)出表 TOMS_TEST_1 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_2 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_3 1 行被導(dǎo)出

在沒有警告的情況下成功終止導(dǎo)出。

 

SQL>

SQL> drop table toms_test_1;

表已丟棄。

SQL> drop table toms_test_2;

表已丟棄。

SQL> drop table toms_test_3;

表已丟棄。

SQL>

SQL> select table_name from tabs where table_name like 'TOMS_TEST%';

未選定行

SQL>

 

E:>imp study/study file=toms_test.dmp fromuser=study touser=study

Import: Release 9.2.0.1.0 - Production on 星期三 6月 7 12:44:01 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

經(jīng)由常規(guī)路徑導(dǎo)出由EXPORT:V09.02.00創(chuàng)建的文件

已經(jīng)完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_1" 1行被導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_2" 1行被導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_3" 1行被導(dǎo)入

成功終止導(dǎo)入，但出現(xiàn)警告。

E:>

SQL> select table_name from tabs where table_name like 'TOMS_TEST%';

TABLE_NAME

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

TOMS_TEST_1

TOMS_TEST_2

TOMS_TEST_3

SQL> select * from toms_test_1;

NO

----------

1

SQL> select * from toms_test_2;

NO

----------

2

SQL> select * from toms_test_3;

NO

----------

3

SQL>

SQL> select table_name from tabs where table_name like '%TEST%';

TABLE_NAME

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

TOMS_TEST_1

TOMS_TEST_2

TOMS_TEST_3

SQL>

 

E:>exp study/study tables=(study.%test%) file=test.dmp rows=y

Export: Release 9.2.0.1.0 - Production on 星期三 6月 7 12:51:56 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

已導(dǎo)出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將導(dǎo)出指定的表通過常規(guī)路徑 ...

. . 正在導(dǎo)出表 TOMS_TEST_1 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_2 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_3 1 行被導(dǎo)出

在沒有警告的情況下成功終止導(dǎo)出。

E:>

 

SQL> drop table toms_test_1;

表已丟棄。

SQL> drop table toms_test_2;

表已丟棄。

SQL> drop table toms_test_3;

表已丟棄。

SQL>

E:>imp study/study file=test.dmp fromuser=study touser=study

Import: Release 9.2.0.1.0 - Production on 星期三 6月 7 12:53:11 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

經(jīng)由常規(guī)路徑導(dǎo)出由EXPORT:V09.02.00創(chuàng)建的文件

已經(jīng)完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_1" 1行被導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_2" 1行被導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_3" 1行被導(dǎo)入

成功終止導(dǎo)入，但出現(xiàn)警告。

E:>

SQL> select table_name from tabs where table_name like '%TEST%';

TABLE_NAME

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

TOMS_TEST_1

TOMS_TEST_2

TOMS_TEST_3

SQL>

 

E:>exp study/study tables=(%test%) file=test.dmp rows=y

Export: Release 9.2.0.1.0 - Production on 星期三 6月 7 13:08:56 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

已導(dǎo)出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將導(dǎo)出指定的表通過常規(guī)路徑 ...

. . 正在導(dǎo)出表 TOMS_TEST_1 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_2 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_3 1 行被導(dǎo)出

在沒有警告的情況下成功終止導(dǎo)出。

E:>

SQL> conn toms/toms

已連接。

SQL> create table stu_1 (no int);

表已創(chuàng)建。

SQL> create table stu_2 (no int);

表已創(chuàng)建。

SQL> insert into stu_1 values(1);

已創(chuàng)建 1 行。

SQL> insert into stu_2 values(2);

已創(chuàng)建 1 行。

SQL> commit;

提交完成。

E:>exp study/study tables=(%test%,toms.stu%) file=test.dmp rows=y

Export: Release 9.2.0.1.0 - Production on 星期三 6月 7 13:11:05 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

已導(dǎo)出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將導(dǎo)出指定的表通過常規(guī)路徑 ...

. . 正在導(dǎo)出表 TOMS_TEST_1 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_2 1 行被導(dǎo)出

. . 正在導(dǎo)出表 TOMS_TEST_3 1 行被導(dǎo)出

當(dāng)前的用戶已更改為 TOMS

. . 正在導(dǎo)出表 STU_1 1 行被導(dǎo)出

. . 正在導(dǎo)出表 STU_2 1 行被導(dǎo)出

在沒有警告的情況下成功終止導(dǎo)出。

SQL> conn study/study

已連接。

SQL> drop table toms_test_1;

表已丟棄。

SQL> drop table toms_test_2;

表已丟棄。

SQL> drop table toms_test_3;

表已丟棄。

SQL> select table_name from tabs where table_name like '%TEST%';

未選定行

SQL> conn toms/toms

已連接。

SQL> drop table stu_1;

表已丟棄。

SQL> drop table stu_2;

表已丟棄。

SQL> select table_name from tabs where table_name like '%STU%';

未選定行

SQL>

E:>imp study/study file=test.dmp fromuser=study touser=study 
tables=(%test%)

Import: Release 9.2.0.1.0 - Production on 星期三 6月 7 13:15:22 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

經(jīng)由常規(guī)路徑導(dǎo)出由EXPORT:V09.02.00創(chuàng)建的文件

已經(jīng)完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_1" 1行被導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_2" 1行被導(dǎo)入

. . 正在導(dǎo)入表 "TOMS_TEST_3" 1行被導(dǎo)入

成功終止導(dǎo)入，但出現(xiàn)警告。

SQL> conn study/study

已連接。

SQL> select table_name from tabs where table_name like '%TEST%';

TABLE_NAME

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

TOMS_TEST_1

TOMS_TEST_2

TOMS_TEST_3

SQL> select * from toms_test_1;

NO

----------

1

SQL> select * from toms_test_2;

NO

----------

2

SQL> select * from toms_test_3;

NO

----------

3

SQL>

E:>imp study/study fromuser=toms touser=toms file=test.dmp 
tables=(stu%)

Import: Release 9.2.0.1.0 - Production on 星期三 6月 7 13:23:49 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

經(jīng)由常規(guī)路徑導(dǎo)出由EXPORT:V09.02.00創(chuàng)建的文件

已經(jīng)完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的導(dǎo)入

. 正在將TOMS的對象導(dǎo)入到 TOMS

. . 正在導(dǎo)入表 "STU_1" 1行被導(dǎo)入

. . 正在導(dǎo)入表 "STU_2" 1行被導(dǎo)入

成功終止導(dǎo)入，但出現(xiàn)警告。

E:>

SQL> conn toms/toms

已連接。

SQL> select table_name from tabs where table_name like '%STU%';

TABLE_NAME

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

STU_1

STU_2

SQL> select * from stu_1;

NO

----------

1

SQL> select * from stu_2;

NO

----------

2

SQL>