exp时遇到EXP-00008&ORA-06550&ORA-00904的解决

exp时遇到EXP-00008&ORA-06550&ORA-00904的解决
现象：exp时报错，如下：
[oracle@cicgo1 oracle]$ exp system/pass owner=cicgo file=/tmp/cicgo.dmp

Export: Release 8.1.7.4.0 - Production on Tue Apr 23 15:07:17 2013

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and ZHS16GBK NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 13:
PLS-00201: identifier 'ORDSYS.ORDTEXP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling ORDSYS.ORDTEXP.schema_info_exp
. exporting foreign function library names for user cicgo
. exporting object type definitions for user cicgo
About to export cicgo's objects ...
. exporting database links
. exporting sequence numbers
EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully

分析：
1.经查看数据库中没有无效对象：
SQL> SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects
WHERE status != 'VALID'
ORDER BY 4,2; 2 3 4

2.根据文档Full Database Export Fails with EXP-00008: ORACLE error 6550 encountered (Doc ID 120540.1)的描述：
fact: Oracle Server - Enterprise Edition 8
fact: Oracle Server - Enterprise Edition 9
fact: Export Utility (EXP)
symptom: Full database export fails
symptom: EXP-00008: ORACLE error 6550 encountered
symptom: PLS-00201: identifier 'ORDSYS.ORDTEXP' must be declared
symptom: EXP-00083: The previous problem occurred when calling ORDSYS.
ORDTEXP.schema_info_exp
cause: User ORDSYS has been dropped. If Time Series option was installed,
it's objects are dropped, but the import/export support for the option is not.
<====该处说明造成exp失败原因为：用户ORDSYS被删除了，单Oracle字典表exppkgact$的信息却没有被更新到一致，因此造成exp时失败
据此推测造成这种现象有2种根本原因：
1.Oracle的BUG导致drop user时字典表exppkgact$未更新；
2.用户使用不正确的方式删除了用户。
fix:
Drop import/export support for Time Series
------------------------------------------
delete from sys.exppkgact$
where package = 'ORDTEXP' and
schema = 'ORDSYS'; <===此处说明了解决办法：delete掉exppkgact$中不一致的记录

commit;

按照Oracle提供的办法delete了字典表exppkgact$的相关记录：
SQL> delete from sys.exppkgact$
where package = 'ORDTEXP' and
schema = 'ORDSYS'; 2 3

1 row deleted.

SQL> commit;
Commit complete.
但执行exp时仍然报错：
[oracle@cicgo1 scripts]$ exp system/pass owner=cicgo file=/tmp/cicgo.dmp direct=y

Export: Release 8.1.7.4.0 - Production on Tue Apr 23 16:00:42 2013

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user cicgo
. exporting object type definitions for user cicgo
About to export cicgo's objects ...
. exporting database links
. exporting sequence numbers
EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully
[oracle@cicgo1 scripts]$
发现这次未再报错ORA-06550了，只报了ORA-00904: invalid column name，到底是内部的什么SQL导致ORA-00904了，我使用了errorstack trace：
1.alter system set events '904 trace name ERRORSTACK level 3';
2.再次执行exp
3.alter system set events '904 trace name context off';
4.查看生成的trace文件：
/home/oracle/data/app/oracle/admin/cicgo/udump/ora_5579.trc

Oracle8i Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /home/oracle/data/app/oracle/product/8.1.7/
System name: Linux
Node name: cicgo1
Release: 2.4.18-3
Version: #1 Thu Apr 18 07:37:53 EDT 2002
Machine: i686
Instance name: cicgo
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 5579, image: oracle@cicgo1 (TNS V1-V3)

*** SESSION ID:(14.23243) 2013-04-24 09:31:40.273
*** 2013-04-24 09:31:40.273
ksedmp: internal or fatal error
ORA-00904: invalid column name
Current SQL statement for this session:
SELECT GRANTOR, GRANTORID, GRANTEE, PRIV, WGO, ISDIR, TYPE FROM SYS.EXU8GRN WHERE OBJID = :1 ORDER BY WGO DESC, SEQUENCE
<==== 发现Oracle的视图EXU8GRN缺少列，一致exp时内部SQL报错，于是重建了EXU8GRN视图并授权。
Please try below:

1. svrmgrl
svrmgrl>connect internal
svrmgrl>@catexp.sql
svrmgrl>CREATE OR REPLACE view exu8grn (objid, grantor, grantorid, grantee,
priv, wgo,
creatorid, sequence, isdir, type) AS
SELECT t$.obj#, ur$.name, t$.grantor#, ue$.name,
m$.name, NVL(t$.option$,0), o$.owner#, t$.sequence#,
DECODE ( (o$.type#), 23, 1, 0 ), /* flag if directory alias */
o$.type#
FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$, sys.user$ ue$
WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND
t$.col# IS NULL AND t$.grantor# = ur$.user# AND
t$.grantee# = ue$.user# AND
ue$.name not in ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS',
'LBACSYS')
/
grant select on exu8grn to select_catalog_role
/
再次运行exp时可以成功导出。