Oracle Database Tips by Donald Burleson

Administration of the Database 

A database consists of executables, global areas, and database files. Within the database files exist tables, indexes, sequences, views, clusters, and synonyms. The DBA will be involved in the creation, maintenance, and deletion of these objects on a frequent basis. The commands CREATE, ALTER, and DROP are fairly easy to master. A subset of the CREATE and ALTER command, the STORAGE clause, is also very important for the DBA to understand and use properly.

The CREATE Command

As its name implies, the CREATE statement is used to create databases, tablespaces, tables, clusters, database links, indexes, sequences, views, users, packages, procedures, functions, and rollback segments. It has this general format (any thing in square brackets is optional):

See Code Depot

The STORAGE Clause

The STORAGE clause specifies how an object uses the space to which it is allocated. Some objects, including packages, procedures, types, views, libraries, directories, indextypes, and others, don't use the STORAGE clause Let's look at the format of the STORAGE clause.

See Code Depot

where:

[DEFAULT]. 

Is used only in a TABLESPACE specification to specify the default storage used for objects placed in the tablespace when no object-specific storage specification is made.

INITIAL.  Specifies the size in bytes of the initial extent of the object. The default is 5 Oracle block sizes (10K for a 2K blocksize, 40K for an 8K blocksize, and so forth). The minimum size is 2 Oracle blocks plus 1 for each freelist specified (freelists default to 1 for tables, 2 for indexes.) The maximum is 4 gigabytes on most platforms. All values are rounded to the nearest Oracle blocksize.  

NEXT.  Indicates the size for the next extent after the INITIAL is used. The default is 5 Oracle blocks, the minimum is 1 Oracle block, the maximum is 4 gigabytes. NEXT is the value that will be used for each new extent if PCTINCREASE is set to 0. If PCTINCREASE is greater than 0, then the next extent will be NEXT, the second extension will be NEXT times 1 plus PCTINCREASE/100, then the size of that extent times 1 plus PCTINCREASE/100 for the next extension, and so forth. The factor of 1 plus PCTINCREASE/100 is only applied to the size of the last extent.   

MINEXTENTS.  Specifies the number of initial extents for the object. Generally, except for rollback segments, it is set to 1. If a large amount of space is required and there is not enough contiguous space for the table, setting a smaller extent size and specifying several extents may solve the problem. The values for INITIAL, NEXT, and PCTINCREASE are used when calculating the extent sizes for the number of extents requested.  

MAXEXTENTS.  Specifies the largest number of extents allowed the object. This defaults to the max allowed for your blocksize for Oracle8, Oracle8i, and Oracle9i. In addition, if UNLIMITED is set, there is no upper limit.

PCTINCREASE.  Tells Oracle how much to grow each extent after the INITIAL and NEXT extents are used. A specification of 50 will grow each extent after NEXT by 50 percent  for each subsequent extent. This means that for a table created with one INITIAL and a NEXT extent, any further extents will increase in size by 50 percent over their predecessor. Under Oracle8, Oracle8i, and Oracle9i, this parameter is applied only against the size of the previous extent. The DEFAULT value is 50, and this should always be adjusted.

www.dba-oracle.com/oracle_scripts.htm