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.
