Oracle: deleting data from tables leads to data file fragmentation?

WePython 1 Months+

When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments. Is there a way to know about the disk state and any query to defragment or shrink the space?


Can you explain exactly what problem you are concerned with? People have come up with lots of different definitions of "fragmentation" some of which are impossible, some of which are possible but don't create any problems, and a few of which might be worth thinking about.

If you delete at random 1 out of every 2 rows in a table, that will generally mean that you'll have lots of half-empty blocks in the table. That space will be reused by subsequent inserts into the table, though, so it is generally not create any problems unless you are stating that you are permanently reducing the size of the table (i.e. it will never again grow past 5 GB in size) or you have queries that rely on doing full table scans of the table that you want to tune. Depending on the Oracle version, you can probably do a SHRINK SPACE on the table to reduce the size of the segment in that case.


Yes, deleting 50% of data from a table leaves it fragmented, and this make two problems:

1) The High Water Mark (which show the max usage of a table) is still at maximum size of table and make worst performance on Full table Scan

2) Space (extents) are not freed.

If you are using Oracle 11g you can run the Segment Advisor (read this) and run ALTER TABLE SHRINK SPACE CASCADE;

Read warnings for the ALTER command like this one:

"As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause."

Previous : Bug in PL/pgSQL function creation
Next : Selecting 'edge' records in a sequence of data