數(shù)據(jù)遷移中碰見的一些問題

單位有一套Oracle 9i的古老測試數(shù)據(jù)庫，因?yàn)闄C(jī)房搬遷，所以需要遷移數(shù)據(jù)，新庫是Oracle 11g了，一個(gè)比較簡單的需求，但過程中碰見了一些問題，看似比較瑣碎，值得總結(jié)一下。

由于源庫是9i，因此只能用imp/exp，不能用數(shù)據(jù)泵。

問題1：導(dǎo)入目標(biāo)庫用戶的默認(rèn)表空間

源庫由于不規(guī)范的使用，對象默認(rèn)存儲的是數(shù)據(jù)庫默認(rèn)表空間USERS，既然是遷移，新庫就要盡量規(guī)范一些。但問題來了，impdp/expdp可以使用remap_tablespace映射新舊表空間，exp/imp應(yīng)該如何做？

網(wǎng) 上有一種說法是，首先收回用戶user的unlimited tablespace權(quán)限，然后設(shè)置user默認(rèn)表空間為bank_tbs，再將user對system和users表空間配額設(shè)置為0，意圖是讓imp 導(dǎo)入的時(shí)候，發(fā)現(xiàn)users表空間無權(quán)限，則自動(dòng)找用戶的默認(rèn)表空間bank_tbs。

revoke unlimited tablespace from user;

alter user user quota unlimited on bank_tbs;

alter user user quota 0 on system;

alter user user quota 0 on users;

但從我實(shí)測看，并不是這樣，可 以使用imp命令的show選項(xiàng)，看dmp文件內(nèi)容，create table子句是會(huì)跟著tablespace users，即指定了表使用的表空間名稱，由于user用戶在users表空間配額為0，因此會(huì)報(bào)quota相關(guān)的錯(cuò)誤，并不會(huì)找用戶默認(rèn)的 bank_tbs表空間。

我們再捋一下，

1. dump文件中有指定了tablespace users表空間。

2. 目標(biāo)庫存在users表空間，但用戶在users表空間配額為0，其默認(rèn)表空間為bank_tbs。

3. imp執(zhí)行導(dǎo)入，報(bào)錯(cuò)users表空間quota錯(cuò)誤。

用戶默認(rèn)表空間的作用，是若create table語句未指定tablespace子句，則會(huì)默認(rèn)存儲此表空間，既然如此，既然如此，又由于這是一套測試庫，因此首先改一下users表空間名稱，

alter tablespace users rename to users_k;

然 后執(zhí)行imp導(dǎo)入，就可以正常存入user用戶默認(rèn)的bank_tbs中。順著思路想，可以改一下數(shù)據(jù)庫的默認(rèn)表空間users，只要保證不存在 users表空間，dmp中create table語句就不能根據(jù)tablesapce子句，插入對應(yīng)的表空間，而是找用戶默認(rèn)的表空間。

除此之外，可以初始化就導(dǎo)入users表空間，然后拼接SQL語句，將對象可以move至其他表空間，當(dāng)然這就需要兩倍的空間。另外還可以收工改一下dmp文件中tablespace子句對應(yīng)的表空間，但只適應(yīng)于小容量文件。

這里有一些知識點(diǎn)值得關(guān)注，

1. unlimited tablespace權(quán)限，是為用戶授予resource角色是自動(dòng)添加的，但從安全性的角度來考慮，在創(chuàng)建用戶并且授予resource角色之后應(yīng)該回 收unlimited tablespace這個(gè)系統(tǒng)權(quán)限，原因就是有了這個(gè)權(quán)限，用戶可以在任意表空間中創(chuàng)建對象，就有可能惡意占領(lǐng)系統(tǒng)表空間，影響數(shù)據(jù)庫的正常運(yùn)行。

2. Oracle 9i以前，數(shù)據(jù)庫默認(rèn)用戶的表空間是SYSTEM，這是極為不合理的，因?yàn)镾YSTEM存儲的是數(shù)據(jù)庫重要的底層數(shù)據(jù)字典信息，如果無限制地存儲用戶數(shù)據(jù)，極有可能影響數(shù)據(jù)庫的運(yùn)行。從9i開始，默認(rèn)表空間則變?yōu)榱薝SERS，建庫的時(shí)候會(huì)默認(rèn)創(chuàng)建。

