今天碰到一个很有意思的错误，通过JAVA程序连接数据库，运行程序时报错，错误信息就是ORA-00942: table or view does not exist。
找到导致问题的最终原因，并验证解决方法。
一次ORA-942错误的跟踪（一）：http://yangtingkun.itpub.net/post/468/480559
一次ORA-942错误的跟踪（二）：http://yangtingkun.itpub.net/post/468/480671
一次ORA-942错误的跟踪（三）：http://yangtingkun.itpub.net/post/468/480744
根据上一篇文章的分析结果，问题与同义词有关，还与TJSQ_TRADE的模板原型用户TJSQ_TRADE用户有关。
现在回忆一下整个事件的过程：在创建了BJSQ_NDMAIN和BJSQ_TRADE用户之后，利用数据泵导出了BJSQ_NDMAIN和BJSQ_TRADE用户，并利用数据泵的REMAP_SCHEMA的方式，导入了TJSQ_NDMAIN和TJSQ_TRADE用户。
其中导入TJSQ_TRADE用户对应的log文件内容如下：
[oracle@yans2 dmp]$ more imp_trade_20090316.log
;;;
Import: Release 10.2.0.3.0 - 64bit Production on Monday, 16 March, 2009 14:30:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TJSQ_TRADE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
ORA-39060: table(s) dropped because of conflict with master table
Starting "TJSQ_TRADE"."SYS_IMPORT_FULL_01": tjsq_trade/******** directory=d_dmpdp dumpfile=bjsq_trade_product_090316.dp logfile=imp
_trade_20090316.log remap_schema=bjsq_trade:tjsq_trade remap_tablespace=bjsq_trade:tjsq exclude=grant exclude=table_statistics
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
ORA-31685: Object type SYNONYM:"TJSQ_TRADE"."A_DATA" failed due to insufficient privileges. Failing sql is:
CREATE SYNONYM "TJSQ_TRADE"."A_DATA" FOR "BJSQ_NDMAIN"."A_DATA"
ORA-31685: Object type SYNONYM:"TJSQ_TRADE"."CAT_ADMIN" failed due to insufficient privileges. Failing sql is:
CREATE SYNONYM "TJSQ_TRADE"."CAT_ADMIN" FOR "BJSQ_NDMAIN"."CAT_ADMIN"
ORA-31685: Object type SYNONYM:"TJSQ_TRADE"."CAT_AGENT" failed due to insufficient privileges. Failing sql is:
CREATE SYNONYM "TJSQ_TRADE"."CAT_AGENT" FOR "BJSQ_NDMAIN"."CAT_AGENT"
ORA-31685: Object type SYNONYM:"TJSQ_TRADE"."CAT_AREA_MEDICARE" failed due to insufficient privileges. Failing sql is:
.
.
.
ORA-31685: Object type SYNONYM:"TJSQ_TRADE"."CAT_AUTH_PATENT_DRUG" failed due to insufficient privileges. Failing sql is:
CREATE SYNONYM "TJSQ_TRADE"."CAT_AUTH_PATENT_DRUG" FOR "BJSQ_NDMAIN"."CAT_AUTH_PATENT_DRUG"
不过由于导入操作是同事执行的，且这个同事刚刚接触数据泵时间不长，因此没有采用最简单的方式，将两个用户放在一起导出、导入。本来单独导入也没有问题，在导入TJSQ_TRADE的时候指定两个REMAP_SCHEMA一样可以将同义词正确的导入到TJSQ_TRADE用户中，并指向TJSQ_NDMAIN用户的对象。不过由于同事不了解这一点，又怕同义词导入出现错误，因此没有给用户授予创建同义词的权限。其实这里完全可以将同义词导入，即使指向的用户是错误的也没有关系。因为CREATE OR REPLACE方式比直接建立一个新的同义词要方便一些。退一步讲，就算是不打算导入同义词，也可以简单的指定EXCLUDE=SYNONYM，而不需要通过限制权限的方式让导入报错。当然，这个同事不是专业的DBA，能处理成这样也算难能可贵了。
不过随后他在处理手工处理同义词的时候犯了一个错误。第一次手工处理同义词的时候，TJSQ_TRADE的同义词仍然指向了BJSQ_NDMAIN用户的对象，而不是指向了TJSQ_NDMAIN用户。随后直接启动了JAVA程序，显然由于缺少授权，将会报错，而这个错误就是ORA-942错误。
$ sqlplus tjsq_trade
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 18 17:43:38 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from dba_objects;
select * from dba_objects
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select owner, object_name, object_type
2 from all_objects
3 where object_name = 'DBA_OBJECTS';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC DBA_OBJECTS SYNONYM
DBA_OBJECTS是PUBLIC的同义词，显然这个对象是存在的，而普通用户访问这个对象并不会报错没有权限，而是表和视图不存在。
而显然这时共享池中的SQL语句指向的是BJSQ_NDMAIN用户，由于访问的对象和BJSQ_TRADE用户通过同样的SQL访问的对象一样，所以Oracle在共享池中Oracle打算利用了BJSQ_TRADE用户已经生成的SQL分析。这就是为什么两个用户的相同SQL的FIRST_LOAD_TIME时间是一致的。
但是随后TJSQ_TRADE重建了同义词后，Oracle这里出现了bug，没有将共享池中的SQL置为INVALID状态，随后访问的SQL仍然沿用前面的这个SQL分析，因此仍然出现了ORA-942错误，虽然这时同义词已经指向了正确的对象。
而所有SQL的变形，只要不匹配这个共享池中错误的SQL，都可以得到正确的结果。
简单总结一下，由于当前用户通过同义词访问对象的SQL，匹配了另一个用户通过同义词访问另一个用户对象的SQL，因此Oracle利用以前的SQL的分析，但是由于当前用户确实权限，导致这个SQL分析的结果是报错ORA-942：表或视图不存在。但是随后当前用户重建同义词，并指向一个拥有访问权限的用户时，共享池中已有的SQL并没有被丢弃，于是再次发出和刚才相同的SQL，就是导致ORA-942错误。
根据这个现象，把ORA-942和SHARED_POOL作为关键字在METALINK中查询，结果还真的找到了关于这个bug的描述：Bug No. 6155720。对比错误信息，错误描述，以及Oracle给出的解释，和当前碰到的情况完全一样。
检查Oracle确认能碰到这个问题的列表，就包括10.2.0.3 for linux环境，而这正是当前数据库的版本。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
下面尝试刷新共享池：
SQL> CONN TJSQ_TRADE
Enter password:
Connected.
SQL> select * from usr_module ;
select * from usr_module
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> CONN TJSQ_TRADE
Enter password:
Connected.
SQL> SET AUTOT TRACE
SQL> select * from usr_module ;
636 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 130732051
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 636 | 51516 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| USR_MODULE | 636 | 51516 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
694 recursive calls
0 db block gets
188 consistent gets
0 physical reads
0 redo size
56906 bytes sent via SQL*Net to client
954 bytes received via SQL*Net from client
44 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
636 rows processed
至此，所有导致问题的原因已经彻底搞清楚了，解决问题的方法也得到了验证。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-573325/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-573325/
697
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
