Help - Search - Members - Calendar
Full Version: ORA-12899: value too large for column MSG
Oracle DBA Forums > Oracle > Oracle Forum
sbusztin
1. When querying the "alert_log" table I created from the alert log using the script below, 2 new files were created ALERT_LOG_30499.bad and ALERT_LOG_30499.log.

The ALERT_LOG_30499.log. contains this error message:

error processing column MSG in row 2910 for datafile /u02/damistst/admin/bdump/alert_damistst.log
ORA-12899: value too large for column MSG (actual: 82, maximum: 80)

the ALERT_LOG_30499.bad , so far, only contains datafile resize information. The datafiles have plenty of space and there is plenty of space on the San slice the datafiles reside.


2. then each time I recreate the table and increased the increased the varchar2 size, the "actual" size will also increase in the log file.

error processing column MSG in row 2910 for datafile /u02/damistst/admin/bdump/alert_damistst.log
ORA-12899: value too large for column MSG (actual: 92, maximum: 90)




3. When I increased the varchar2 size to 120+ it gave me this error message:


[oracle@tds_dw bdump]$ cat ALERT_LOG_30715.log


LOG file opened at 03/09/11 14:46:20

Field Definitions for table ALERT_LOG
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

MSG CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader




TABLE DDL:

create table
alert_log ( msg varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_damistst.log')
)
reject limit 1000;




**** QUESTION
I can still query the alert_log table in sqlplus, but those log and bad files are generated, is this an issue?


example of a piece of the results from " select * from alert_log; "

MSG
--------------------------------------------------------------------------------
Thread 1 advanced to log sequence 5254 (LGWR switch)
Current log# 1 seq# 5254 mem# 0: /tds_oradata/redo01a.log
Current log# 1 seq# 5254 mem# 1: /u02/damistst/REDO_LOGS/redo01b.log
Thread 1 cannot allocate new log
Checkpoint not complete
Current log# 1 seq# 5254 mem# 0: /tds_oradata/redo01a.log
Current log# 1 seq# 5254 mem# 1: /u02/damistst/REDO_LOGS/redo01b.log
Wed Mar 9 14:33:09 2011
Thread 1 advanced to log sequence 5255 (LGWR switch)
Current log# 2 seq# 5255 mem# 0: /tds_oradata/redo02a.log
Current log# 2 seq# 5255 mem# 1: /u02/damistst/REDO_LOGS/redo02b.log

13076 rows selected.
RHR
General information on the ORA-12899 error is available here:

http://www.dba-oracle.com/t_ora_12899_valu..._for_column.htm

Perhaps our experts will be able to help you further.

RHR
sbusztin
QUOTE (RHR @ Mar 9 2011, 04:13 PM) *
General information on the ORA-12899 error is available here:

http://www.dba-oracle.com/t_ora_12899_valu..._for_column.htm

Perhaps our experts will be able to help you further.

RHR



Thanks for your feedback RHR. Ill post the fix if that ends up helping us resolve the issue.
burleson
Hi Busztin,

First, see working examples here for using the alert log as an external table:

http://www.dba-oracle.com/t_oracle_alert_l...rnal_tables.htm

Here is how I monitor my alert logs, a bit more sophistoicated than your method:

http://www.dba-oracle.com/t_script_monitor_alert_log.htm

*******************************************
>> ORA-12899: value too large for column MSG (actual: 82, maximum: 80)

Obviously, your external table definition shows the table as too small!

CODE
create table
alert_log ( msg varchar2(80) )
organization external (


change to:

CODE
create table
alert_log ( msg varchar2(100) )
organization external (


This should fix it!!!!

sbusztin
QUOTE (burleson @ Mar 11 2011, 09:00 AM) *
Obviously, your external table definition shows the table as too small!

CODE
create table
alert_log ( msg varchar2(80) )
organization external (


change to:

CODE
create table
alert_log ( msg varchar2(100) )   <--- I already tried this yesterday  (see my post above).. I tried all numbers at and above the length of text representation of the longest column. None worked. They all say it is either too large or too small.

SQL> select data_length from dba_tab_columns where table_name='ALERT_LOG';

DATA_LENGTH
-----------
        113

Ive tried: alert_log ( msg varchar2(100) )
alert_log ( msg varchar2(112) )
alert_log ( msg varchar2(113) )
alert_log ( msg varchar2(114) )

at 113 it says its too small
at 114 it says its too large

Any other suggestions?


organization external (


This should fix it!!!!

sbusztin
If too small, it says this:

error processing column MSG in row 2910 for datafile /u02/damistst/admin/bdump/alert_damistst.log
ORA-12899: value too large for column MSG (actual: 92, maximum: 90)





If too large:

LOG file opened at 03/09/11 14:46:20

Field Definitions for table ALERT_LOG
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

MSG CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
burleson
Hi,

>> MSG in row 2910

It's IMPORTANT to inspect this row in the alert log file and see why it is different!

*******************************************
Google also shows similar issues:

http://www.google.com/search?&q=%22ale...og%22+ORA-12899

A Google search for similar errors reveals several possible solutions:

http://www.google.com/search?&q=%22ext...le%22+ORA-12899

Several solutions involve checking your "terminated by" and enclosed by" parameters.


**************************************
>> Terminated by ","

Aha!

Check terminated by "," in the row where the alert log job aborted

Try removing your "optionally enclosed" clause:

QUOTE
By Chris Stephens:

The problem ended up being the 'optionally enclosed by' clause. ....actually, I guess I don't know that was the problem.

The solution was to take that clause out.

I think the explanation for the problems is on 9-21 of the utilities guide although I still don't fully understand it.


Finally, search MOSC, there are several bugs relating to using external tables and the alert log with ORA-12899:
http://support.oracle.com

Especially, note bug 5172797.

I'm betting that MOSC will have your answer, a bug or a workaround . . .

Let us know what you find!

Good Luck!
sbusztin
Hello, Happy Monday blink.gif I'll try this out and post the outcome. Thanks for your help!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.