oracle统计平均行长度,计算表数据真实行长度获得表真实大小

表存在很久后统计数据可能会不准需要计算行长度以期获得表的真实大小

脚本思路计算出没列的平均长度相加然后乘以总行数获得真实表数据大小。

select 'select  ' ||(SELECT

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=1 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=2 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=3 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=4 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=5 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=6 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=7 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=8 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=9 ) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=10) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=11) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=12) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=13) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=14) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=15) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=16) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=17) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=18) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=19) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||')+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=20) ||

(select t.siz from   (select  rownum rn ,'AVG('||'Lengthb('||COLUMN_NAME||')'||') '   siz from dba_tab_columns where  table_name='TABLE_NAME' and  owner='USERNAME') t where rn=21) --- 最后一行不需要逗号

FROM DUAL)||' from TABLE_NAME'  from dual

注释：第一步需要计算出 select       count(COLUMN_NAME) from    dba_tab_columns where  table_name='TABLE_NAME' and  owner='USERNAME' 计算出这张表有多少列作为rn 的最大值

第二：在表中如果有CLOB NCLOB 列则需要将Lengthb(返回字节数) 函数变成Length(返回字符数) 然后查处系统使用什么字符集一个字符占多少byte相乘

举例如果有CLOB或者NCLOB 则那行需要变成

(select t.siz from   (select  rownum rn ,'AVG('||'Length('||COLUMN_NAME||')'||')*一个字符占字节数+ '  siz from dba_tab_columns where table_name='TABLE_NAME' and owner='USERNAME') t where rn=N)

得出的行记录 就可以拿出来执行获得行的长度 再来计算表真实大小

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26764973/viewspace-1188879/，如需转载，请注明出处，否则将追究法律责任。