IMP-00008: unrecognized statement in the export file: string

279

上周需要將Oracle10g中的某一個用戶下的對象導(dǎo)入到oracle11g中去。用exp在10g的數(shù)據(jù)庫服務(wù)器上導(dǎo)出的dump文件，再用imp在11g的數(shù)據(jù)庫服務(wù)器上將dump文件導(dǎo)入到數(shù)據(jù)庫中，前面執(zhí)行的都很正常，后來發(fā)現(xiàn)報如下錯誤： IMP-00008: unrecognized statement in the

上周需要將Oracle10g中的某一個用戶下的對象導(dǎo)入到oracle11g中去。用exp在10g的數(shù)據(jù)庫服務(wù)器上導(dǎo)出的dump文件，再用imp在11g的數(shù)據(jù)庫服務(wù)器上將dump文件導(dǎo)入到數(shù)據(jù)庫中，前面執(zhí)行的都很正常，后來發(fā)現(xiàn)報如下錯誤：

IMP-00008: unrecognized statement in the export file:

IMP-00008: unrecognized statement in the export file:

IMP-00008: unrecognized statement in the export file:
? ?
IMP-00008: unrecognized statement in the export file:
?
IMP-00008: unrecognized statement in the export file:
? #
IMP-00008: unrecognized statement in the export file:
? #
IMP-00008: unrecognized statement in the export file:

IMP-00008: unrecognized statement in the export file:

IMP-00008: unrecognized statement in the export file:

IMP-00008: unrecognized statement in the export file:

IMP-00008: unrecognized statement in the export file:

IMP-00008: unrecognized statement in the export file:

Oracle官方文檔的對這個錯誤的是這么說的：

IMP-00008: unrecognized statement in the export file:string
Cause:Import did not recognize a statement in the export file. Either the export file was corrupted, or an Import internal error has occurred.
Action:If the export file was corrupted, retry with a new export file. Otherwise, report this as an Import internal error and submit the export file to customer support.
看這個沒有什么幫助。

后來查到是由于Oracle11g有個新特性，不能導(dǎo)入空表：

11G中有個新特性，當(dāng)表無數(shù)據(jù)時，不分配segment，以節(jié)省空間，這樣就會沒有導(dǎo)出數(shù)據(jù)　

現(xiàn)在常用的方法
? ??
IMP-00008: unrecognized statement in the export file:
? ?
IMP-00008: unrecognized statement in the export file:
在用imp 導(dǎo)入數(shù)據(jù)的時候出現(xiàn)IMP-00008: unrecognized statement in the export file:的錯誤，

解決方法

在Oracle 11g中，exp默認(rèn)不能導(dǎo)出空表。用傳統(tǒng)的exp，imp進(jìn)行異構(gòu)平臺數(shù)據(jù)庫遷移會比較麻煩。不過可以使用expdp、impdp進(jìn)行遷移。

把64位windows 2003的Oracle11gR2數(shù)據(jù)庫遷移到64位Linux RedHat Enterprise 5中，可以使用expdp、impdp進(jìn)行遷移數(shù)據(jù)。

如：Linux 為A服務(wù)器，Linux為B服務(wù)器,數(shù)據(jù)庫用戶為test，把A服務(wù)器的數(shù)據(jù)遷移到B服務(wù)器中

在A服務(wù)器操作
1、創(chuàng)建目錄
SQL> create directoryexpdp_diras ‘/home/Oracle/expdp_dir’;? //一臺服務(wù)器只要創(chuàng)建一次
2 賦予導(dǎo)出的用戶權(quán)限
SQL> grant read,write on directoryexpdp_dirto test；//一臺服務(wù)器只要創(chuàng)建一次

3、在Linux目錄創(chuàng)建目錄/home/Oracle/expdp_dir

mkdir -p /home/Oracle/expdp_dir //一臺服務(wù)器只要創(chuàng)建一次

4、在命令窗口導(dǎo)出：

expdp test/test DIRECTORY=expdp_dirDUMPFILE=test.dmp logfile=testexpdp.log? //每次都要操作
在B服務(wù)器中操作：

5、SQL> create directory impdp_dir as ‘/home/Oracle/impdp_dir’;//一臺服務(wù)器只要創(chuàng)建一次

? SQL> grant read,write on directory impdp_dir to test；//一臺服務(wù)器只要創(chuàng)建一次

6、 在系統(tǒng)中需要有/home/Oracle/impdp_dir目錄
7、用ftp把A服務(wù)器導(dǎo)出的數(shù)據(jù)上傳到B服務(wù)器的/home/Oracle/impdp_dir目錄中
在A服務(wù)器中配置好B服務(wù)器的服務(wù)器名,在A服務(wù)器導(dǎo)入數(shù)據(jù)
8、在命令窗口導(dǎo)入：
impdp test/test@B_database DIRECTORY=impdp_dir DUMPFILE=test.dmp logfile=testimpdp.log

（這里注意大小寫，如果test.dmp在linux中為大寫，必須更改為大寫。Linux區(qū)分大小寫）

解決方法，原理就是往所有的空表中分配空間：

