How to Find table Fragmented & reuse the space in Oracle

Before we start lets understand the why its necessary to fragmentation required before that we need to understand the how oracle manages space for tables

Oracle is designed for high performance, and Oracle does not clean up as rows are added and deleted, thus Oracle consider as a used block.
Rows are not stored contiguously or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.
When a lot of DML operations applied to table then table will become fragmented because DML will not release free space from High Water Mark.HWM (High Water Mark) shows the usage of data block in datafiles but those data might be deleted but HWM will not move that's why oracle will scan upto HWM while full table scan.Not only tables will fragmented but also index will be fragmented when too many DML applied

 So as a job of Oracle DBA need to check regular basis and 
scheduled a downtime for free disk space.But oracle 10g onwards this is becomes more easy with user of Segment Advisor which does not require of downtime for the this task.

Here are the Methods of Recliam wasted space in tables 
 1-> Using Shrink Space
 2-> Create a new copy of table 
 3-> Alter Tables table_name move tablespace other_tablespace
 4-> Impoer Export Dump Utility

So here we will understand with Shrinking Space

first,let's make a scenario by doing random Large DML operations.so we can understand easily 

 This is first method and easy method to reclaim space so let's see

1 => Using Shrink Space 

For Instance, We will create one tablespace,one user and random dml operation for better understanding


--Tablespace Creation
create tablespace abc 
datafile '/u01/app/oradata/testdb/abc1.dbf' size 100 M autoextend on next 10M maxsize 500M
;

--user creation & grant permission
CREATE user vvs identified by 1
default tablespace abc
account unlock
;
grant connect to vvs
;
grant dba to vvs
;

create table table1 as select * from SCOTT.EMP
;
insert into table1 select * from table1  <- multiple times
;
commit
;
create table table2 as select * from SCOTT.DEPT
;
insert into table2 select * from table2   

<- multiple times
;
commit

;
 Now We Have applied bunch of DML statements.On Enterprise manager we can see the current status of spaces of tables.that shoos the tablespace is full if we insert more records it has raised the error

sql > insert into table1 select * from table1
Error report -
SQL Error: ORA-01653: unable to extend table VVS.TABLE1 by 1024 in tablespace ABC
01653. 00000 -  "unable to extend table %s.%s by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
a table segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.

1.1 -> Run the Segment Adviser for Tablespace ABC to find out actual used space on disk

Server -> Tablespace ->tablespace_name 
--run segment adviser from drop down menu

After completing job of Segment adviser navigate to Segment Adviser Recommendations to check how many space can be reclaimable.

OR 

also you can find the from below query 

col RECLAIMABLE_SPACE format a10
col tablespace_name format a10
col username format a10
col object_name format a10
col Allocated_space format a10
col Used_space format a10
col segment_type format a5

