表存在很久后统计数据可能会不准需要计算行长度以期获得表的真实大小
脚本思路计算出没列的平均长度相加然后乘以总行数获得真实表数据大小。
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)
得出的行记录 就可以拿出来执行获得行的长度 再来计算表真实大小
脚本思路计算出没列的平均长度相加然后乘以总行数获得真实表数据大小。
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26764973/viewspace-1188879/