方法1：
用Oracle sqldeveloper ,建立下面的存儲過程
–查詢當(dāng)前用戶下的所有空表，并生成空表的插入語句腳本
create or replace
procedure? generate_tab_segement is
v_table????????????????????? NVARCHAR2(200);
v_sql??????????????????????? VARCHAR2(200);
v_q????????????????????????? NUMBER;
v_char_value???????????????? NVARCHAR2(10);
v_varchar2_value???????????? NVARCHAR2(10);
v_number_value?????????????? NVARCHAR2(10);
v_date_value???????????????? NVARCHAR2(10);
v_add_value????????????????? NVARCHAR2(10);
v_separate_value???????????? NVARCHAR2(10);
v_insert_sql???????????????? NVARCHAR2(4000);
col_num????????????????????? NVARCHAR2(200);
tb_name????????????????????? NVARCHAR2(200);
TYPE TCUR IS REF CURSOR;
c2 TCUR;
begin
???? FOR r1 IN (SELECT table_name tn FROM tabs) LOOP
??????? BEGIN
??????????? v_table?? :=r1.tn;
??????????? v_sql???? := ‘SELECT count(*) as q FROM ‘ || v_table || ‘ where rownum = 1’;
??????????? v_char_value := ”’1”’;
??????????? v_varchar2_value := ”’1”’;
??????????? v_number_value := ‘1’;
??????????? v_date_value := ‘sysdate’;
??????????? v_separate_value := ‘,’;
??????????? v_insert_sql := ‘insert into ‘;
??????????? v_add_value :=’null’;
??????????? OPEN c2 FOR v_sql ;
??????????? LOOP
??????????????? FETCH c2 INTO v_q;
??????????????? EXIT WHEN c2%NOTFOUND;
??????????????? IF v_q = 0 THEN
??????????????????? –DBMS_OUTPUT.PUT_LINE(v_table);
??????????????????? –構(gòu)造插入行
??????????????????? FOR user_null_table IN (select to_char(count(*)) as col_num,
??????????????????? t.TABLE_NAME as tb_name
??????????????????? from user_tab_columns t
??????????????????? where t.TABLE_NAME= v_table
??????????????????? group by t.TABLE_NAME) LOOP
??????????????????????? BEGIN
??????????????????????????? col_num????? := user_null_table.col_num;
??????????????????????????? tb_name????? := user_null_table.tb_name;
??????????????????????????? v_insert_sql := ‘insert into ‘;
??????????????????????????? v_insert_sql := v_insert_sql || tb_name || ‘ values(‘ ;
??????????????????????????? FOR user_tab_cols IN (select * from user_tab_columns t where t.TABLE_NAME = tb_name ORDER BY t.COLUMN_ID) LOOP
??????????????????????????????? BEGIN??????????????????????????????????????????????????????????????????
??????????????????????????????????? v_add_value :=’null’;???????????????????????????????????
??????????????????????????????????? IF user_tab_cols.COLUMN_ID = col_num THEN
?????????????????????????????????????? v_separate_value := ”;
??????????????????????????????????? else
??????????????????????????????????????? v_separate_value := ‘,’;
??????????????????????????????????? END IF;??????????????????????????????????
??????????????????????????????????? if user_tab_cols.DATA_TYPE = ‘CHAR’ THEN
??????????????????????????????????????? v_add_value := v_char_value;
??????????????????????????????????? END if;
???????????????????????????????????
??????????????????????????????????? IF user_tab_cols.DATA_TYPE = ‘VARCHAR2’ THEN
??????????????????????????????????????? v_add_value := v_varchar2_value;
??????????????????????????????????? END if;???????????????????????????????????
??????????????????????????????????? IF user_tab_cols.DATA_TYPE = ‘NVARCHAR2’ THEN
??????????????????????????????????????? v_add_value := v_varchar2_value;
??????????????????????????????????? END if;???????????????????????????????????
??????????????????????????????????? IF user_tab_cols.DATA_TYPE = ‘NUMBER’ THEN
??????????????????????????????????????? v_add_value := v_number_value;
??????????????????????????????????? END if;???????????????????????????????????
??????????????????????????????????? IF user_tab_cols.DATA_TYPE = ‘LONG’ THEN
??????????????????????????????????????? v_add_value := v_number_value;
??????????????????????????????????? END if;???????????????????????????????????
??????????????????????????????????? IF user_tab_cols.DATA_TYPE = ‘DATE’ THEN
??????????????????????????????????????? v_add_value := v_date_value;
??????????????????????????????????? END IF;???????????????????????????????????
??????????????????????????????????? IF substr(user_tab_cols.DATA_TYPE,0,9) = ‘TIMESTAMP’ THEN
??????????????????????????????????????? v_add_value := v_date_value;
??????????????????????????????????? END IF;??????????????????????????????????
??????????????????????????????????? v_insert_sql := v_insert_sql || v_add_value ||
??????????????????????????????????????? v_separate_value;???????????????????????????????
??????????????????????????????? end;
??????????????????????????? END LOOP;???????????????????????????
??????????????????????????? v_insert_sql := v_insert_sql || ‘);’;
??????????????????????????? DBMS_OUTPUT.PUT_LINE(v_insert_sql);??????????????????????????
??????????????????????? end;
??????????????????? END LOOP;???????????????
??????????????? end if;
??????????? END LOOP;
??????????? CLOSE c2;
??????? end;
??? END LOOP;
end generate_tab_segement;
/
然后用圖形界面工具去調(diào)用該存儲過程，讓存儲過程語句執(zhí)行一次，并起作用，不知道什么原因有些表的數(shù)據(jù)沒有插入進(jìn)去，為了保證不會出錯，大家可以將執(zhí)行完顯示的SQL語句在執(zhí)行一次就絕對不會錯誤。
然后將執(zhí)行完的結(jié)果復(fù)制一次，在用圖形界面執(zhí)行一次，就可以了

? 版權(quán)聲明
THE END
數(shù)據(jù)庫
# 解決方法
喜歡就支持一下吧
點贊9 分享