Are there any disadvantages of using UTF8 in an oracle database

charsetoracleoracle-11gutf-8

We are installing ordering a configured oracle database and they are asking us what character encoding we would like to have. The application (in Java) is in English only but users are from different parts of the world.

Are there any motivations for NOT using UTF8 or other unicode character set?

Best Answer

You should have two choices to make :

Choose your database character set (used by VARCHAR2, CHAR, CLOB datatypes).
Choose your national character set (used by NVARCHAR2, NCHAR, NCLOB datatypes).

As seen here :

Oracle recommends using Unicode for all new system deployments.

National character sets can only be Unicode : UTF-8 or UTF-16. So choosing the same character set for both would be redundant...

My advice (you say your application is in English only) :

Ask for your database character set to be UTF-8.
Ask for your national character set to be UTF-16.

And here is my general advice for your schema definition. Table by table, column by column (I take the VARCHAR2/NVARCHAR2 sample here) :

if your column could contain any character in the world (as in user input), make it NVARCHAR2.
if you have control about what is going to be stored (English then), make it VARCHAR2.

Related Solutions

Database – the typical maximum number of database connections for Oracle running on Windows server

In order to handle a database connection, a separate Oracle process is required, which may require up to 2 MB of memory. Normally an Oracle installation limits the number of concurrent connections based on the available memory and CPU configuration.

For example, let's say you have 50 customer accounts on the database with a maximum of 100 concurrent connections per account. If every account in average uses only 25% = 25 connections, the total average number would be 1250 connections. That means 2500 MB only for storing connection specific data. There is also processor time needed for context switching.

By setting limits on concurrent connections, you can ensure your transactions get through, and that some very active customer accounts don’t start to block your database server's resources. The number of connections is limited by the sessions parameter and your licensing. The sessions parameter controls the total number of connections including the Oracle background processes.

Adding more RAM to the server using a 64-bit Windows platform is a good idea, since you don’t have to spend a lot, even adding 16 GB of memory.

For detailed information of how configurations perform on realistic workload scenarios, read the Oracle E-Business Suite Standard Benchmark. Typically they have a large number of connections.

Mysql – how does one quote unicode characters in thesql prompt or in SQL in general

This page seems to be very close to what you're looking for, although the specific values are different; 0x0092 is decimal 146, the Windows code for right smartquote. That's the equivalent of 0xe28099 in UTF-8, as you'll see in the link :)

In summary:

This code should operate with both the Windows-1252 charset, and also UTF-8, an encoding with an extended character set that has made it the preferred encoding for email and websites.
UPDATE `t` SET `c` = REPLACE(`c`, 0xE28098, "'");
UPDATE `t` SET `c` = REPLACE(`c`, 0xE28099, "'");
UPDATE `t` SET `c` = REPLACE(`c`, 0xE2809C, '"');
UPDATE `t` SET `c` = REPLACE(`c`, 0xE2809D, '"');

...and so on.

Best Answer

Related Solutions

Database – the typical maximum number of database connections for Oracle running on Windows server

Mysql – how does one quote unicode characters in thesql prompt or in SQL in general

Related Topic