Oracle Defragmentation

reorganizes data in tables and indexes to remove fragmentation, reclaim space, and improve performance. Fragmentation from frequent inserts, updates, and deletes can increase I/O operations during queries.

create table test(id number,name varchar2(15));

begin
for i in 1..100000 loop
insert into test values(i,'siva');
end loop;
end;
/

commit

select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='TEST';
select sum(bytes)/1024/1024/1024 from USER_segments where segment_name='TEST';

select table_name,avg_row_len,round(((blocks16/1024)),2)||'MB' "TOTAL_SIZE", round((num_rowsavg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2)/round(((blocks16/1024)),2))100 "percentage"
from all_tables WHERE table_name='TEST';

select table_name,avg_row_len,round(((blocks16/1024)),2)||'MB' "TOTAL_SIZE", round((num_rowsavg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2)/round(((blocks16/1024)),2))100 "percentage"
from user_tables where table_name='TEST';

analyze table test compute statistics;

set serverouput on;

DECLARE
unf NUMBER;
unfb NUMBER;
fs1 NUMBER;
fs1b NUMBER;
fs2 NUMBER;
fs2b NUMBER;
fs3 NUMBER;
fs3b NUMBER;
fs4 NUMBER;
fs4b NUMBER;
full NUMBER;
fullb NUMBER;
BEGIN
DBMS_SPACE.space_usage ('C##CHENNAI','TEST','TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
DBMS_OUTPUT.put_line ('Total number of blocks that are unformatted: ' || unf);
DBMS_OUTPUT.put_line ('Number of blocks that has at least 0 to 25% free space: ' || fs1);
DBMS_OUTPUT.put_line ('Number of blocks that has at least 25 to 50% free space: ' || fs2);
DBMS_OUTPUT.put_line ('Number of blocks that has at least 50 to 75% free space: ' || fs3);
DBMS_OUTPUT.put_line ('Number of blocks that has at least 75 to 100% free space: ' || fs4);
DBMS_OUTPUT.put_line ('Total number of blocks that are full in the segment: ' || full);
END;
/

EXEC dbms_stats.gather_table_stats(ownname => 'c##chennai', tabname => 'test', cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

Defragmentation Methods:

1. move tablesapce
2. ctas
3. exp/imp
4. shrink
5. online redfinition

Shrink:

The SHRINK method in Oracle is used for de-fragmentation by compacting data within a segment to reclaim unused space and make storage more efficient. It works on tables and indexes in tablespaces managed with Automatic Segment Space Management (ASSM).

Benefits of Shrink:

1. Reclaims unused space.
2. Reduces fragmentation.
3. Can be done online with minimal impact on DML operations.

alter table test enable row movement;
alter table test shrink space;
alter table test shrink space compact;
alter table test shrink space cascade;

Online table Redefinition:

DBMS_REDEFINITION is an Oracle package that allows for online table redefinition to reorganize and optimize database structures without significant downtime. This process can be used for defragmentation, schema changes, or moving tables to different tablespaces, all while the table remains available for DML operations.

Usage:

1. Reducing fragmentation.
2. Changing table structure (e.g., adding partitions).
3. Migrating tables to new storage or tablespaces.

create table emp(id number,name varchar2(15),constraint id_pk primary key(id));

begin
for i in 1..100000 loop
insert into emp values(i,'siva');
end loop;
end;
/

begin
for i in 100001..10000000 loop
insert into emp values(i,'siva');
end loop;
end;
/

update emp set name='muthusiva' where id between 500000 and 700000;
delete from emp where id between 700001 and 900000;

analyze table emp compute statistics;

col table_name format a20
col total_size format a20
col actual_size format a20
col FRAGMENTED_SPACE format a20

select table_name,avg_row_len,round(((blocks16/1024)),2)||'MB' "TOTAL_SIZE", round((num_rowsavg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks16/1024)-(num_rowsavg_row_len/1024/1024)),2)/round(((blocks16/1024)),2))100 "percentage"
from user_tables where table_name='EMP';

grant execute on dbms_redefinition to chennai container=current;
GRANT EXECUTE ON DBMS_REDEFINITION TO chennai;
GRANT CREATE ANY TABLE TO chennai;
GRANT ALTER ANY TABLE TO chennai;
GRANT DROP ANY TABLE TO chennai;

conn sys / as sysdba

exec dbms_redefinition.can_redef_table('CHENNAI','EMP');

create table chennai.emp2 as select * from chennai.emp;

exec dbms_redefinition.start_redef_table('CHENNAI','EMP','EMP2′,'ID ID');

exec dbms_redefinition.sync_interim_table('CHENNAI','EMP','EMP2′);

alter table chennai.emp2 add (constraint id_pk2 primary key(id));

exec dbms_redefinition.finish_redef_table('CHENNAI','EMP','EMP2′);

drop table chennai.emp2;