計算表資料真實行長度獲得表真實大小

表存在很久後統計資料可能會不準需要計算行長度以期獲得表的真實大小
指令碼思路計算出沒列的平均長度相加然後乘以總行數獲得真實表資料大小。
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)

得出的行記錄 就可以拿出來執行獲得行的長度 再來計算表真實大小