| Lesson 6 | Setting the extent size for an object |
| Objective | Use the storage clause to set the extent size for a table in Oracle |
Use the storage clause to set the extent size for a table in Oracle.
In the previous lesson, you learned about the different attributes that determine how Oracle uses extents within a segment. There are a set of SQL keywords you can use in the DDL statements that create and alter database objects and thus control how extents are used.
Syntax for extents
In the previous lesson, you learned about the different attributes that determine how Oracle uses extents within a segment. There are a set of SQL keywords you can use in the DDL statements that create and alter database objects and thus control how extents are used.
All of these storage parameters can be used with a CREATE TABLE, CREATE INDEX, ALTER TABLE, or ALTER
INDEX statement:
| INITIAL: | This keyword is used with an integer and either K or M, to specify either Kilobytes or Megabytes. The INITIAL size is the size of the initial extent allocated when the object is created. |
| NEXT: | This keyword uses the same integer notation as INITIAL and describes the size of the first extent allocated after the initial extent is full. |
| PCTINCREASE: | This keyword specifies the percentage each subsequent extent should grow by. If the NEXT size is 20 K and the PCTINCREASE is 50 (its default value), the third extent allocated would be 30 K (20 K * 150%), the fourth extent allocated would be 45 K (30 K * 150%), and so on. |
| MINEXTENTS: | This keyword is followed by an integer that indicates the minimum number of extents that are initially allocated. If the value for this parameter is greater than 1, Oracle will allocate the additional extents when the object is created, based on the INITIAL, NEXT, and PCTINCREASE parameters. |
| MAXEXTENTS: | This parameter limits the overall size of the database object. |
Example
Click the View Code link below.
View Code
View Code
If you use the storage parameters when creating a table, the syntax will look like this: This SQL statement causes the
table to begin with a single extent of 100 K allocated. Subsequent extents would contain 50K, 75 K (50 K *150%), and so
on. The table could not contain more than five extents, which would limit its overall size to 506.25 K.
You do not have to specify storage parameters for any object.
The next lesson shows how to set default storage attributes for all the objects in a tablespace.
You do not have to specify storage parameters for any object.
The next lesson shows how to set default storage attributes for all the objects in a tablespace.
