How to identify table fragmentation and remove it ?

Posted on , updated on by Yannick Jaquier
Oracle ,

Table of contents

Preamble

After a first blog post on when to rebuild or shrink indexes I have naturally decided to write a post on table fragmentation and how to remove it. Maybe I should have started with this one but when we implemented Oracle Disk Manager (ODM) I have got the feedback from applicative team they have multiple times experience a good performance improvement when rebuilding indexes.

It is equally important to defragment tables as it reduces the number of physical reads to put table data blocks in memory. It also decreases the number of blocks to handle in each query (logical reads) as you will also reduce what is called High Water Mark. You increase rows density per blocks as more table figures (rows) will be clubbed in one block.

I have tried to draw few pictures (tolerance requested, done by myself) to visually explain the concept, you will see them in many other blog posts but those two pictures perfectly sum up what we want to achieve.

As a reminder a tablespace is made of multiple datafiles, the object logical storage is called a segment that is made of multiple extents and each extents is made of multiple blocks. In worst case situation you have deleted multiple rows from your table and remaining rows got sparse is a lot of blocks with a low percentage of completion. High Water Mark (HWM) is the last block containing a row of your table. When doing a Full Table Scan (FTS) all blocks till this HWM will be read. If your table blocks are almost empty you easily understand the over-I/O you will do:

table fragmentation 01
table fragmentation 01

The ideal target is to move to a situation where all rows have been condensed (defragmented) in a minimum number of blocks, each being almost full:

table fragmentation 02
table fragmentation 02

Of course if the deleted rows will be soon reinserted then no need to do anything as Oracle will start to insert new rows in not already full blocks before starting to allocate new ones.

This blog post as been written using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production running on Red Hat Enterprise Linux Server release 6.5 (Santiago).

Legacy situation

The old school approach is to work with DBA_TABLES and estimate how much space the table is taking and how much optimal space it could take. Current size is number of used blocks multiply by block size to have a size in bytes. Theoretical smaller size is number of rows multiply by average row length. The gain you might get is a simple computation from this two values. Of course you must take into account the value of PCTFREE that is percentage of space reserved in blocks for future updates (mainly to avoid what is called row chaining when a row is spread on more than one block).

Of course if you want to estimate the size of a non yet existing table it does not apply !

The query could looks like:

SELECT
  a.blocks*b.block_size AS current_size,
  a.num_rows*a.avg_row_len AS theorical_size,
  (a.blocks*b.block_size)-(a.num_rows*a.avg_row_len) AS gain,
  (((a.blocks*b.block_size)-(a.num_rows*a.avg_row_len))*100/(a.blocks*b.block_size)) - a.pct_free AS percentage_gain
FROM dba_tables a, dba_tablespaces b
WHERE a.tablespace_name=b.tablespace_name
AND owner = UPPER('<owner>')
AND table_name = UPPER('<table_name>');

If you want a better display it can even be put in a PL/SQL block like (inspect_table.sql):

SET linesize 200 pages 1000
SET serveroutput ON SIZE 999999
SET verify off
SET feedback off
DECLARE
  vcurrent_size NUMBER;
  vtheorical_size NUMBER;
  vgain NUMBER;
  vpercentage_gain NUMBER;
  FUNCTION format_size(value1 IN NUMBER)
  RETURN VARCHAR2 AS
  BEGIN
    CASE
      WHEN (value1>1024*1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024*1024),'999,999.999') || 'GB');
      WHEN (value1>1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024),'999,999.999') || 'MB');
      WHEN (value1>1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024),'999,999.999') || 'KB');
      ELSE RETURN LTRIM(TO_CHAR(value1,'999,999.999') || 'B');
    END CASE;
  END format_size;
BEGIN
  SELECT
    a.blocks*b.block_size,
    a.num_rows*a.avg_row_len,
    (a.blocks*b.block_size)-(a.num_rows*a.avg_row_len),
    (((a.blocks*b.block_size)-(a.num_rows*a.avg_row_len))*100/(a.blocks*b.block_size)) - a.pct_free
  INTO vcurrent_size, vtheorical_size, vgain, vpercentage_gain
  FROM dba_tables a, dba_tablespaces b
  WHERE a.tablespace_name=b.tablespace_name
  AND owner = UPPER('&1.')
  AND table_name = UPPER('&2.');
 
  dbms_output.put_line('For table ' || UPPER('&1.') || '.' || UPPER('&2.'));
  dbms_output.put_line('Current table size: ' || format_size(vcurrent_size));
  dbms_output.put_line('Theoretical table size: ' || format_size(vtheorical_size));
  dbms_output.put_line('Potential saving: ' || format_size(vgain));
  dbms_output.put_line('Potential saving percentage: ' || ROUND(vpercentage_gain, 2) || '%');
