Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Release 9.0.1

Part Number A86647-01





Go to previous page Go to next page

Introduction to the Reorg Wizard

The Reorg Wizard can help you maintain a well performing database by correcting space usage problems. Over time, database performance can be affected by problems such as row chaining and/or migration and index stagnation. The Reorg Wizard can eliminate space problems by reorganizing database space usage. The Reorg Wizard also allows you to change an object's storage settings and location.

The Reorg Wizard gives you three reorganization options:

The wizard performs reorganizations within the Oracle database and does not use external data handling operations. Reorganizations can be performed within the target tablespace or by temporarily using space in another tablespace.

The Reorg Wizard evaluates the reorganization operations and generates an Impact Report and reorganization script, which can be reviewed prior to execution. Reorganizations can be performed immediately or can be scheduled at a later time, perhaps during off-peak hours. In either case, the reorganization will be run by an Enterprise Manager job using the Oracle Agent.


The Enterprise Manager Console, Oracle Management Server and Agent are required to perform reorganizations with the Reorg Wizard. The Agent must be running on the same node as the database being reorganized. 

Certain database reorganization operations can be time-consuming and will impact the availability of objects being reorganized. Use database object reorganization selectively. For example, tablespace reorganizations can often be avoided by reorganizing only selected tables and indexes.

When to use the Reorg Wizard

Over time, your database's space usage can become fragmented and inefficient. The Reorg Wizard can be used for efficient space management.

Specifically, you may want to use the Reorg Wizard when one or more of the following situations occur:

Any of these factors may impact the overall performance of the database.

Accessing the Reorg Wizard

You can start the Reorg Wizard in context of an object that you want to reorganize or in standalone mode. You can start the Reorg wizard in context to an object by selecting an entire tablespace or by selecting a specific database object. You can also start the Reorg Wizard in standalone mode from the Oracle Enterprise Manager console Tools=>Tuning Pack menu or from the Oracle Enterprise Manager console's Tuning Pack toolbar.


In order to launch the Reorg Wizard, the database and node preferred credentials must be set. 

Reorganizing an Entire Tablespace

Reorganizing a Specific Schema Object

Reorganization Process

The Reorg Wizard leads you through the process of reorganizing objects within a database. As part of the process you will be able to select the objects targeted for reorganization, optionally change the storage setting and location of these selected objects, and then perform the reorganization immediately or schedule it to be performed at later time.

If you need assistance while using the wizard, select the Help button from any of the pages to detailed information.

The Reorg Wizard guides you through the following steps:

Should a job fail; for example, due to insufficient free space in a tablespace to carry out the reorganization, there are two ways of dealing with the failure:

  1. Correct the error at the destination database (for example, by increasing the size of a tablespace) and proceed with the reorganization. From the Enterprise Manager Console create a new job. To do this, right-click on the failed job and select the Create Like... menu item. Then from the Create Job dialog, click Submit. The job will be resubmitted.

  2. Undo the changes from the reorganization job. From the Enterprise Manager Console create a new job. To do this, right-click on the failed job and select the Create Like... menu item. From the Create Job dialog select the Parameters tab. Change the parameters from 'agent false proceed_and_cleanup' to 'agent false revert' and submit the modified job.

Go to previous page Go to next page
Copyright © 2001 Oracle Corporation.

All Rights Reserved.