Remote DBA Plans
Remote DBA Service

Oracle Database Administration

Oracle Tips by Burleson Consulting

A database consists of executables, global areas, and database files. Within the database files exist tables, indexes, sequences, views, clusters, and synonyms. The Remote 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 Remote 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):

CREATE object_type [schema_name.]object_name
create options,
[STORAGE ( storage parameters)]

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.

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.

OPTIMAL. Used only for rollback segments, it specifies the value to which a rollback segment will shrink back after extending.

FREELIST GROUPS. Specifies the number of freelist groups to maintain for a table or index. FREELIST GROUPS should be set to the number of instances that will be addressing the table in an OPS or RAC environment. You must allow one block for each FREELIST; the number of FREELISTS * FREELIST GROUPS yields the number of total freelists. If you are using locally managed tablespaces (covered in the tablespace section), and you specify a combination of FREELISTS and FREELIST GROUPS that is too large for the extent sizes specified, the create operation will fail.

FREELISTS. For objects other than tablespaces, specifies the number of freelists for each of the freelist groups for the table, index, or cluster. The minimum value is 1; the maximum is blocksize-dependent. Always specify FREELISTS to the number of simultaneous transactions that will be addressing a single block. Empirical testing has shown little improvements by setting this parameter to greater than 4. INITIAL must be set to the minimum value plus the total number of freelists.

NOTE: Both FREELIST GROUPS and FREELISTS are ignored if the tablespace in which the object (index, table, cluster, snapshot, materialized view) resides is in automatic space management mode.

BUFFER_POOL. Specifies the buffer pool in which to place the object when it is used. The choices are DEFAULT (the default value), KEEP, and RECYCLE. KEEP should be used for objects such as indexes or lookup tables that will be referenced multiple times.

Proper use of the STORAGE clause means that you will have to perform accurate estimates of table and index size before creation. This will be covered in Chapters 4, Administration of Relational Database Tables, 5, Administration of Oracle 9i Object Tables, and 6, Administration of Indexes.

Database Creation, Alteration, and Deletion

Like other objects under Oracle, databases themselves can be created, altered, and deleted. Let’s look at these different processes.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.