Skip to content

How to Resolve ORA-01406: fetched column value was truncated

Found ORA-01046 in the alert log of a 8i database:
Errors in file /oracle/admin/orcl/bdump/orcl_snp2_43589.trc:
ORA-12012: error on auto execute of job 6859
ORA-12008: error in snapshot refresh path
ORA-01406: fetched column value was truncated
ORA-02063: preceding line from DBLINK
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 386
ORA-06512: at "SYS.DBMS_IREFRESH", line 450
ORA-06512: at "SYS.DBMS_REFRESH", line 182
ORA-06512: at line 1

It looked like that it was caused by some data that are larger than column defined length when refreshing the snapshot, so we traced the changes on the master table and found one column was recently altered from VARCHAR2(2000) to VARCHAR2(4000). In which, 4000 is the maximum length of type VARCHAR2.

We searched all the cases related to ORA-01046 on the Internet and MOS, they all have one common feature: At least one column with VARCHAR2(4000) is in the table. But unfortunately, they had no real cure to our problem. Finally, we examined all the data in this column and cut some long data if necessary, after that, the snapshot was back to normal.

I think it's probably a bug for 8i and there is no real solution for this problem. In the above case, we chose to cut some long data to workaround it, but I don't think it's a good workaround because it makes the data changed. As a DBA, I might change the physical or logical location of data, but I seldom need to change the data itself owned by other maintenance teams. In other words, we should try to keep the data intact.

One particular case I think you should be aware of is that some users copy and paste their data into the table via GUI tools like TOAD, PL/SQL Developer, etc. This could make the data contain many unwanted white spaces appended to the string and push the length of strings to the boundary of the column, which is 4000. In such case, you should notify the user to trim the white spaces by herself (or himself), and then back to the snapshot, it might work.

Another workaround might be better which will change the schema of the table and you can try is to alter the column type from VARCHAR2(4000) to LONG. It might work also. I hope this post can inspire some ideas for you.

Leave a Reply

Your email address will not be published. Required fields are marked *