oracle varchar多大,varchar2设置长度与实际大小

以前有学生问过，varchar2(4k)与varchar2(1)里面存同样的数据会怎么样。

我喜欢直接用测试结果说话，现在把实验补上

CREATE TABLE vc1(c1 VARCHAR2(1));

CREATE TABLE vc4k(c1 VARCHAR2(4000));

分别加入1W行数据进去

SQL> insert into vc1 select * from vc4k;

1000000 rows inserted

SQL> select s.BYTES/1024/1024 from user_segments s where s.segment_name in(upper('vc4k'),upper('vc1'));

S.BYTES/1024/1024

-----------------

13

13

2 rows selected

SQL> select count(*) from vc1 union all select count(*) from vc4k;

COUNT(*)

----------

1000000

1000000

2 rows selected

SQL> select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH from user_tab_columns c where c.TABLE_NAME in(upper('vc4k'),upper('vc1'));

TABLE_NAME COLUMN_NAM DATA_TYPE DATA_LENGTH

---------- ---------- ---------- -----------

VC1 C1 VARCHAR2 1

VC4K C1 VARCHAR2 4000

2 rows selected

SQL>

segment一样

各自增加1W行再看。

SQL> insert into vc1 select * from vc4k;

1000000 rows inserted

SQL> insert into vc4k select * from vc4k;

1000000 rows inserted

SQL> select s.BYTES/1024/1024 from user_segments s where s.segment_name in(upper('vc4k'),upper('vc1'));

S.BYTES/1024/1024

-----------------

25

25

2 rows selected

SQL> 仍然一样