What does all_tab_columns.data_length actually store?

From: Mark Leonard <mark.leonard_at_tertio.com>
Date: 11 Jun 2003 04:09:50 -0700
Message-ID: <467336d2.0306110309.25e9f48d_at_posting.google.com>



[Quoted] Can somebody please shed some light on the following?

I have created two Oracle (9.2) users and some tables by running a .sql script (using sqlplus) twice in the same UNIX environment.

Therefore I expect the tables to have the same properties, just different owners. However, this does not appear to be the case. Here are some sample outputs. The table name has been changed...

User 1:

SQL> desc my_table_name;

 Name                                 Null?    Type
 ------------------------------------ --------

--------------------------------
DATESTAMP NOT NULL DATE COMPONENTID NOT NULL VARCHAR2(12 CHAR)

select column_name, data_type, data_length, data_precision, data_scale from all_tab_columns where table_name = 'MY_TABLE_NAME'

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
--------------------- -------------------- ----------- --------------


DATESTAMP             DATE                           7
COMPONENTID           VARCHAR2                      48

(Log out and log in as) User 2:

SQL> desc my_table_name;

 Name                                   Null?    Type
 -------------------------------------- --------

--------------------------------
DATESTAMP NOT NULL DATE COMPONENTID NOT NULL VARCHAR2(12)

select column_name, data_type, data_length, data_precision, data_scale from all_tab_columns where table_name = 'MY_TABLE_NAME'

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
--------------------- -------------------- ----------- --------------


DATESTAMP             DATE                           7
COMPONENTID           VARCHAR2                      12

--

My questions are
1. Why does describe for user 1 show VARCHAR2(12 CHAR) ?
2. Why is the data length 4 * 12 rather than just 12 ?
   All of the VARCHAR2 columns have this property (4 * number of
chars).
Received on Wed Jun 11 2003 - 13:09:50 CEST

Original text of this message