用errorstack event來定位exp導出時direct=y參數報錯的問題

 

1.exp導出時加上direct=y參數出錯，顯示ora-00942錯誤顯示表格不存在，經檢查表格是存在的。

 EXP-00056:

ORA-00942:

SQL> conn amy/amy@test129

已連線.

SQL> select count(*) from class_info;

 COUNT(*)

----------

        6

 

2.用errorstack event來定位問題

打開errorstack

 SQL>alter system set events '942 trace name errorstack level 1';

 

再執行報錯的exp

 

關閉errorstack

SQL>alter system set events'942 trace name errorstack off';

 

3．查看警告日志文件

[oracle@MESTEST bdump]$ tail -n 10 alert_mes.log

Sat Nov 20 15:37:22 2010

OS Pid: 13391 executed alter system set events '942 trace name errorstack level 1'

Sat Nov 20 15:37:28 2010

Errors in file /opt/oracle/admin/mes/udump/mes_ora_13403.trc:

ORA-00942: table or view does not exist

OS Pid: 13405 executed alter system set events '942 trace name errorstack off'

 

4.檢查警告日志中出現的trace文件，發現是有系統表SYS.EXPCOMPRESSEDTAB不存在導致的，

[oracle@MESTEST bdump]$ more /opt/oracle/admin/mes/udump/mes_ora_13403.trc

 

*** SESSION ID:(74.48444) 2010-11-20 15:37:27.982

*** 2010-11-20 15:37:27.982

ksedmp: internal or fatal error

ORA-00942: table or view does not exist

Current SQL statement for this session:

SELECT COUNT(*) FROM SYS.EXPCOMPRESSEDTAB WHERE TOBJ$ = :1

 

5.查找/opt/oracle/9205/rdbms/admin /catexp.sql腳本中關於SYS.EXPCOMPRESSEDTAB的腳本並執行。

REM

REM View to see if a table is compressed (new for 10i).

REM

CREATE OR REPLACE VIEW sys.expcompressedtab

   (SPARE1, TOBJ$) AS

       SELECT s.spare1, t.obj#

       FROM  sys.tab$ t, sys.seg$ s

       WHERE t.ts#   = s.ts#

       AND   t.file# = s.file#

       AND   t.block# = s.block#

       AND   s.type# = 5

       AND   (bitand(s.spare1,4096) = 4096 OR bitand(s.spare1,2048) = 2048)

/

GRANT SELECT ON sys.expcompressedtab TO PUBLIC;

 

6.再執行帶direct=y的導出，錯誤消失了，可以正常導出。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9902302/viewspace-711928/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9902302/viewspace-711928/