Hi,
I was going through a few databases at my work place and I noticed something unusual.
Database Server - Oracle 10g R2
Database Client - Oracle 11g R1 (11.1.0.6.0 EE)
Client OS - Win XP
SQL>
SQL> @ver
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
SQL> --
SQL> drop table t;
Table dropped.
SQL> create table t (
2 a char(3 char),
3 b char(3 byte),
4 c char(3),
5 d varchar2(3 char),
6 e varchar2(3 byte),
7 f varchar2(3)
8 );
Table created.
SQL> --
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(3 CHAR)
B CHAR(3)
C CHAR(3 CHAR) <= why does it show "CHAR" ? isn't "BYTE" semantics the default i.e. CHAR(3) = CHAR(3 BYTE) ?
D VARCHAR2(3 CHAR)
E VARCHAR2(3)
F VARCHAR2(3 CHAR) <= same here; this should be VARCHAR2(3)
SQL> --
SQL> select table_name,
2 column_name,
3 data_type,
4 data_length,
5 data_precision,
6 data_scale
7 from user_tab_columns
8 where table_name = 'T';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
------------ ------------ ---------- ----------- -------------- ----------
T A CHAR 12 <= why 12 and not 3 ? why multiply by 4 ?
T B CHAR 3
T C CHAR 12 <= same here
T D VARCHAR2 12 <= and here
T E VARCHAR2 3
T F VARCHAR2 12 <= and here
6 rows selected.
SQL>
SQL>
I believe it multiplies the size by 4, because it shows 16 in user_tab_columns when the size is changed to 4.
When I try this on 11g R1 server, it looks good -
Database Server - Oracle 11g R1
Database Client - Oracle 11g R1 (11.1.0.6.0 EE)
Client OS - Win XP
SQL>
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
5 rows selected.
SQL> --
SQL> drop table t;
Table dropped.
SQL> create table t (
2 a char(3 char),
3 b char(3 byte),
4 c char(3),
5 d varchar2(3 char),
6 e varchar2(3 byte),
7 f varchar2(3)
8 );
Table created.
SQL> --
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(3 CHAR)
B CHAR(3)
C CHAR(3)
D VARCHAR2(3 CHAR)
E VARCHAR2(3)
F VARCHAR2(3)
SQL> --
SQL> select table_name,
2 column_name,
3 data_type,
4 data_length,
5 data_precision,
6 data_scale
7 from user_tab_columns
8 where table_name = 'T';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE
------------ ------------ ------------ ----------- -------------- ----------
T A CHAR 3
T B CHAR 3
T C CHAR 3
T D VARCHAR2 3
T E VARCHAR2 3
T F VARCHAR2 3
6 rows selected.
SQL>
SQL>
Is it a known bug ? Unfortunately, I do not have access to Metalink.
Thanks,
isotope
Edited by: isotope on Mar 3, 2010 6:46 AM