I'm new on oracle db administration. Just want to know how a tablespace become fragmented? You see we had a tablespace that is very I/O intensive and we want to move one table (11GB of size) on it to a new tablespace with new filesystem/volume group (maybe raid 1). I'm concerned about if the procedure will create fragmentation. If then, is there a way we can defrag it. Any info would much be appreciated.
Reasons for tablespace fragmentation:
1. Incorrect extent management at table instead of tablespace level.
2. High value for freelists parameter (though increasing performance for concurrent inserts it results in higher fragmentation)
To avoid fragmentation:
1. Organize your tablespaces as locally managed
2. Place big and small tables in different tablespaces
To defrag tablespace:
Export/Import the most fragmented table (prior Oracle 8i) or
move your table to separate tablespace (and then back if you want)
with different storage parameters if needed.