select tablespace_name,
       segment_owner as username,
       segment_name as object_name,
               segment_type, 
               allocated_space/1024/1024 || ' MB' AS Allocated_space,
               ROUND(used_space/1024/1024,2) || ' MB' AS Used_space,
               ROUND(reclaimable_space/1024/1024,2) || ' MB' AS Reclaimable_space 
                   from (
                          select
                                * from
table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE')))
order by Tablespace_name
;


TABLESPACE USERNAME   OBJECT_NAM SEGME ALLOCATED_ USED_SPACE RECLAIMABL
---------- ---------- ---------- ----- ---------- ---------- ----------
ABC        VVS        TABLE2     TABLE 35 MB      17.51 MB   17.49 MB   
ABC        VVS        TABLE1     TABLE 464 MB     313.09 MB  150.91 MB  

1.2-> We are going to Reclaim that space 

  Enable the Row Movement for the table

sql> atler table table_name enable row movement ;

It is easy if one or two tables are required to to enable row movement and all operaions but what if tables are 10 or more then it becomes quite difficult to manage . so we have created one query to make sql statement even if there is 100 or more tables just apply the below query you will find the result. 

------------to Enable Row movement for Tables

SELECT  'ALTER TABLE '||
owner ||'.'||TABLE_NAME|| ' enable row movement;'
FROM
(SELECT 
D.OWNER,
D.TABLESPACE_NAME,
D.TABLE_NAME,
ALLOCATED_SPACE AS TOTAL_ALLOCATED_SPACE,
USED_SPACE AS TOTAL_USED_SPACE,
RECLAIMABLE_SPACE,
ROUND(((NUM_ROWS * AVG_ROW_LEN)/1024/1024),2) "Acutal_Data_Size_MB",
((BLOCKS * 8192 )/1024/1024) - (ROUND(((NUM_ROWS * AVG_ROW_LEN)/1024/1024),2)) "Space_Wasted_MB"
FROM (SELECT TABLESPACE_NAME, 
              SEGMENT_OWNER AS USERNAME,
              SEGMENT_NAME AS OBJECT_NAME,
              SEGMENT_TYPE, 
              ALLOCATED_SPACE/1024/1024 || ' MB' AS ALLOCATED_SPACE,
              ROUND(USED_SPACE/1024/1024,2) || ' MB' AS USED_SPACE,
              ROUND(RECLAIMABLE_SPACE/1024/1024,2) || ' MB' AS RECLAIMABLE_SPACE 
                    FROM ( SELECT
                           * FROM
                               TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS('TRUE', 'TRUE', 'FALSE')))
                           ORDER BY TABLESPACE_NAME)A , DBA_TABLES D
WHERE D.OWNER=A.USERNAME
AND D.TABLESPACE_NAME=A.TABLESPACE_NAME
AND D.TABLE_NAME=A.OBJECT_NAME
);


sql>ALTER TABLE VVS.TABLE2 enable row movement;                                                    
sql>ALTER TABLE VVS.TABLE1 enable row movement;                                                    


1.3 -> Now apply shrink space command 

sql > alter table table_name shrink space ;

SELECT  'ALTER TABLE '||
OWNER ||'.'||SEGMENT_NAME|| ' shrink space ;'
    FROM
(SELECT 
D.OWNER,
D.TABLESPACE_NAME,
D.TABLE_NAME AS SEGMENT_NAME,
ALLOCATED_SPACE AS TOTAL_ALLOCATED_SPACE,
USED_SPACE AS TOTAL_USED_SPACE,
RECLAIMABLE_SPACE,
ROUND(((NUM_ROWS * AVG_ROW_LEN)/1024/1024),2) "Acutal_Data_Size_MB",
((BLOCKS * 8192 )/1024/1024) - (ROUND(((NUM_ROWS * AVG_ROW_LEN)/1024/1024),2)) "Space_Wasted_MB"
FROM (SELECT TABLESPACE_NAME, 
              SEGMENT_OWNER AS USERNAME,
              SEGMENT_NAME AS OBJECT_NAME,
              SEGMENT_TYPE, 
              ALLOCATED_SPACE/1024/1024 || ' MB' AS ALLOCATED_SPACE,
              ROUND(USED_SPACE/1024/1024,2) || ' MB' AS USED_SPACE,
              ROUND(RECLAIMABLE_SPACE/1024/1024,2) || ' MB' AS RECLAIMABLE_SPACE 
                    FROM ( SELECT
                           * FROM
                           TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS('TRUE', 'TRUE', 'FALSE')))
                           ORDER BY TABLESPACE_NAME)A , DBA_TABLES D
WHERE D.OWNER=A.USERNAME
AND D.TABLESPACE_NAME=A.TABLESPACE_NAME
AND D.TABLE_NAME=A.OBJECT_NAME

);

sql> ALTER TABLE VVS.TABLE2 shrink space ;                                                    
sql> ALTER TABLE VVS.TABLE1 shrink space ;                                                    

1.4 ->Disable the Row movement 

After the shrink space command by applying above point no. 1.1's query by just replace enable with disable

sql> ALTER TABLE VVS.TABLE2 disable row movement;                                                    
sql>ALTER TABLE VVS.TABLE1 disable row movement; 

   let's see the tablesapce size as earilier we have seen and checked any changes has been down or not

from the enterprise manager we can check that HWM has been Reduced and space has been reclaimed which can be use again for feature.



So its Done, We have succesfully reclaim the wasted space from tables 


Thanks, 

Stay tune for more