Oracle Doc defind describes the columns
Column: DATA_LENGTH Datatype: NUMBER Description: Length of the column (in bytes) Column: CHAR_LENGTH Datatype: NUMBER Description: Displays the length of the column in characters. This value only applies to the following data types: CHAR、VARCHAR2、NCHAR、NVARCHAR2 Column: CHAR_USED Datatype: VARCHAR2(1) Description: Indicates that the column uses BYTE length semantics (B) or CHAR length semantics (C), or whether the data type is not any of the following (NULL): CHAR、VARCHAR2、NCHAR、NVARCHAR2
Example:
SQL> create table t1 (c1 int, c2 varchar2(100), c3 varchar2(100 char)); Table created.
Query data_length and char_length, and found char_used is 'C', the char_length and data_length are difference length
SQL> select column_name, char_used, char_length, data_length 2 from user_tab_columns 3 where table_name = 'T1'; COLUMN_NAME C CHAR_LENGTH DATA_LENGTH ------------------------------ - ----------- ----------- C1 0 22 C2 B 100 100 C3 C 100 400
Query database server characterset
SQL> select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET'); PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ------------------------------ ------------------------------ NLS_CHARACTERSET AL32UTF8 Character set
The database server characterset is AL32UTF8, store 1 charter is 4 bytes
100(chars) x 4(bytes each) = 400(bytes)
Maybe best a query suitable for DATA_LENGTH
SQL> select column_name, DECODE(char_used,'C',char_length,data_length) data_length from user_tab_columns where table_name = 'T1'; COLUMN_NAME DATA_LENGTH ------------------------------ ----------- C1 22 C2 100 C3 100
Ref:
0 留言