END;
/
SET feedback ON

For one of my test tables it gives:

SQL> @inspect_table <owner> <table_name>
FOR TABLE owner.table_name
CURRENT TABLE SIZE: 57.031MB
Theoretical TABLE SIZE: 179.000B
Potential saving: 57.031MB
Potential saving percentage: 90%

So my table is currently using around 57MB and I could ideally make it fitting in 179 bytes so one block at the end (that’s why here the computation is not accurate). But here we do not take into account extent management of tablespaces and so obviously the gain will not be that big !

Newest methods to estimate tables size

Not like for indexes here you cannot use EXPLAIN PLAN for create table statement because mainly Oracle cannot guess how many rows you will insert. Since Oracle 10gR1, same as for indexes, you can now DBMS_SPACE.CREATE_TABLE_COST procedures (because two version exist). When calling them you specify target number of row for future tables or real number of rows for existing tables.

As just written there are two versions of DBMS_SPACE.CREATE_TABLE_COST. One where you specify average row length (so for existing table) and one where you give the data type and length of every columns (so apply for soon to be created tables). I have tried both on an existing table and it provided same result, the second form of the procedure is a bit more complex to handle as you must build a variable with a special type (CREATE_TABLE_COST_COLUMNS) which describe all columns. Here is the small PL/SQL block I have written (create_table_cost.sql):

SET linesize 200 pages 1000
SET serveroutput ON SIZE 999999
SET verify off
SET feedback off
DECLARE
  vtablespace_name dba_tables.tablespace_name%TYPE;
  vavg_row_len dba_tables.avg_row_len%TYPE;
  vnum_rows dba_tables.num_rows%TYPE;
  vpct_free dba_tables.pct_free%TYPE;
  used_bytes NUMBER;
  alloc_bytes NUMBER;
  CURSOR cursor1 IS
  SELECT data_type, data_length
  FROM dba_tab_columns
  WHERE owner = UPPER('&1.')
  AND table_name = UPPER('&2.')
  ORDER BY column_id;
  columns1 sys.create_table_cost_columns:=sys.create_table_cost_columns();
  i NUMBER:=0;
  TYPE collection1 IS TABLE OF cursor1%ROWTYPE INDEX BY PLS_INTEGER;
  item1 collection1;
  FUNCTION format_size(value1 IN NUMBER)
  RETURN VARCHAR2 AS
  BEGIN
    CASE
      WHEN (value1>1024*1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024*1024),'999,999.999') || 'GB');
      WHEN (value1>1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024),'999,999.999') || 'MB');
      WHEN (value1>1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024),'999,999.999') || 'KB');
      ELSE RETURN LTRIM(TO_CHAR(value1,'999,999.999') || 'B');
    END CASE;
  END format_size;
BEGIN
  SELECT tablespace_name, avg_row_len, num_rows, pct_free 
  INTO vtablespace_name, vavg_row_len, vnum_rows, vpct_free 
  FROM dba_tables
  WHERE owner = UPPER('&1.')
  AND table_name = UPPER('&2.');
 
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('------------ DBMS_SPACE.CREATE_TABLE_COST version 1 ------------');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_space.create_table_cost(vtablespace_name, vavg_row_len, vnum_rows, vpct_free, used_bytes, alloc_bytes);
  dbms_output.put_line('Used: ' || format_size(used_bytes));
  dbms_output.put_line('Allocated: ' || format_size(alloc_bytes));
 
  OPEN cursor1;
  FETCH cursor1 BULK COLLECT INTO item1;
  FOR i IN item1.FIRST..item1.LAST LOOP
    columns1.extend;
    columns1(i):=sys.create_table_cost_colinfo(item1(i).data_type, item1(i).data_length);
  END LOOP;
  CLOSE cursor1;
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('------------ DBMS_SPACE.CREATE_TABLE_COST version 2 ------------');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_space.create_table_cost(vtablespace_name, columns1, vnum_rows, vpct_free, used_bytes, alloc_bytes);
  dbms_output.put_line('Used: ' || format_size(used_bytes));
  dbms_output.put_line('Allocated: ' || format_size(alloc_bytes));
