A user noticed that a column in a table shows different sizes depending on where they look.
For example, in SQL PI it shows VARCHAR2(128), while in PL/SQL or metadata queries it shows VARCHAR2(32 CHAR).
Oracle stores column sizes in two ways:
If the column is defined as VARCHAR2(32 CHAR, Oracle reserves enough space to store 32 characters. In a multibyte character set (like UTF-8), each character can take up to 4 bytes. So Oracle may reserve up to 128 bytes.
Some tools (like SQL PI in the Oracle agent) report the byte length (128) and assume the column is defined as VARCHAR2(128 BYTE), which is incorrect. The actual definition is based on character semantics.
To confirm the real column definition, run this query:
SELECT COLUMN_NAME, DATA_TYPE, CHAR_LENGTH, CHAR_USED, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = '<TABLE_NAME>'
AND OWNER = '<OWNER>'
AND COLUMN_NAME = '<COLUMN_NAME>';
If CHAR_USED = 'C', the column is defined using character semantics (e.g., VARCHAR2(32 CHAR).
DATA_LENGTH shows the maximum byte size, not the declared character length.
| What You See | What It Means |
|---|---|
| VARCHAR2(128) | Byte size reported by some tools |
| VARCHAR2(32 CHAR) | Actual declared character size |
| DATA_LENGTH = 128 | Max bytes needed for 32 characters |
| CHAR_USED = 'C' | Column uses character semantics |
