Managing Storage Structures

Some of the storage parameters and all block utilization parameters can be altered with the ALTER TABLE command. The command won't fail on locally managed tablespace tables, but the effects will be irrelevant. The storage parameters that they are responsible for maintaining are maintained by the bitmaps in the segments and not in the parameters stored in the data dictionary.

The following code shows a generic example of how a table's storage parameters would be changed using an ALTER TABLE command:

 ALTER TABLE tablename {[storage clause and block utilization clauses]}; 

Different effects occur depending on the parameters that you alter. The most common of these follow:

• NEXT is the value for the next new extent. Subsequent extents will be increased by the value of PCTINCREASE. The value of NEXT will be rounded to a value that is a multiple of the block size that is greater than or equal to the value specified.

• PCTINCREASE will change in the data dictionary immediately but will not be used to recalculate the NEXT until the next extent is ready to be allocated.

• MINEXTENTS will be changed to any value that is less than or equal to the current number of extents in the table. This will only have a noticeable effect if the table is truncated.

• MAXEXTENTS can be set to any value that is equal to or greater than the current number of extents in the table and can be set to UNLIMITED. This change takes effect immediately.

The value for INITIAL cannot be modified for a table.

Manually Allocating Extents

Not only can extents be allocated automatically by Oracle, the DBA can, if necessary, allocate extents to a table manually. This can be done to control and even out the distribution of extents across data files or as a preemptive measure before bulk loading data to avoid dynamic extension of tables.

The general format for this is as follows:

 ALTER TABLE tablename ALLOCATE EXTENT (SIZE xxxK DATAFILE '<tablespace's data file>'; 

If SIZE is omitted, Oracle uses the NEXT_EXTENT size from DBA_TABLES as the size to allocate for the extent. This does not affect the value of NEXT_EXTENT regardless of what PCTINCREASE is set to.

The DATAFILE specified must be a file that belongs to the tablespace to which the table belongs or the statement will generate an error. If the DATAFILE clause is not specified, Oracle allocates the extent in one of the tablespace's files that contains the table.

It often becomes necessary in the course of a DBA's career to move objects around, to make things run better, to alter the structure of objects and the places that they are stored. The next section covers many of the objects that you will encounter in your adventures moving things around.