END;
/
SET feedback ON

With my test table it gives:

SQL> @create_table_cost <owner> <tavle name>
----------------------------------------------------------------
------------ DBMS_SPACE.CREATE_TABLE_COST version 1 ------------
----------------------------------------------------------------
Used: 8.000KB
Allocated: 64.000KB
----------------------------------------------------------------
------------ DBMS_SPACE.CREATE_TABLE_COST version 2 ------------
----------------------------------------------------------------
Used: 8.000KB
Allocated: 64.000KB

So the procedure handle minimum first extent size of 64KB of my 8KB block size and EXTENT MANAGEMENT LOCAL AUTOALLOCATE and SEGMENT SPACE MANAGEMENT AUTO. Despite what Oracle is claiming I see no difference between the two versions of the procedure. If we check current used space (DBA_EXTENTS):

SQL> SELECT bytes, blocks,COUNT(*)
  2  FROM dba_extents
  3  WHERE owner = UPPER('<owner>')
  4  AND segment_name = UPPER('<table_name>')
  5  GROUP BY bytes, blocks
  6  ORDER BY blocks;
 
     BYTES     BLOCKS   COUNT(*)
---------- ---------- ----------
     65536          8         16
   1048576        128         57
 
2 ROWS selected.

Table fragmentation identification

Once we have the estimated size (whatever the method) of the table we can compare it with its actual size and see how much we might gain. To compute the current size of an existing table (of course) we have multiple methods:

  • DBMS_SPACE.SPACE_USAGE procedure
  • DBA_SEGMENTS view
  • DBA_TABLES view

From my testing DBMS_SPACE.SPACE_USAGE is giving exact same result as the query we have seen on DBA_TABLES with a bit more insight on block completion. So the small PL/SQL blocks I have written is not using DBA_TABLES (table_saving.sql):

SET linesize 200 pages 1000
SET serveroutput ON SIZE 999999
SET verify off
SET feedback off
DECLARE
  unformatted_blocks NUMBER;
  unformatted_bytes NUMBER;
  fs1_blocks NUMBER;
  fs1_bytes NUMBER;
  fs2_blocks NUMBER;
  fs2_bytes NUMBER;
  fs3_blocks NUMBER;
  fs3_bytes NUMBER;
  fs4_blocks NUMBER;
  fs4_bytes NUMBER;
  full_blocks NUMBER;
  full_bytes NUMBER;
  dbms_space_bytes NUMBER;
  bytes_dba_segments NUMBER;
  vtablespace_name dba_tables.tablespace_name%TYPE;
  vavg_row_len dba_tables.avg_row_len%TYPE;
  vnum_rows dba_tables.num_rows%TYPE;
  vpct_free dba_tables.pct_free%TYPE;
  used_bytes NUMBER;
  alloc_bytes NUMBER;
  FUNCTION format_size(value1 IN NUMBER)
  RETURN VARCHAR2 AS
  BEGIN
    CASE
      WHEN (value1>1024*1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024*1024),'999,999.999') || 'GB');
      WHEN (value1>1024*1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024*1024),'999,999.999') || 'MB');
      WHEN (value1>1024) THEN RETURN LTRIM(TO_CHAR(value1/(1024),'999,999.999') || 'KB');
      ELSE RETURN LTRIM(TO_CHAR(value1,'999,999.999') || 'B');
    END CASE;
  END format_size;
