Oracle 表的行數、表佔用空間大小，列的非空行數、列佔用空間大小 查詢

標籤：col   log   user   佔用   select   gpo   table   ora   tables   

--表名,表佔用空間大小（MB）,行數
select table_name, round(num_rows * avg_row_len /1024/1024, 8) as total_len, num_rows

from user_tables

where table_name = ‘表名‘

order by table_name;

 

--表名,列名,列佔用空間大小（MB）,非空行數

select c.table_name, c.column_name, round((t.num_rows - c.num_nulls) * c.avg_col_len /1024/1024, 8) as total_len, t.num_rows - c.num_nulls as nums

from user_tab_columns c

join user_tables t on c.TABLE_NAME = t.TABLE_NAME

where c.table_name = ‘表名‘

order by c.column_name;

Oracle 表的行數、表佔用空間大小，列的非空行數、列佔用空間大小 查詢