Difference between BYTE and CHAR in column data types

SQL> CREATE TABLE CLIENT

  2  (

  3   NAME VARCHAR2(11 BYTE),

  4   ID_CLIENT NUMBER

  5  );

Table created.

SQL> desc client;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 NAME                                                           VARCHAR2(11)

 ID_CLIENT                                                      NUMBER

SQL> CREATE TABLE CLIENT1

  2  (

  3   NAME VARCHAR2(11 CHAR),

  4   ID_CLIENT NUMBER

  5  );

Table created.

SQL> desc client1;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 NAME                                                           VARCHAR2(33)

 ID_CLIENT                                                      NUMBER

SQL> CREATE TABLE CLIENT2

  2  (

  3   NAME VARCHAR2(11),

  4   ID_CLIENT NUMBER

  5  );

Table created.

SQL> desc client2;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 NAME                                                           VARCHAR2(11)

 ID_CLIENT                                                      NUMBER

SQL>

SQL> select TABLE_NAME,CHAR_USED,CHAR_LENGTH,DATA_LENGTH from user_tab_columns where table_name IN (

'CLIENT','CLIENT1','CLIENT2') AND COLUMN_NAME='NAME';

TABLE_NAME                C   CHAR_LENGTH      DATA_LENGTH

--------------------------- -   ----------------        ---------------

CLIENT                         B          11                   11

CLIENT1                       C          11                   33

CLIENT2                       B          11                   11

Let us assume the database character set is UTF-8, it is the default in recent version of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE) or VARCHAR2(11), Oracle will allocate 11 bytes for storage, but you may not actually be able to store 11 charactersin the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle to allocate enough space to store 11 characters,no matter how many bytes it takes to store each one. In Oracle 10g, 3 bytes per character were used.

The VSIZE function returns the byte size of a String.