BEGIN
  SELECT tablespace_name, avg_row_len, num_rows, pct_free 
  INTO vtablespace_name, vavg_row_len, vnum_rows, vpct_free 
  FROM dba_tables
  WHERE owner = UPPER('&1.')
  AND table_name = UPPER('&2.');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('Analyzing table &1..&2.');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('-------------------- DBMS_SPACE.SPACE_USAGE --------------------');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_space.space_usage(UPPER('&1.'), UPPER('&2.'), 'TABLE', unformatted_blocks, unformatted_bytes, fs1_blocks, fs1_bytes, fs2_blocks,
  fs2_bytes, fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, full_blocks, full_bytes);
  dbms_output.put_line('Total number of blocks unformatted :' || unformatted_blocks);
  --dbms_output.put_line('Total number of bytes unformatted: ' || unformatted_bytes);
  dbms_output.put_line('Number of blocks having at least 0 to 25% free space: ' || fs1_blocks);
  --dbms_output.put_line('Number of bytes having at least 0 to 25% free space: ' || fs1_bytes);
  dbms_output.put_line('Number of blocks having at least 25 to 50% free space: ' || fs2_blocks);
  --dbms_output.put_line('Number of bytes having at least 25 to 50% free space: ' || fs2_bytes);
  dbms_output.put_line('Number of blocks having at least 50 to 75% free space: ' || fs3_blocks);
  --dbms_output.put_line('Number of bytes having at least 50 to 75% free space: ' || fs3_bytes);
  dbms_output.put_line('Number of blocks having at least 75 to 100% free space: ' || fs4_blocks);
  --dbms_output.put_line('Number of bytes having at least 75 to 100% free space: ' || fs4_bytes);
  dbms_output.put_line('The number of blocks full in the segment: ' || full_blocks);
  --dbms_output.put_line('Total number of bytes full in the segment: ' || format_size(full_bytes));
  dbms_space_bytes:=unformatted_bytes+fs1_bytes+fs2_bytes+fs3_bytes+fs4_bytes+full_bytes;
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('------------------------- DBA_SEGMENTS -------------------------');
  dbms_output.put_line('----------------------------------------------------------------');
  SELECT bytes INTO bytes_dba_segments FROM dba_segments WHERE owner=UPPER('&1.') AND segment_name=UPPER('&2.');
  dbms_output.put_line('Size of the segment: ' || format_size(bytes_dba_segments));
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('----------------- DBMS_SPACE.CREATE_TABLE_COST -----------------');
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_space.create_table_cost(vtablespace_name, vavg_row_len, vnum_rows, vpct_free, used_bytes, alloc_bytes);
  dbms_output.put_line('Used: ' || format_size(used_bytes));
  dbms_output.put_line('Allocated: ' || format_size(alloc_bytes));
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('---------------------------- Results ---------------------------'); 
  dbms_output.put_line('----------------------------------------------------------------');
  dbms_output.put_line('Potential percentage gain (DBMS_SPACE): ' || ROUND(100 * (dbms_space_bytes - alloc_bytes) / dbms_space_bytes) || '%');
  dbms_output.put_line('Potential percentage gain (DBA_SEGMENTS): ' || ROUND(100 * (bytes_dba_segments - alloc_bytes) / bytes_dba_segments) || '%');
END;
/
SET feedback ON

On my test table it gives:

SQL> @table_saving <owner> <table_name>
----------------------------------------------------------------
Analyzing TABLE <owner>.<table_name>
----------------------------------------------------------------
-------------------- DBMS_SPACE.SPACE_USAGE --------------------
----------------------------------------------------------------
Total NUMBER OF blocks unformatted :0
NUMBER OF blocks HAVING AT LEAST 0 TO 25% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 25 TO 50% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 50 TO 75% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 75 TO 100% free SPACE: 7300
The NUMBER OF blocks full IN the SEGMENT: 0
----------------------------------------------------------------
------------------------- DBA_SEGMENTS -------------------------
----------------------------------------------------------------
SIZE OF the SEGMENT: 58.000MB
----------------------------------------------------------------
----------------- DBMS_SPACE.CREATE_TABLE_COST -----------------
----------------------------------------------------------------
Used: 8.000KB
Allocated: 64.000KB
----------------------------------------------------------------
---------------------------- Results ---------------------------
----------------------------------------------------------------
Potential percentage gain (DBMS_SPACE): 100%
Potential percentage gain (DBA_SEGMENTS): 100%

Only with DBMS_SPACE.SPACE_USAGE you already know that the potential for storage saving is huge because my table is made of 7300 blocks which are all not more than 25% full…

You can even create a procedure based on above PL/SQL block, I have chosen to use DBMS_SPACE.SPACE_USAGE (table_saving_function.sql):

