This content has been marked as final. Show 7 replies
HI,1 person found this helpful
Mention the database version and tell us why do you feel tables are fragmented.
We are using Oracle 10g
Update table's statistics and try this, you should see highly fragmented tables at the top with high wasted_space.
I have modified the query a little bit because the ORDER BY won't work if we concat ||'kb' with the column because that makes the output a character data type columns.
select table_name,round((blocks*8),2) "size (kb)" , round((num_rows*avg_row_len/1024),2) "actual_data (kb)", (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)" from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) order by 4 desc
And have added a WHERE condition to only see those tables where total size is greater then actual data size.
Edited by: zahid79 on Jul 23, 2010 1:40 PM
refer the link:1 person found this helpful
Thanks! I would just like to ask you, what do the negative values mean in wasted space?
Is there an easy way to improve defragmentation state?
TABLE NAME SIZE ACTUAL DATA WASTER SPACE
TREE 0 0 0
GC_S 3744 4651.9 -907.9
TRAIL 104 113.04 -9.04
ASSOCIATION_RULES 272 353 -81
ATTRIBUTES 1728 2528.12 -800.12
AUDITACTION 128 208.48 -80.48
DV 18608 36266.47 -17658.47
S134 728 903.08 -175.08
A178 344 518.75 -174.75
S129 728 896.48 -168.48
AGS_NODES 2864 4510.33 -1646.33
S149 472 633.79 -161.79
S127 728 871.62 -143.62
tu 2232 3619.76 -1387.76
PCd_DATA 3112 4371.75 -1259.75
Were the table statistics updated when you ran the query?1 person found this helpful
I have slightly changed the query above, try that now.
Though there a few ways to defragment tables, but the quickest ones are:
1. alter table <<table_name>> move + rebuild all indexes on the table indexes
2. create table <<new_tabele>> as select * from <<old_table>> DROP table <<old_table>> purge; rename <<new_table>> to <<old_table>> OR create table <<new_tabele>> as select * from <<old_table>> TRUNCATE table <<old_table>>; INSERT INTO <<old_table>> SELECT * FROM <<new_table>> DROP TABLE <<new_table>> PURGE;
You helped me a lot!