The steps to remove fragmentation in tables in Oracle database
There are several ways of removing fragmentation in oracle database:
- Export and Import
- Moving the tables to a different tablespace and then back to old tablespace.
- Moving the tables to same tablespace.
You may choose any of the options but moving tables may require you to check space requirements
Not to implement the 3rd option we have two ways :
Method 1- through direct SQL queries.
Method 2- through bash script.
Use the following query to see the total size of table with fragmentation, expected without fragmentation and how much % of size can be reclaimed after table de-fragmentation. Define table_name and schema_name before you run this query.
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';
Now Implementing Method 1:
Step1: Create index rebuild script with the below query:
SPOOL rebuildindexes.sql SELECT 'ALTER INDEX '|| index_name ||' REBUILD TABLESPACE new_table_sapce;' FROM dba_indexes WHERE tablespace_name='your_table_space'; SPOOL OFF;
Step 2: Create script to move table with the below query:
SPOOL movetables.sql SELECT 'ALTER TABLE '|| table_name ||' MOVE TABLESPACE new_tablesapce;' FROM dba_tables WHERE tablespace_name='your_table_space'; SPOOL OFF;
Step 2: Run the movetables.sql script:
@ movetables.sql
Step 2: Run rebuildindexes.sql query to rebuild index:
@rebuildindexes.sql
Great! you are done.
Now Implementing Method 2:
Get index rebuild script the same way as you did in Method 1 and then:
create a textfile, name it as eraazifile.txt and enter the TABLE_NAME, each in a new line
Create a bash script with following code (edit it according to your need and give it executable permissions.)
#!/bin/bash cat eraazifile.txt |while read TABLE_NAME do {$ORACLE_HOME}/bin/sqlplus "/ as sysdba" > logoutput.log <<EOF set verify off set heading off alter table OWNER.'%${TABLE_NAME}%' move......; / exit EOF
This works magically creating a loop and reading new line TABLE_NAME each time loop executes.
Now run rebuildindexes.sql query from method 1 to rebuild index:
@rebuildindexes.sql
Cheers! You are done.