Oracle – Problems with NLS_LENGTH_SEMANTICS Set to CHAR

character-setnlsoracle

I've always thought that anyone using a database where NLS_LENGTH_SEMANTICS set to BYTE when using a multi-byte character set needed their heads examined, but recently a customer of ours said that they had experienced problems applying Oracle patches on databases where NLS_LENGTH_SEMANTICS is set to CHAR. Is there anything to this? Is it a known problem?

Best Answer

There's a useful page here that details any pros/cons/issues that you may encounter when setting the NLS_LENGTH_SEMANTICS parameter.

To answer your question, make sure you set NLS_LENGTH_SEMANTICS=BYTE when running any Oracle "internal" scripts. IE: patches, upgrades, anything in $ORACLE_HOME/dbms/admin.

Related Solutions

Ny doc listing standard <=> Oracle character sets correspondencies

I believe this is covered by the documentation here

Mysql – How to find an invalid utf8 character “somewhere” in the MySQL/trac database

You could try turning on the general query log, which logs every query the server receives, and then use that information to see what's being queried, and then issue those queries yourself and examine the data.

To see the current setting for the general log (location and whether it's enabled):

mysql> SHOW VARIABLES LIKE 'general_log%';  # shows the on/off status and file path

To turn it on:

mysql> SET GLOBAL general_log = on; # or off to disable

Remember to turn if off again when you're done, to avoid filling the disk and potentially degrading performance with excessive I/O.

There are also options for logging to a table instead of a file.

mysql> SHOW VARIABLES LIKE 'log_output';    # shows whether logging to FILE or TABLE

Best Answer

Related Solutions

Ny doc listing standard <=> Oracle character sets correspondencies

Mysql – How to find an invalid utf8 character “somewhere” in the MySQL/trac database

Related Question