EraAzi.com

There are several ways of removing fragmentation in oracle database:

1. Export and Import
2. Moving the tables to a different tablespace and then back to old tablespace.
3. 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.