timesbas.blogg.se

Here, I am following Options 1 and 3 option by keeping table availability in mind. (Shrink command is only applicable for tables which are tablespace with auto segment space management) Export and import the table:- (difficult to implement in production environment) (Depends upon the free space available in the tablespace)Ģ. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:. We have four options to reorganize fragmented tables:ġ. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.Ĥ. If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats. Database Administrator has to provide table_name and schema_name as input to this query. Where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))īelow query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. Select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’ Now again check table size using and will find reduced size of the table. Other wise i would suggest to gather table stats to get updated stats.Įxec dbms_stats.gather_table_stats(‘&schema_name’,’&table_name’) If this value is recent you can skip this step.

Check LAST_ANALYZED value for table in dba_tables. So, We have to have updated stats on the table stored in dba_tables. The difference between these value will report actual fragmentation to DBA. To check exact difference in table actual size (dba_segments) and stats size (dba_tables). Steps to Check and Remove Table Fragmentation:. This all is due to fragmentation in the table or stats for table are not updated into dba_tables. In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)).