ORA-06502: character string buffer too small while re/creating dbconsole 18 December 2009Posted by David Alejo Marcos in Enterprise Manager, Grid Control, SQL - PL/SQL.
Tags: Enterprise Manager, Grid Control, SQL - PL/SQL
we are getting close to the end of this year. This may be the last post for 2009, we will have to think about next year…
OK, the last thing we faced here was the following error while re-creating the dbconsole using the standard emca -config dbcontrol db -repos recreate command:
CONFIG: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 259
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 259
followed for some nasty Java alerts and:
SEVERE: Error creating the repository
oracle.sysman.emcp.exception.EMConfigException: Error creating the repository
Well, the first thing to check is that you are on the right ORACLE_HOME, this is not going to solve the problem, but it will help us to modify the script on the right ORACLE_HOME the first time.
The problem is located on a variable used to store the name of the host.
Normally this variable is defined for 32 characters. This is normally right for most systems, but we had a problem with one of ours as the domain of the servers plus the name of the database had a length of 33 characters.
How can we check the length of our host_name?, quite simple:
SYS@XXXXXXX> col host_name for a40
SYS@databee> select host_name, length(host_name) length from v$instance;
I had to change the names for security reasons.
Ok, the problem is down to a single character, the length is 33 characters and the variable has been defined as:
so it can only store 32.
the fix is quite simple.
1.- Check you are on the right ORACLE_HOME
2.- cd $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor
3.- cp self_monitor_post_creation.sql self_monitor_post_creation.sql_bk
4.- Edit self_monitor_post_creation.sql and replace
for a length that can store your host_name
NOTE.- there are two places where this variable is defined on the script, remember to modify the definition on both places.
Once the script has been changed, you can rerun the command to re/create the dbconsole:
emca -config dbcontrol db -repos recreate
As always, comments are welcome.