When a lot of DML operations happens on a table, the table will become fragmented because DML does not release free space from the table below the HWM. So despite having less number of rows, due to fragmentation, it consumes more space. So it is best practice to re-org the oracle table regularly.
In this below example, we will consider the table DBATEST.TEST1 :
SQL> select count(*) from DBATEST.TEST1;
COUNT(*)
----------
1450176
SQL> SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER='DBATEST' and segment_name='TEST1';
SUM(BYTES/1024/1024)
--------------------
168
Below script to get the current fragmentation details on the table:
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
OUTPUT:
Enter value for schema_name: DBATEST
Enter value for table_name: TEST1
old 15: dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
new 15: dbms_space.space_usage ('DBATEST', 'TEST1', 'TABLE', v_unformatted_blocks,
Unformatted Blocks = 492
FS1 Blocks = 1
FS2 Blocks = 1
FS3 Blocks = 0
FS4 Blocks = 239
Full Blocks = 20584 ----------->>>>>>>>>>>>>>>>>>> THIS MANY BLOCKS ARE FULLLY OCCUPIED
PL/SQL procedure successfully completed.
Where:
unformatted_blocks : Total number of blocks unformatted
fs1_blocks : Number of blocks having at least 0 to 25% free space
fs2_blocks : Number of blocks having at least 25 to 50% free space
fs3_blocks : Number of blocks having at least 50 to 75% free space
fs4_blocks : Number of blocks having at least 75 to 100% free space
ful1_blocks : Total number of blocks full in the segment
Let’s perform some DML operation:
SQL> delete from DBATEST.TEST1; 1450176 rows deleted. commit;
Check the table size:
SQL> SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER='DBATEST' and segment_name='TEST1';
SUM(BYTES/1024/1024)
--------------------
168
We can see, table segment size is still same, despite deleting all the rows from the table.
Run the fragmentation script
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
Enter value for schema_name: DBATEST
Enter value for table_name: TEST1
old 15: dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
new 15: dbms_space.space_usage ('DBATEST', 'TEST1', 'TABLE', v_unformatted_blocks,
Unformatted Blocks = 492
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 20825 ------->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> THIS BLOCKS HAVING 75 TO 100 PERCENT FREE SPACE
Full Blocks = 0
PL/SQL procedure successfully completed.
You can see, 20825 blocks are having 75 percent to 100 percent. This is due to the fragmentation. So this table is a candidate for re-org.
TABLE REORG:
There are multiple methods of reorg , But alter table move is the easiest method to do reorg.
SQL> ALTER TABLE DBATEST.TEST1 move; Table altered.
Check the Table size:
SQL> SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE OWNER='DBATEST' and segment_name='TEST1';
SUM(BYTES/1024/1024)
--------------------
.0625
All the space has been released, Lets run the same fragmentation script
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
Enter value for schema_name: DBATEST
Enter value for table_name: TEST1
old 15: dbms_space.space_usage ('&SCHEMA_NAME', '&TABLE_NAME', 'TABLE', v_unformatted_blocks,
new 15: dbms_space.space_usage ('DBATEST', 'TEST1', 'TABLE', v_unformatted_blocks,
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 0
Full Blocks = 0
PL/SQL procedure successfully completed.
NOTE – If indexes are present on this table, then after re-org, rebuild this index, else they will become unusable.
ALTER INDEX INDEX_NAME REBUILD;
Thanks for table de-fragmentation script.
Can you please provide a script were it will first check if fragmentation is there or not for all tables in database. A tabular report like output.
Then , a next step to de-fragment all those tables by re-org (or any other method).
Next a script to rebuild all necessary indexes with parallel option (with some input thread value)
ERROR at line 1:
ORA-14107: partition specification is required for a partitioned object
ORA-06512: at “SYS.DBMS_SPACE”, line 235
ORA-06512: at line 15
Can you share script for partitioned table too?
Thanks