SQL> desc t_seq;####查看表结构
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(10) ###注意是char,且为16个宽度
B NUMBER(38)
#####用plsql批量插入100000要记录,利用序列为主键生成方式
SQL> 1 declare
2 v_i int;
3 begin
4 for v_i in 1..100000 loop
5 insert into t_seq values(seq_1.nextval,v_i);
6 commit;
7 end loop;
8* end;
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes,bytes/1024/1024 mb from user_segments where segment_name='T_SEQ';###10w记录占用空间为2m
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES MB
---------- ----------
T_SEQ
3145728 3
SQL> alter table t_seq modify a char(5);######不能把数据类型从大变成小,只能从小变成大
alter table t_seq modify a char(5)
*
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big
SQL> alter table t_seq modify a char(16);#####加大a列宽度,从10到16
Table altered.
SQL> select segment_name,bytes,bytes/1024/1024 mb from user_segments where segment_name='T_SEQ';#####这个表占用空间增大到4m,足足加了1m
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES MB
---------- ----------
T_SEQ
4194304 4
小结:
1,了解业务,选择适用表的列数据类型,科学节省存储空间
2,学习oracle各种列(不同数据类型)所占用的空间的方法,结合业务,估算存储空间,为选用存储及分区作好准备