Oracle Database Administration: The Essential Refe

A Space Allocation Example

Consider the following SQL statement:

CREATE TABLE dept (
deptno               NUMBER(4),
deptname             VARCHAR(30)
location             VARCHAR(20))
TABLESPACE users
PCTFREE 10
STORAGE (INITIAL 8K NEXT 4K MINEXTENTS 1 PCTINCREASE 10)

Oracle will allocate space for the DEPT table as follows:

1. Oracle will look in the free space pool for 8K of space to assign as the INITIAL extent for the table. Assuming a database with a 4K blocksize, two blocks would be allocated.

2. After providing for the block headers, 10% of the remaining space would be reserved for growth of data rows, as indicated by the PCTFREE 10 parameter.

3. Once the INITIAL extent is filled (in this case, both blocks are filled), Oracle looks to the NEXT extent parameter to determine how much additional space to allocate. In this case, a value of 4K is specified. Since this database has a blocksize of 4K, a single block will be allocated and added to this table’s space allocation. After this extent is allocated, the value of NEXT will be dynamically increased to 4506 because PCTINCREASE is set to 10 (4096 × 1.10).

4. When the third block is filled, additional space is required. This time Oracle allocates two blocks, since the current value of NEXT is 4506, and this value is rounded up to the next Oracle block.