SQL Extensions   «Prev  Next»

Lesson 4 Setting storage space for a table
ObjectiveIdentify the parameters that define storage space.

Setting Table Storage Space

You can specify one or all of the space-related parameters yourself to fine-tune the table's storage requirements. The MouseOver below explains the syntax.
  1. Here is where you place your list of columns, the datatype, and column constraints. For example:
    (PRODUCT_ID NUMBER(10,2), PRODUCT_NAME VARCHAR2(50), …)
    
  2. Percent of space saved for updates of current rows in the block.
  3. Maximum percent used before no more rows can be inserted into the block.
  4. Initial extent size.
  5. Next extent size.
  6. Minimum extents to create when the table is created.
  7. Maximum extents allowed. Specify a number or the word UNLIMITED.
  8. Percent increase of each subsequent extent over the previous one.
  9. fill in.......

Create Space Syntax

Storage parameters

The table below explains each parameter in more detail.
Parameter Description
PCTFREE Specifies the percentage (0 - 99) of space within a block that is to be left available for rows to grow through updates.
PCTUSED Tells the database what percentage (1 - 99) of space within a block can be used for inserting new rows. Once the block passes this percentage, no more inserts can be done in the block. A new block will be started for inserting rows. The remaining space in the first block is saved for updates.
INITIAL Sets the space initially allocated to the table when it is created.
NEXT Sets the amount of space that will be allocated to the table when the initial space has been used up.
MINEXTENTS The minimum number of extents to allocate when the table is created. The default is one
MAXEXTENTS The maximum number of extents that can be allocated for the table. You can either use a set number or use the word "UNLIMITED."
PCTINCREASE The percentage of size that each new extent increases compared to the one before it (not counting the first extent). For example, if the NEXT parameter is 10K and PCTINCREASE is 50K, then the second extent will be 10K, the third extent will be 15K, and the fourth extent will be 24K, and so on. (The smallest increment is always one block, which is usually 2K, so 22.5 gets rounded up in this case to 24.)
TABLESPACE Names the tablespace, where all the extents for this table are to be stored.

You can name any one of these parameters or all of them. They can appear in any order so long as they are placed after the column specifications in the statement.
The next lesson shows you how to establish a primary key for your table when you create it.

Creating Oracle Tables - Quiz

Click the Quiz link below to answer a few questions about creating tables in Oracle.
Creating Oracle Tables - Quiz