Troubleshooting of ORA-06502 errors reported by sqlplus Logon

Troubleshooting of ORA-06502 errors reported by sqlplus Logon

I recently encountered a sqlplus login error. I was wrong at the beginning of troubleshooting. I had to ask Master dbsnake for such a small problem, it solves the problem like "Mr. Luo does not look at people to pass the ball". It is only possible for reasons, and it is true for everything. Of course, I was not frustrated for the first time. In the final analysis, I still encountered few problems. I did not have enough knowledge of some basic principles and could not see the essence from the phenomenon directly, leading to a wrong direction, it's useless if you are in a hurry. Let's make a summary to avoid making the same mistake next time.

Problem description:
A development library of 11.2.0.4 with the username sqlreviewer and password sqlreviewer, which can be used all the time before. When sqlplus sqlreviewer/sqlreviewer appears recently, an error is returned:

Description of the ORA-06502:

This error is reported in PLSQL blocks because the actual value exceeds the length defined by the variable.

If the incorrect password sqlreviewea is used, an error is returned:

A normal error occurred during password verification.

Here, some friends may have guessed the general direction, but an error occurred when I started troubleshooting. I tried to use strace to view the execution of sqlplus, if you try to create a user sqlreviewea with the same number of digits, it is normal to execute sqlplus logon,

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

Solution:
1. sqlplus logon reports PLSQL assignment variable error. Check whether logon trigger is set in the database.
Method 1: Search the triggers view in GC for records whose Event is LOGON.
Here we can see a trigger named LOG_DEFERRED:

Method 2:

Note: use length (triggering_event) to check that the actual number of digits is 6, that is, 'logon'. There is one more space on the right side, so you need to perform the rtrim operation.

2. Check what logon trigger has done.
Method 1: GC

Method 2:

This reminds me that in order to solve the problem of a user permission (), a logon trigger was specially added to the library last time to determine if a specific user is logged on, the session-level function is used to disable the delay segment allocation attribute. The username variable logon_user is defined as VARCHAR2 (10). It is clear that the sqlreviewer user name length exceeds 10 characters, this explains why a PLSQL error is reported through sqlplus sqlreviewer/sqlreviewer. As for the use of the wrong password to report the ORA-01017, a good explanation, the first line of the user password verification, if not passed, this error is reported, if it passes, it will execute the logon trigger logic, therefore, another error is reported.

3. Another question is why it is normal to execute sqlplus logon to create a user named sqlreviewea with the same number of digits,

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

The problem is that the DBA permission is granted to the sqlreviewea user.
This article administer database trigger Privilege Causes Logon Trigger to Skip Errors (Document ID 265012.1) clearly states:
Sqlplus logon, a user with administrative database trigger system permissions, can still be connected even if logon trigger reports an error and does not prevent logon. However, this error is recorded in the alert. log and trace files.
Check the alert. log. There is indeed a record of this error:

To check the trace log, the problem is clearer. There is a direct sentence:

Skipped error 604 during the execution of SYS.LOG_DEFERRED

The error message returned when the trigger is skipped.

Note: The trigger used here is the database logon trigger, which corresponds to skipping the error reporting by using the administer database trigger system permission. If it is schema logon trigger, it corresponds to the alter any tigger permission, and the effect is the same.
In addition, the following users and roles have the administer database trigger permission, which explains why sqlreviewea users with dba permission log on to sqlplus will not report an error.

Summary:
1. for the basic theory to master the degree of proficiency and sensitivity, often plays a crucial role in the direction of troubleshooting, such as the emergence of ORA-06502 PLSQL errors, will be linked to the logon trigger, or do you know what logon trigger is. On the one hand, we must continue to absorb knowledge and understand knowledge. On the other hand, we must encounter more problems. "If we don't know these problems, we will naturally know what we encounter." let it go with the flow, so we can't force it.
2. MOS provides a very good and authoritative troubleshooting method, but it must be well used. For example, I used logon trigger to check whether the corresponding one was found, and then I found that the problem was not switched to English, to some extent, there are more and more English documents than Chinese documents.
3. if there is reason for success, everything is suitable. Don't pay less attention to problems. For me, any small problem is an important part of accumulation. Since there is not enough talent, I can only accumulate slowly and have to endure it.