CREATE OR REPLACE FUNCTION table_saving_function(vtable_owner IN VARCHAR2, vtable_name IN VARCHAR2)
RETURN NUMBER
AUTHID current_user
AS
  vtablespace_name dba_tables.tablespace_name%TYPE;
  vavg_row_len dba_tables.avg_row_len%TYPE;
  vnum_rows dba_tables.num_rows%TYPE;
  vpct_free dba_tables.pct_free%TYPE;
  unformatted_blocks NUMBER;
  unformatted_bytes NUMBER;
  fs1_blocks NUMBER;
  fs1_bytes NUMBER;
  fs2_blocks NUMBER;
  fs2_bytes NUMBER;
  fs3_blocks NUMBER;
  fs3_bytes NUMBER;
  fs4_blocks NUMBER;
  fs4_bytes NUMBER;
  full_blocks NUMBER;
  full_bytes NUMBER;
  dbms_space_bytes NUMBER;
  used_bytes NUMBER;
  alloc_bytes NUMBER;
BEGIN
  SELECT tablespace_name, avg_row_len, num_rows, pct_free 
  INTO vtablespace_name, vavg_row_len, vnum_rows, vpct_free 
  FROM dba_tables
  WHERE owner = UPPER(vtable_owner)
  AND table_name = UPPER(vtable_name);
  dbms_space.space_usage(UPPER(vtable_owner), UPPER(vtable_name), 'TABLE', unformatted_blocks, unformatted_bytes, fs1_blocks, fs1_bytes, fs2_blocks,
  fs2_bytes, fs3_blocks, fs3_bytes, fs4_blocks, fs4_bytes, full_blocks, full_bytes);
  dbms_space_bytes:=unformatted_bytes+fs1_bytes+fs2_bytes+fs3_bytes+fs4_bytes+full_bytes;
  IF (vavg_row_len > 0 AND vnum_rows > 0) THEN
    dbms_space.create_table_cost(vtablespace_name, vavg_row_len, vnum_rows, vpct_free, used_bytes, alloc_bytes);
    IF (dbms_space_bytes <> 0) THEN
      RETURN (100 * (dbms_space_bytes - alloc_bytes) / dbms_space_bytes);
    ELSE
      RETURN 0;
    END IF;
  ELSE
    RETURN 0;
  END IF;
END;
/

Then with a query like this you can find the best candidates to work on (this is by the way how I have found the example of this blog post):

SELECT a.owner,a.table_name,table_saving_function(a.owner,a.table_name) AS percentage_gain
FROM dba_tables a
WHERE a.owner='<owner>'
AND a.status='VALID' --In valid state
AND a.iot_type IS NULL -- IOT tables not supported by dbms_space
--and external='no' starting from 12cr2
AND NOT EXISTS (SELECT 'x' FROM dba_external_tables b WHERE b.owner=a.owner AND b.table_name=a.table_name)
AND TEMPORARY='N' --Temporary segment not supported
AND a.last_analyzed IS NOT NULL --Recently analyzed
ORDER BY 3 DESC;

Move, shrink or export/import ?

We have three options in our hands to defragment tables:

  1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes. You obviously need extra space in tablespace to use it. Using ONLINE keyword in Enterprise edition you have no lock and DML are still possible.
  2. Export and import the table. Needless to say the downtime is big and is difficult to get on a production database. Not the option I would choose…
  3. Shrink command available starting with Oracle 10gR1. Usable on segments in tablespaces with automatic segment management and when row movement has been activated.

So the method to target is ALTER TABLE . SHRINK SPACE [COMPACT] [CASCADE]. SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ] … COALESCE.

Same as for indexes COMPACT option has poor interest:

If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE … SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

Let’s try with my test table:

SQL> ALTER TABLE <owner>.<table_name> shrink SPACE;
 
Error starting AT line : 1 IN command -
ALTER TABLE <owner>.<table_name> shrink SPACE
Error report -
ORA-10636: ROW MOVEMENT IS NOT enabled
 
SQL> ALTER TABLE <owner>.<table_name> enable ROW movement;
 
TABLE <owner>.<table_name> altered.
 
SQL> ALTER TABLE <owner>.<table_name> shrink SPACE;
 
TABLE <owner>.<table_name> altered.
 