使用如下語句，可以查詢當(dāng)前系統(tǒng)默認(rèn)表空間，

select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

使用如下語句，可以改下當(dāng)前數(shù)據(jù)庫默認(rèn)的用戶表空間以及臨時(shí)表空間，

alter database default [temporary] tablespace tablespace_name;

問題2：數(shù)據(jù)庫字符集

為了保證數(shù)據(jù)導(dǎo)出導(dǎo)入，不會(huì)出現(xiàn)亂碼，字符集要盡量保持一致，可以使用如下語句檢索當(dāng)前數(shù)據(jù)庫使用的字符集，

select userenv('language') from dual;

例如返回結(jié)果是AMERICAN_AMERICA.ZHS16GBK。

若要檢索當(dāng)前操作系統(tǒng)字符集，可以使用，

echo $NLS_LANG

例如返回結(jié)果是AMERICAN_AMERICA.AL32UTF8。

若要更新操作系統(tǒng)字符集，可以使用，

export  NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

問題3：導(dǎo)入過程中的一些報(bào)錯(cuò)

報(bào)錯(cuò)1：

Export file created by EXPORT:V09.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

錯(cuò)誤信息提示，只用DBA用戶可以導(dǎo)入另一個(gè)DBA導(dǎo)出的文件。意思就是這個(gè)dmp文件，導(dǎo)出用戶是有DBA角色的，因此導(dǎo)入使用的用戶，必須要有DBA角色。

解決方法1：使用非DBA角色的用戶，重新exp導(dǎo)出，再用非DBA用戶imp導(dǎo)入。

解決方法2：使用DBA用戶執(zhí)行imp導(dǎo)入操作。

相比而言，生產(chǎn)系統(tǒng)一般會(huì)選擇方案1，畢竟一般業(yè)務(wù)數(shù)據(jù)的屬主，不會(huì)是一個(gè)DBA角色的用戶，如果用方案2，則要求目標(biāo)端用戶需要DBA角色，未來要是再有導(dǎo)出導(dǎo)入需求，還是需要DBA角色，無休無止了。

報(bào)錯(cuò)2：

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully

此時(shí)執(zhí)行imp可以指定full=y，或者使用fromuser和touser參數(shù)，例如，

imp user/user file=... log=... fromuser=user touser=user

明確導(dǎo)出和導(dǎo)入的用戶名稱。

問題4：創(chuàng)建視圖報(bào)錯(cuò)

導(dǎo)入日志中顯示，創(chuàng)建視圖的時(shí)候報(bào)錯(cuò)了，

ORA-01031: insufficient privileges

原因就是為用戶授予resource和connect常規(guī)角色，并不會(huì)自動(dòng)授予創(chuàng)建視圖的權(quán)限，具體可以參考(http://blog.csdn.net/bisal/article/details/31735185)，此時(shí)可以授予，

SQL> grant createany view to user;

Grant succeeded.

再次導(dǎo)入，即可以正常完成了。

對 于測試數(shù)據(jù)遷移，其實(shí)還有一點(diǎn)，就是是不是所有數(shù)據(jù)，都需要遷移？因?yàn)橥鶞y試庫中有一些，僅臨時(shí)使用的表對象等信息，如果執(zhí)行前，篩選一下真正需要的數(shù) 據(jù)，再開始執(zhí)行導(dǎo)出導(dǎo)入，可能只需要遷移小部分?jǐn)?shù)據(jù)，對于垃圾數(shù)據(jù)就可以直接忽略，這就是人們常說優(yōu)化的極致，即不做任何事。

如果您覺得此篇文章對您有幫助，歡迎關(guān)注微信公眾號：bisal的個(gè)人雜貨鋪，您的支持是對我最大的鼓勵(lì)！共同學(xué)習(xí)，共同進(jìn)步:)