Oracle常見錯誤——ORA-00942

根據前些天為某大飛機公司匯入某大飛機生產資料的經曆，準備寫篇關於Oracle匯出檔案DMP格式的分析，結果在本機匯出一組示範資料時候，出現了錯誤，順便解決下。

本機裝的是Oracle 11g 32bit(11.0.6)的Client，主要是為了使用ODT.Net 11.0.6.21，支援VS2008。但是採用EXP命令匯出資料庫的時候，出現了些詭異的錯誤。

執行

C:/>EXP plx/plx@orcl  FILE=c:/plx.dmp TABLES=(s,sc,c)

結果報錯

Export: Release 11.1.0.6.0 – Production on 星期日 7月 26 12:42:19 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
串連到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字元集和 UTF8 NCHAR 字元集
伺服器使用 UTF8 字元集 (可能的字元集轉換)

即將匯出指定的表通過常規路徑…
. . 正在匯出表                               S
EXP-00008: 遇到 ORACLE 錯誤 904
ORA-00904: “MAXSIZE”: invalid identifier
. . 正在匯出表                              SC
EXP-00008: 遇到 ORACLE 錯誤 1003
ORA-01003: no statement parsed
. . 正在匯出表                               C
EXP-00008: 遇到 ORACLE 錯誤 904
ORA-00904: “MAXSIZE”: invalid identifier
EXP-00008: 遇到 ORACLE 錯誤 942
ORA-00942: table or view does not exist
EXP-00024: 未安裝匯出視圖, 請通知您的 DBA
EXP-00000: 匯出終止失敗

 

很詭異，從來沒見過的錯誤，怎麼會報沒有欄位的錯誤呢。

於是Google（在技術資料方面BAIDU就是垃圾），

有個哥們說是資料字典丟了，我很詫異，我前幾天在Oracle 10g Client下匯出過了，怎麼會遺失資料字典，還是半信半疑的執行了。

在伺服器上執行

oracle@linux-lrn6:~> sqlplus sys/sys@orcl as sysdba

SQL>@/opt/oracle/product/10.2/db_1/rdbms/admin/catexp.sql

以重建資料字典。

 

建好了後，再次執行匯出，還是不行。

但是我在伺服器上執行同樣的語句就可以。

於是我意識到可能是EXP版本問題，於是測試。

 

在一台11g的機器上運行一條SQL檢測那個MAXSIZE是個什麼列。

SQL> select distinct table_name from user_col_comments where column_name=’MAXSIZE’;

TABLE_NAME
————————————————————
EXU9STOU
EXU9PLB
EXU9PDSU
EXU9LBCPU
EXU9PDS
EXU9IXCP
CPOOL$
EXU9TBCPU
EXU9TBCP
EXU9STO
EXU9IXCPU

TABLE_NAME
————————————————————
DBA_CPOOL_INFO
EXU9TBS
EXU9PLBU
EXU9LBCP

 

然後在10g的伺服器上運行同樣的語句，沒有返回行。可見MAXSIZE是11g在資料字典中新加的項，10g是沒有的。

 

#10g
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> desc sys.exu9tbs
名稱                                      是否為空白? 類型
—————————————– ——– —————————-
ID                                        NOT NULL NUMBER
OWNER                                                CHAR(6)
NAME                                   NOT NULL VARCHAR2(30)
ISONLINE                                              VARCHAR2(7)
CONTENT                                              VARCHAR2(9)
INIEXT                                  NOT NULL NUMBER
SEXT                                    NOT NULL NUMBER
PCTINC                                 NOT NULL NUMBER
MINEXT                                 NOT NULL NUMBER
MAXEXT                                NOT NULL NUMBER
MINLEN                                                 NUMBER
DEFLOG                                 NOT NULL NUMBER
EXT_MGT                               NOT NULL NUMBER
ALLOC_TYPE                          NOT NULL NUMBER
BLOCKSIZE                            NOT NULL NUMBER

