Oracle provides a mechanism for a table to extend when new rows are added to the table. Once the size specified by the INITIAL parameter
is exhausted, Oracle will allocate a new extent of NEXT size to hold the new rows. Tables will continue to extend in this fashion until the MAXEXTENTS value is reached.
Multiple Extents
Once a table has extended numerous times, it is not uncommon to see table extents spread randomly across the tablespace. This may (or may not) be a problem. Some DBAs say that multiple extents are good for a table since the data blocks are spread more randomly across the tablespace. Other DBAs argue that multiple extents are bad because Oracle may have to access additional data blocks to get
the table rows.
Table Space
To see this type of fragmentation, you can go to the Tablespace manager within OEM and look at the table extents. Click the View Image button to see an example. The credit detail table is spread in many extents across the tablespace. However, Oracle has allocated most of the extents adjacent to each other.
Create table
new_customer
STORAGE (INITIAL 10m NEXT 2m) <==
where 10m = num_rows * avg_row_len
from dba_tables As
Select *
from customer
Order by
Run the Oracle export utility with the COMPRESS=Y option. This will direct the Oracle import utility to create the table with a large enough INITIAL extent to hold all of the data in one extent.
Use CTAS to reorganize the table. This involves manually computing the new INITIAL extent size (based on avg_row_len times num_rows) and running the CTAS with a STORAGE clause as shown in the example below.
Both of these methods will serve to coalesce extents, but the CTAS method almost always runs faster.
The next lesson explores chained rows.