patternMinor
Oracle: deleting data from tables leads to data file fragmentation?
Viewed 0 times
oracledeletingtablesfilefragmentationfromdataleads
Problem
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?
Solution
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
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.Context
StackExchange Database Administrators Q#22908, answer score: 3
Revisions (0)
No revisions yet.