#11g
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP and Real Application Testing options

SQL> desc sys.exu9tbs
名稱                                      是否為空白? 類型
—————————————– ——– ————————-
ID                                         NOT NULL NUMBER
OWNER                                                 CHAR(6)
NAME                                    NOT NULL VARCHAR2(30)
ISONLINE                                              VARCHAR2(7)
CONTENT                                              VARCHAR2(9)
INIEXT                                   NOT NULL NUMBER
SEXT                                     NOT NULL NUMBER
PCTINC                                  NOT NULL NUMBER
MINEXT                                  NOT NULL NUMBER
MAXEXT                                 NOT NULL NUMBER
MINLEN                                                  NUMBER
DEFLOG                                  NOT NULL NUMBER
EXT_MGT                                NOT NULL NUMBER
ALLOC_TYPE                            NOT NULL NUMBER
BLOCKSIZE                             NOT NULL  NUMBER
MAXSIZE                                                 NUMBER ———>問題在這裡

 

所以，匯入匯出資料的時候，還是儘可能的版本統一，10g導10g的，11g導11g的，免得不必要的麻煩。

 

好了，言歸正傳，分析DMP格式。

DMP是什麼詭異格式？就是上面Oracle的EXP工具匯出的備份檔案的格式。

某次腦子發燒用gVim開啟DMP猛然發現它就是一些SQL，令我非常詫異，於是發現了一些詭異的修改備份的方法，就是直接修改DMP檔案。

 

下面我開啟一個新鮮匯出的備份檔案，分析一下DMP的結構。

首先一開啟DMP，最上面有幾行這樣的東西：

DPLX
RUSERS
8192
0
20
0

下面那些數字不要管，資料庫的一些參數，DPLX就是匯出的使用者名稱，D是標識，PLX是使用者名稱，EUSERS是預設資料表空間，E是標識，USERS是資料表空間。

繼續往下看，能看到這樣的語句：

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT(’USERENV’,'CURRENT_SCHEMA’), export_db_name=>’ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM’, inst_scn=>’5603910′);

這個是建立模式的語句，原則上每個使用者就是一個模式，Schema。

這上面這些東西，最好都不要改，改了要出事的，一般匯入就不行了，會失效，因為DMP雖然大部分是明文SQL，但是也有一部分是二進位的，改了這裡會影響校正。

再往後看就有建表語句了，

CREATE TABLE “C” (”CNO” NUMBER(10, 0) NOT NULL ENABLE, “CNAME” VARCHAR2(20) NOT
NULL ENABLE, “CPNO” NUMBER(10, 0), “CCREDIT” NUMBER(3, 0) NOT NULL ENABLE)  PCTF
REE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREE
LIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE “USERS” LOGGING NOCOMPRESS

有時候匯出的資料表空間混亂，就可以在黃色底色的那裡改，替換成想要的資料表空間。

然後一個表下面就是插入資料的語句了：

INSERT INTO “C” (”CNO”, “CNAME”, “CPNO”, “CCREDIT”) VALUES (:1, :2, :3, :4)
再往下就是一堆二進位，就是要插入的資料，那個就改不了了，Oracle沒有公開格式。

不過知道這些已經足以解決匯入中的很多問題，尤其是資料表空間混亂的問題。

某大飛機資料庫就非常混亂，一個ARJ使用者模式（我沒有泄密，公開的）下有來自ARJ,M3,USERS,SYSTEM等資料表空間的表，就是匯入的時候方法不正確。然後用上篇Oracle日誌中取消許可權的方法，也會有些表導不進去，這是Oracle的BUG，但是我這樣改了以後，匯入後想在哪個資料表空間就哪個資料表空間。

用這種方法修改了某大飛機的兩個資料庫匯出檔案，匯入到同一個使用者模式下了，資料表空間全部統一，資料沒有丟失，匯入處理程序完全沒用警告。