SQL> EXEC dbms_stats.gather_table_stats('<owner>','<table_name>');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> @table_saving <owner> <table_name>
----------------------------------------------------------------
Analyzing TABLE <owner>.<table_name>
----------------------------------------------------------------
-------------------- DBMS_SPACE.SPACE_USAGE --------------------
----------------------------------------------------------------
Total NUMBER OF blocks unformatted :0
NUMBER OF blocks HAVING AT LEAST 0 TO 25% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 25 TO 50% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 50 TO 75% free SPACE: 0
NUMBER OF blocks HAVING AT LEAST 75 TO 100% free SPACE: 1
The NUMBER OF blocks full IN the SEGMENT: 0
----------------------------------------------------------------
------------------------- DBA_SEGMENTS -------------------------
----------------------------------------------------------------
SIZE OF the SEGMENT: 64.000KB
----------------------------------------------------------------
----------------- DBMS_SPACE.CREATE_TABLE_COST -----------------
----------------------------------------------------------------
Used: 8.000KB
Allocated: 64.000KB
----------------------------------------------------------------
---------------------------- Results ---------------------------
----------------------------------------------------------------
Potential percentage gain (DBMS_SPACE): -700%
Potential percentage gain (DBA_SEGMENTS): 0%
 
SQL> @create_table_cost <owner> <table_name>
----------------------------------------------------------------
------------ DBMS_SPACE.CREATE_TABLE_COST version 1 ------------
----------------------------------------------------------------
Used: 8.000KB
Allocated: 64.000KB
----------------------------------------------------------------
------------ DBMS_SPACE.CREATE_TABLE_COST version 2 ------------
----------------------------------------------------------------
Used: 16.000KB
Allocated: 64.000KB
 
SQL> @inspect_table <owner> <table_name>
FOR TABLE <owner>.<table_name>
CURRENT TABLE SIZE: 8.000KB
Theoretical TABLE SIZE: 1.090KB
Potential saving: 6.910KB
Potential saving percentage: 76.38%
 
SQL> SELECT bytes, blocks,COUNT(*)
  2  FROM dba_extents
  3  WHERE owner = UPPER('<owner>')
  4  AND segment_name = UPPER('<table_name>')
  5  GROUP BY bytes, blocks
  6  ORDER BY blocks;
 
     BYTES     BLOCKS   COUNT(*)
---------- ---------- ----------
     65536          8          1
 
1 ROW selected.

As expected the table is now fitting in one block. But still one extent of 64KB has been allocated to store it. HWM has been reduced so no real need to find something better. Maybe my PL/SQL should be modified to avoid reporting negative percentage gain and just report 0 for no gain…

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

This entry was posted in Oracle and tagged , . Bookmark the permalink.

11 thoughts on “How to identify table fragmentation and remove it ?”

  1. venkat says:

    Queries are not working. Getting error like below.

    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 17

    And does this work for partitioned table.

  2. Tony says:

    Hi,
    Thank you for this article. Really helps me to understand better how to calculate the real size of one table. No I am monitoring the ALTER TABLE SHRINK COMPACT of one huge table by 1350 Gb after my customer has removed rows, since I couldn’t take another method to release the space on tablespace. Started 29 hours ago is still running, the script based on dbms_space.space_usage (”, ”, ‘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); returns unchanged results for last 2 hours but the session is still active. I do not know how could I monitor what is doing.
    Regards,
    Tony

  3. Rupdipt says:

    Hi Yannick

    The script for table fragmentation identification is not working for any partitioned table. Getting the below error.

    ERROR at line 1:
    ORA-14107: partition specification is required for a partitioned object
    ORA-06512: at “SYS.DBMS_SPACE”, line 214
    ORA-06512: at line 43

    Can you check and advice.

    • Mike says:

      *Cause: This procedure can be used only on segments in tablespaces with AUTO SEGMENT SPACE MANAGEMENT
      @Yannick can we used function on tablespaces without AUTO SEGMENT SPACE MANAGEMENT?

      • Yannick Jaquier says:

        @Rupdipt: Yes partitioned table are not (yet) taken into account. Will try to free a bit of time to work on it…
        @Mike: Yes should work with MANUAL segment management but why would you still use this obsolete approach ?

  4. Mamta Chawla says:

    Hi Yannick,
    Thanks for the script. I am not able to run it, as value1 is empty. what is value1 here??

    Regards
    Mamta Chawla

      • Mamta Chawla says:

        Hi Yannick,

        SELECT bytes INTO bytes_dba_segments FROM dba_segments WHERE owner=UPPER(‘&1.’) AND segment_name=UPPER(‘&2.’);

        was giving me the error…I corrected it…as

        SELECT bytes INTO bytes_dba_segments FROM dba_segments WHERE owner=UPPER(‘&1.’) AND segment_name=UPPER(‘&2.’) and tablespace_name=’DATA’

        Regards
        Mamta Chawla

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>