Table size,fragmentation
Table size
Select segment_name, segment_type, bytes/1024/1024 MB from dba_segments where segment_type='tablename' and segment_name='Table_name'
To check fragmentation or reclaim
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';
Select segment_name, segment_type, bytes/1024/1024 MB from dba_segments where segment_type='tablename' and segment_name='W_QUOTEITEM_F';
Select segment_name, segment_type, bytes/1024/1024 MB from dba_segments where segment_type='tablename' and segment_name='Table_name'
To check fragmentation or reclaim
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';
Select segment_name, segment_type, bytes/1024/1024 MB from dba_segments where segment_type='tablename' and segment_name='W_QUOTEITEM_F';
Comments
Post a Comment