|Lesson 7|| Tablespace default extents |
|Objective||Set the default Storage for all the extents in a tablespace. |
Oracle Tablespace default Extents
You can assign storage parameters for each individual table and index in your database.
You may not want to define these parameters for every database object, however, because many of the objects in a database share the same storage characteristics.
In this situation you can assign storage attributes for a tablespace. Any objects stored in that tablespace will use the default storage characteristics of the tablespace. You still can assign specific storage parameters explicitly for any objects within the tablespace.
View the Code below.
In the following example, a tablespace is created and the default storage characteristics are assigned.
CREATE TABLESPACE tabspace_1
DATAFILE 'd:\\:tabspace_file2.dat' SIZE 20M
DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 999 PCTINCREASE 10)
Tablespaces and Storage Characteristics
Some database administrators recommend creating different tablespaces, with different default storage characteristics,
to hold different types of database objects. You can then assign objects that require larger extents to one tablespace, objects that benefit from smaller extents to another tablespace, and so on.
The other side of this argument is that tablespace is too important in overall Oracle operations to use it to manage extent spaces.
The best course of action depends on both the number of database objects you have and your own personal preferences.
Saving the overhead of specifying extents for 1,000 database objects may not be worth subsuming the purpose of the tablespace, while saving the overhead for creating and maintaining 100,000 objects may justify this approach.
The biggest component of a database is usually the files where data is stored.
You could create a database with just one data file if you wanted to prove a point, but most databases have dozens of data files
Data files are logically grouped into tablespaces and are usually given descriptive names such as
- UNDO, and
that indicate their intended purpose. Use a tablespace only for the purpose indicated
by its name.
For example, the SYSTEM tablespace should be used to store only the data dictionary (tables
containing information about the rest of the database). Except for the SYSTEM and SYSAUX tablespaces,
which are always created and whose names are mandated by Oracle, the number and names of the other
tablespaces are left to you.
Each Oracle table and index is assigned to a single tablespace, and their growth is therefore limited by
the availability of space in that assigned tablespace. They share the space with all the other tables or indexes
also assigned to the tablespace. Data files can grow automatically as required.
However, for reasons associated with manageability, you should limit how large they can become.
You can also create large data files of fixed size,
and you can create additional data files during the life of the database.
The names chosen for data files typically include the name of the tablespace and the extension dbf or
For example, SYSTEM01.dbf might be the name given to the first data file in the SYSTEM tablespace.
But this is only a convention, not a requirement, and you may invent your own convention or choose not
to have one.
The space within data files is organized into data blocks (sometimes called pages) of equal size: 2, 4, 8,
16, 32, or 64 kilobytes (KB). 8KB is a commonly used block size.
Each block contains data from just one table.
The size of data records should be considered when deciding what size block to use. Oracle allows data
records to span data blocks, but it is more efficient to retrieve one data block instead of multiple blocks from
different locations on the storage disks. All the data files in one tablespace use blocks of the same size, and
the block size should be a factor in the decision to assign a table or an index to a particular tablespace. When
a table needs more space, it grabs a contiguous range of data blocks called an extent.
It is conventional to use uniformly sized extents for all tables in the same tablespace.
Oracle Tablespace Quiz
Click the Quiz link below to answer a few questions about setting extent sizes.
Oracle Tablespace - Quiz
The next lesson shows how to check on storage attributes in the data dictionary.