【Oracle】满填一张11字段500万记录的表大致消耗空间 答案两G多。

【需求】

确定一张11字段500万记录的表大致消耗多少M空间

【建表语句】

create table emp76(
    id number(12),
    f01 nvarchar2(20),
    f02 nvarchar2(20),
    f03 nvarchar2(20),
    f04 nvarchar2(20),
    f05 nvarchar2(20),
    f06 nvarchar2(20),
    f07 nvarchar2(20),
    f08 nvarchar2(20),
    f09 nvarchar2(20),
    f10 nvarchar2(20),
    primary key(id)
);

【手动计算】

12+10*20*2=412，这是一行记录占的字节数。

412*5000000/1000/1000/1000=2G

加上表格和主键需要的数据结构，实际占用空间应大于2G。

 

【充值语句】

insert into emp76
select rownum,
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij',
       '1234567890abcdefghij'
from dual 
connect by level<5000001;

可以看出，除了id是顺序填充外，其它字段都是采取20个字符填满策略。

 

【查看表空间使用状况的SQL】

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

 

【充值前执行情况】

luna@ORCL>SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
  2  D.TOT_GROOTTE_MB "表空间大小(M)",
  3  D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
  4  TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
  5  F.TOTAL_BYTES "空闲空间(M)",
  6  F.MAX_BYTES "最大块(M)"
  7  FROM (SELECT TABLESPACE_NAME,
  8  ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
  9  ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
 10  FROM SYS.DBA_FREE_SPACE
 11  GROUP BY TABLESPACE_NAME) F,
 12  (SELECT DD.TABLESPACE_NAME,
 13  ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
 14  FROM SYS.DBA_DATA_FILES DD
 15  GROUP BY DD.TABLESPACE_NAME) D
 16  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

表空间名                       表空间大小(M) 已使用空间(M) 使用比   空闲空间(M)
------------------------------ ------------- ------------- -------- -----------
 最大块(M)
----------
SYSAUX                                   620        587.44   94.75%       32.56
        17

UNDOTBS1                               10955         18.19    0.17%    10936.81
      3968

USERS                                   4415         83.62    1.89%     4331.38
   2379.75
SYSTEM                                   700        699.69   99.96%         .31
       .31

EXAMPLE                                  100         78.75   78.75%       21.25
     18.81


已选择5行。

 

【充值后执行情况】

luna@ORCL>SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
  2  D.TOT_GROOTTE_MB "表空间大小(M)",
  3  D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
  4  TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
  5  F.TOTAL_BYTES "空闲空间(M)",
  6  F.MAX_BYTES "最大块(M)"
  7  FROM (SELECT TABLESPACE_NAME,
  8  ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
  9  ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
 10  FROM SYS.DBA_FREE_SPACE
 11  GROUP BY TABLESPACE_NAME) F,
 12  (SELECT DD.TABLESPACE_NAME,
 13  ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
 14  FROM SYS.DBA_DATA_FILES DD
 15  GROUP BY DD.TABLESPACE_NAME) D
 16  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

表空间名                       表空间大小(M) 已使用空间(M) 使用比   空闲空间(M)
------------------------------ ------------- ------------- -------- -----------
 最大块(M)
----------
SYSAUX                                   620        587.44   94.75%       32.56
        17

UNDOTBS1                               10955        281.25    2.57%    10673.75
      3968

USERS                                   4415       2467.62   55.89%     1947.38
    770.19
SYSTEM                                   700        699.69   99.96%         .31
       .31

EXAMPLE                                  100         78.75   78.75%       21.25
     18.81


已选择5行。

【结论】

2467M-83M=2384M，约等于2.3G,这与前面的手动计算是可以相互印证的。

故创建一张11字段500万记录的表，填满情况下需要2.3G空间。

 

END