[20200312]ORA-00904 POLTYP invalid identifier.txt

[20200312]ORA-00904 POLTYP invalid identifier.txt

--//10g的資料庫使用11g的exp版本匯出，遇到如下錯誤：
--//連結：http://www.itpub.net/thread-2129145-1-1.html
--//EXP-00008: ORACLE error 904 encountered
--//ORA-00904: "POLTYP": invalid identifier
--//EXP-00000: Export terminated unsuccessfully

--//重複演示與分析：

1.環境：
SYS@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

2.測試：
--//使用11g的exp匯出：
$ exp scott/xxxx@192.168.xxxx.yy:1521/test tables=dept BUFFER=10240000 file=dept.emp direct=Y
Export: Release 11.2.0.4.0 - Production on Thu Mar 12 09:13:34 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
EXP-00008: ORACLE error 904 encountered
ORA-00904: "POLTYP": invalid identifier
EXP-00000: Export terminated unsuccessfully

$ strings $(which exp) | grep -i POLTYP
SELECT POLGRP, POLTYP, POLICY, POLOWN, POLSCH, POLFUN, STMT, CHKOPT,             ENABLED, SPOLICY      FROM   SYS.EXU9RLS      WHERE  OBJOWN = :1 AND             OBJNAM = :2
--//你可以發現11g的exp訪問SYS.EXU9RLS時有1個POLTYP欄位。

SYS@test> @ &r/desc SYS.EXU9RLS
Name       Null?    Type
---------- -------- -------------
OBJOWN     NOT NULL VARCHAR2(30)
OBJNAM     NOT NULL VARCHAR2(30)
POLGRP     NOT NULL VARCHAR2(30)
POLICY     NOT NULL VARCHAR2(30)
POLOWN     NOT NULL VARCHAR2(30)
POLSCH              VARCHAR2(30)
POLFUN     NOT NULL VARCHAR2(30)
STMT                VARCHAR2(28)
CHKOPT     NOT NULL NUMBER
ENABLED    NOT NULL NUMBER
SPOLICY             NUMBER

--//你可以發現10g的資料庫SYS.EXU9RLS表沒有POLTYP欄位。而11g資料庫是有的：
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book>  @ &r/desc SYS.EXU9RLS
Name       Null?    Type
---------- -------- ----------------------------
OBJOWN     NOT NULL VARCHAR2(30)
OBJNAM     NOT NULL VARCHAR2(30)
POLGRP     NOT NULL VARCHAR2(30)
POLICY     NOT NULL VARCHAR2(30)
POLOWN     NOT NULL VARCHAR2(30)
POLSCH              VARCHAR2(30)
POLFUN     NOT NULL VARCHAR2(30)
STMT                VARCHAR2(34)
CHKOPT     NOT NULL NUMBER
ENABLED    NOT NULL NUMBER
SPOLICY             NUMBER
POLTYP              VARCHAR2(33)

--//對比前面，可以發現後面多了1個欄位POLTYP，所以11g的exp無法匯出10g資料庫的表。
--//最佳的方式使用10g自帶的exp版本。有一些匯出檔案太大，磁碟空間不足。你可以使用exp imp on th fly，一邊匯出一邊壓縮。
--//連結:http://blog.itpub.net/267265/viewspace-2644894/

$ exp scott/xxxx file=>(gzip>dept.dmp.gz)  tables=dept buffer=8388608
Export: Release 10.2.0.4.0 - Production on Thu Mar 12 09:21:17 2020
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
Export terminated successfully without warnings.

$ ls -l dept.dmp.gz
-rw-r--r-- 1 oracle oinstall 1225 2020-03-12 09:21:17 dept.dmp.gz