Oracle Database Administration: The Essential Refe

How Oracle Allocates Storage

Although Oracle stores rows of data in Oracle blocks, which exist in the context of a particular operating system file, the DBA has little control over which particular block is used for a particular database object. However, the DBA does control which tablespace an object is created in and how much space within that tablespace is allocated to the object.

When creating an object in the database, the CREATE statement usually specifies the tablespace in which the object will be stored (see the TABLESPACE clause of the CREATE commands in Chapter 13). If a TABLESPACE clause is not included in the CREATE command, the default tablespace for that user will be used for object creation.

Tip

As a DBA, you should be careful always to specify a DEFAULT TABLESPACE for each user, since Oracle will assign the SYSTEM tablespace as the default if you omit the specification, and in most cases you will not want non-system objects created in the SYSTEM tablespace.

Oracle allocates storage in logical units called extents . An extent is simply an amount of storage that is rounded up to the next multiple of the Oracle blocksize. When an object is created, that object has associated with it, either explicitly or implicitly, the amount of storage to be allocated upon object creation (the INITIAL extent) and the amount of space to be allocated when the INITIAL allocation is used and more space is required (the NEXT extent). A typical CREATE command contains a STORAGE clause in the ...