sqlplus登录报ORA-06502错误的问题排查和解决

最近碰见了一个sqlplus登陆报错的问题，我一开始排查问题的方向就错了，不得已，还是请教了dbsnake大师，对于这么一个小问题，就像“小罗的不看人传球”般解决了问题，有因才有果，对于任何事情都成立。当然，我也不是第一次受挫了，归根结底，还是碰见的问题少，对于一些基础原则性知识掌握不够，不能从现象中直接看出本质，导致了方向性上的错误，急也没用，慢慢积累吧，所以还是要总结一下，避免下次再犯同样的错误。

问题描述： 
一个11.2.0.4的开发库，用户名是sqlreviewer，密码是sqlreviewer，之前一直可以用，最近出现sqlplus sqlreviewer/sqlreviewer的时候，报错： 

关于ORA-06502的描述： 

描述的是PLSQL块中，因为实际值超过了变量定义的长度，所以报了这个错。

如果使用错误的密码sqlreviewea，报错： 

用户口令校验的正常错误。

问题到这里，可能有的朋友已经猜到大致的方向了，但我开始排查的方向就出了错误，我尝试用strace查看sqlplus的执行，然并卵，尝试创建使用同样位数的用户sqlreviewea，执行sqlplus登录是正常的，

SQL> create user sqlreviewea identified by abc;
SQL> grant dba to sqlreviewea;

问题解决： 
1.sqlplus登录报PLSQL赋值变量错误，需要排查是否库设置了logon trigger。 
方法1：GC中triggers视图查找Event是LOGON的记录 
这里可以看见有一个trigger名称是LOG_DEFERRED的触发器： 

方法2： 

注意使用length(triggering_event)查看实际位数是6，即’LOGON ‘，右侧多一个空格，所以需要rtrim操作。

2.查看logon trigger做了什么。 
方法1：GC中 
 
方法2： 

这才想起来，为了解决一个用户权限的问题(http://blog.itpub.net/7192724/viewspace-2120330/)，上次特意为这个库增加了一个logon trigger，判断登录的若是某个特定用户，则session级关闭延迟段分配属性，其中用户名的变量logon_user定义为VARCHAR2(10)，显然sqlreviewer用户的名称长度超过了10位，这就能解释通sqlplus sqlreviewer/sqlreviewer报一个PLSQL错误的原因了。至于使用错误的密码报ORA-01017，很好解释，先进行了用户口令验证，未通过则报这个错，如果通过了，就会执行logon trigger的逻辑，因此报了另一个错误。

3.还有一问题，就是为何尝试创建使用同样位数的用户sqlreviewea，执行sqlplus登录是正常的，

SQL> create user sqlreviewea identified by abc;
SQL> grant dba to sqlreviewea;

问题就出在了将DBA权限授予了sqlreviewea用户。 
《ADMINISTER DATABASE TRIGGER Privilege Causes Logon Trigger to Skip Errors (文档 ID 265012.1)》这篇文章明确指出了： 
具有ADMINISTER DATABASE TRIGGER系统权限的用户sqlplus登录即使出现logon trigger报错，仍可以连接，不会阻止登录，但是这错误会记录在alert.log和trace文件中。 
查看alert.log日志，确实有这个错误的记录： 

查看trace日志，问题更清晰了，直接有一句：

Skipped error 604 during the execution of SYS.LOG_DEFERRED

就说明了其跳过这个trigger执行的报错。 

注：这用的trigger是database logon trigger，对应的是使用ADMINISTER DATABASE TRIGGER系统权限可跳过报错。如果是schema logon trigger，则对应是ALTER ANY TIGGER权限，效果相同。 
另外，就是以下用户和角色是有ADMINISTER DATABASE TRIGGER权限的，这就解释了为何具有dba权限的sqlreviewea用户登录sqlplus不会显示报错的原因。 

总结： 
1.对于基础理论的掌握熟练程度和敏感度，往往对排查问题的方向起到了至关重要的作用，例如出现了ORA-06502的PLSQL报错，是否就会联系到logon trigger，或者是否知道什么是logon trigger。一方面要持续吸收知识，更要理解知识，另一方面就要多碰问题，“本不知道这些问题，碰见的多了自然就知道了”，顺其自然，强求不得。 
2.MOS是提供了很好、很权威的问题排查途径，但要能用好，例如这块我用logon trigger查了未找到对应的，再看才发现未切换至英文，一定程度上看，英文资料还是比中文资料广而多。 
3.有因才有果，凡事都适合，不要因为问题小就不重视，对于我来说，任何小问题都是积累的重要一环，既然天分不足，只能慢慢积累，要耐得住。

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

转载于:http://blog.itpub.net/7192724/viewspace-2122583/