Setup Menus in Admin Panel

DBATalent

Fragmentation in Oracle Database

Knowledge Base > Oracle Database > Fragmentation in Oracle Database

in Oracle Database

This article describes the steps for performing fragmentation of a table in oracle database.

Contents

 

Introduction

When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM. The High Water Mark of the table moved forward (as new rows are inserting into the table), it could not be moved backwards in order to decrease the table size and de-allocate space. When we delete many rows from the table, the HWM and the table size remains same and will not decrease. We can obtain the space from such fragmented tables by using different methods.

Identifying fragmented tables

  • To view the list of most fragmented tables in database use the below query,

SQL>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;

 

Procedure

  • Select a fragmented table and view the last analyzed information of a table.

SQL> select owner,table_name,last_analyzed, global_stats from all_tables where table_name=’&Tablename’;

 

  • If the table has recent modifications execute gather table stats to get updated stats on a table.

SQL>exec dbms_stats.gather_table_stats(‘&schema_name’,’&Table_name’);

 

  • You can find the occupied size of the table by using below query.

SQL>select bytes/1024/1024/1024 GB from dba_segments where segment_type=’TABLE’ and segment_name=’&Table_Name’;

 

  • Find the reclaimable space of the table.

SQL>select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&Table_Name’ AND OWNER LIKE ‘&schema_name’;

 

  • Check the table name and tablespace name from below query.

SQL> select table_name,tablespace_name from all_tables where table_name = ‘&Tablename’;

 

  • Run the below command to list the tablespaces with Used space,Free space , Total space, Pct free information .

SQL> select df.tablespace_name “Tablespace”, totalusedspace “Used MB”, (df.totalspace – tu.totalusedspace) “Free MB”, df.totalspace “Total MB”, round(100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace))”Pct.Free” from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace  from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by  tablespace_name) tu where df.tablespace_name = tu.tablespace_name order by “Pct.Free”;

 

  • Check the used space and free space of tablespace and datafile also using the below commands.

SQL> select a.tablespace_name,round(sum(a.bytes/1024/1024)) as “TOTAL_MB”,round(sum(b.bytes/1024/1024))as “FREE_MB” from dba_data_files a, dba_free_space b where a.tablespace_name=b.tablespace_name and a.tablespace_name=’&tablespace_name’ group by a.tablespace_name;

SQL> select a.file_name,round(sum(a.bytes/1024/1024)) “TOTAL MB” ,round(sum(b.bytes/1024/1024)) “FREE MB” from dba_data_files a, dba_free_space b where a.file_id=b.file_id and a.tablespace_name=’&tablespace_name’ group by a.file_name;

 

  • Note the Current size of your table and check if any other tablespace has same free space available. So, that we can move this table to the tablespace. OR create a new tablespace with the required size.

SQL>create tablespace <tablespacename> datafile ‘<path>’ size 32000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

  • Collect status of all the indexes on the table.

SQL>select index_name,status from user_indexes where table_name like ‘&Table_name’;

 

  • Enable row movement for the table.

SQL> alter table <Table_name> enable row movement;

  • Move fragmented table from one tablespace to another tablespace.

SQL> alter table <Table_name> move tablespace <New_Tablespace_name>;

 

  • Now again move the table back to the original tablespace.

SQL> alter table < Table_name> move tablespace <Old_Tablespace_name>;

 

  • We should disable the row movement after moving the table. Because it will cause performance issues.

SQL> select row_movement,table_name from user_tables where table_name=’&Table_name’;

SQL>alter table <Table_name> disable row movement;

  • Rebuild all indexes on the table because the move command will take the indexes into unusable state.

SQL> select status,index_name from dba_indexes where table_name = ‘&Table_name’;

 Here, value in status field may be valid or unusable.

 

  • Use this command to rebuild the index.

SQL> alter index <INDEX_NAME> rebuild online;

 

  • Now check the status of the index it should be valid.

SQL> select status,index_name from dba_indexes where table_name = ‘&Table_name’;

 

  • If you have created a new tablespace drop the tablespace.

SQL> drop tablespace <Tablespace_name> including contents and datafiles;

 

  • Now gather table stats and check the size of moved table.

SQL>exec dbms_stats.gather_table_stats(‘<Owner>’,'<Table_name>’,ESTIMATE_PERCENT=>100,METHOD_OPT=>’for all indexed columns size auto’,CASCADE=>True);

SQL>select bytes/1024/1024/1024 GB from dba_segments where segment_type=’TABLE’ and segment_name=’&Table_Name’;

 

  • Find the reclaimed space in the datafiles of a tablespace using below queries and resize datafiles to obtain a space at OS level.

SQL> select a.tablespace_name,round(sum(a.bytes/1024/1024)) as “TOTAL_MB”,round(sum(b.bytes/1024/1024)) as “FREE_MB” from dba_data_files a, dba_free_space b where a.tablespace_name=b.tablespace_name and a.tablespace_name=’&tablespace_name’ group by a.tablespace_name;

SQL> select a.file_name,round(sum(a.bytes/1024/1024)) “TOTAL MB” ,round(sum(b.bytes/1024/1024)) “FREE MB” from dba_data_files a, dba_free_space b where a.file_id=b.file_id and a.tablespace_name=’&tablespace_name’ group by a.file_name;

 

  • Resize the datafile.

SQL> alter database datafile ‘<datafile path>’ resize <size_clause>’;

0