Previous | Table of Contents | Next

Page 518

Correcting Excessive Table Growth

The Oracle RDBMS handles regular table growth by allocating additional extents to the table segment. We periodically will find tables that are growing much faster than anticipated, however, and such tables may be benefited by a complete reorganization. Correcting an overextended table involves the following steps:

  1. Compute the total size of the table (summing the sizes of all extents).
  2. Perform a logical backup of the table.
  3. Drop the table.
  4. Re-create the table with an initial extent of at least the size from step 1. You should make the initial extent size large enough to contain the current data plus an extra amount of space to accommodate another six to 12 months of growth.
  5. Import the table's data from the logical backup taken in step 2.

This is commonly referred to as reorganizing (reorg) the table. The above steps can be performed one of two ways—using export/import to create the logical backups, or by using a CREATE TABLE AS SELECT statement. For smaller tables, CREATE TABLE AS SELECT is often quicker and easier to perform.

The main problem with performing a table reorganization is handling foreign key constraints dependent on the table. Oracle does not allow you to drop a table that has enabled foreign keys accessing the table. There are two ways to circumvent this problem:

Although the first method is less work, the second is preferred because the integrity definitions are not lost. It is also preferred because grants and other database structures are not lost from the truncate, as they are when the table is dropped. Oracle does not allow you to drop the table, even if you disable the foreign keys—this restriction does not apply to truncating the table. Size the next extent so that all the database rows fit in the two extents, and allocate enough space to allow for future growth. When done, you will have a table with two extents—an initial extent of the same size as it was when created, and a second extent containing your remaining table data and growth space.

Consolidating Clusters

Because clusters and tables are similar in their storage nature, our procedure for consolidating their respective data into single extents varies only slightly. Follow these steps to consolidate clusters:

  1. Compute the total allocated size of the cluster itself as well as the associated tables. The cluster's total size should roughly match that of the cumulative total sizes of the member tables.

Page 519

  1. Perform a logical backup of the cluster (to include all associated tables).
  2. Drop the cluster and associated tables.
  3. Recreate the tables first, then the cluster with an initial extent size sufficient to contain all of the data for each segment plus enough surplus space to accommodate six to 12 months' growth.
  4. Reload the cluster from the logical backup in step 2.

Clusters are relatively similar in nature to tables and the points mentioned above apply to clusters as well.

Consolidating Indexes

An index is very easy to consolidate into a single extent. Because there is no real data that must be saved first and then reloaded, there are only three steps:

  1. Compute the total needed for the index.
  2. Drop the index.
  3. Re-create the index with an initial extent large enough to contain all of index's data as well as enough extra space to accommodate six to 12 months of growth.

Alternatively, use the ALTER INDEX REBUILD command to recreate an index with new storage parameters. This is especially helpful on very large indexes, or primary key indexes that cannot be easily dropped. Be sure to look into the UNRECOVERABLE and PARALLEL clauses to speed index rebuild time.

Managing Tablespace Growth

Handling tablespace growth used to be solved by adding a datafile to the tablespace, with the ALTER TABLESPACE ADD DATAFILE command. While fairly routine, this is a procedure not easily implemented in a hands-off or scripted manner, and thus requires the full attention of a DBA to perform. While not a problem in a planned storage upgrade, this could be a problem when your production system runs out of temp space, and the DBA (most likely you) is in another time zone.

Luckily, Oracle addressed this problem in version 7.3 and beyond of the database. We now have two valuable features to help manage tablespaces and datafiles with—resizable datafiles and automatic datafile extension. Although not a replacement for vigilant space monitoring and planned routines, these new features deserve a valuable place in any Oracle DBA's arsenal.

To resize a datafile, issue the following command:

alter database datafile [datafile name] resize [new_size];

For example, the following statement resizes a datafile in the user_data tablespace to 20MB:

alter database datafile `/u01/oradata/PROD/user_PROD_01.dbf' resize 20M;

Page 520

You can resize a datafile to as large as the OS volume the datafile is stored on can handle. When resizing a tablespace to reclaim lost space, however, a tablespace can only be resized to a size greater than the table's high water mark. Consider the following tablespace map:

Table1 5MB
Free Space 10MB
Table2 15MB
Free Space 125MB
Table3 15MB
Free Space 100MB


In this example, we have three tables in the tablespace taking up 35MB total. The tablespace itself is 265MB in length, but the only space you could reclaim would be the last 100MB.

With autoextending datafiles, Oracle automatically allocates a predetermined amount of space to a datafile when that datafile fills. The syntax of the command is as follows:

ALTER DATABASE DATAFILE [file_spec]
AUTOEXTEND ON NEXT [increment_size] MAXSIZE [max_size,UNLIMITED];

Autoextend can also be enabled when creating the tablespace, by placing the autoextend parameter and values immediately after the datafile specification. The increment_size defines how much space to add to the datafile when it fills. Maxsize sets the maximum size of the datafile. An example of this command is shown below:

ALTER DATABASE DATAFILE `/u02/oradata/DEVL/temp_DEVL_01.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

Autoextend is especially useful for temporary and rollback segment tablespaces. They are a great tool in avoiding transaction failures because there is no more space for a temporary segment or rollback segment to extend. But be wary of an UNLIMITED maxsize—you can find yourself filling an entire OS volume with your temporary tablespace datafile, because of a run-away query that should have been canceled in the first place, when it started allocating all of your temporary space.

OS Filesystem Fragmentation
Oracle DBAs are fairly accustomed to dealing with database fragmentation but often forget the implications of OS fragmentation. Most Oracle sites create tablespaces with datafiles, which are stored on a filesystem native to the operating system. Filesystems can quickly and easily have most of the free space badly fragmented. When Oracle tries to create a datafile of several megabytes, it usually depends on the OS to actually allocate the space it needs on a physical device. If the OS cannot find contiguous free space, it allocates multiple sections of free space until it has found enough aggregate space to hold a datafile. Because of this, more read/write head movement is required to access data in the new datafile(s).

Previous | Table of Contents | Next