Session 5: Correcting Space-Related Problems Using Space Analyst for Oracle

Go Up to Space Analyst for Oracle tutorial

After you have identified space-related bottlenecks in your database with Space Analyst, you can use a number of different tools and facilities within the product to eliminate each storage problems.

Correcting Storage Structure ‘out of space’ Problems for Space Analyst for Oracle

Space Analyst can help you quickly remedy tablespaces that are running out of room by:

• Resizing an existing tablespace to have more room.
• Adding a new datafile to an existing tablespace.
• Setting the Autoextend attribute for one or more of a tablespace’s datafiles to allow for automatic growth.

To manage these functions, do the following:

1. Select the Database Overview tab.
2. In the Tablespace Space Overview section, right-click the graph, and then select Toggle Display.
3. Select the tablespace that is running low on free space.
4. Select the Open toolbar button or the Open shortcut menu option.

   DBArtisan opens a Tablespace Editor.

In the Tablespace Editor, you can remedy the tablespace problem three ways:

• To resize an existing tablespace, open the tablespace’s datafile in the editor (using the Edit button) and change the datafile’s size to a new, larger size. Then close the editor and select the Alter toolbar button to make the change permanent to the database.
• To add a new datafile to the tablespace, select the Add button in the Datafile tab of the tablespace editor, and specify the new datafile’s attributes. Once complete, close the Add Datafile dialog and select the Alter toolbar button to make the change permanent to the database.
• To set Autoextend for one or more of a tablespace’s datafiles, open each datafile in the datafile editor by selecting the Edit button. Once in the Modify Datafile dialog, select the option for Auto Extend and specify other attributes as needed. When complete, close the dialog and select the Alter toolbar button to make the change permanent to the database.

Correcting Storage Structure Fragmentation Issues Using Space Analyst for Oracle

Space Analyst can help you quickly fix tablespaces that are suffering from fragmentation by:

• Coalescing adjacent free space fragments (honeycombs) in a tablespace
• Reorganizing a tablespace to eliminate free space bubbles
• Converting a dictionary-managed tablespace to locally-managed

To coalesce free space fragments in a tablespace, follow these steps:

1. On the Database Overview tab, select any tablespace that appears to be suffering from fragmentation problems.
2. Select the Open toolbar button or the Open shortcut menu option.

   DBArtisan opens a Tablespaces Editor.

3. To view fragmentation details for the tablespace, click the Space tab of the Tablespaces Editor.
4. To combine adjacent free space pockets into singular free space chunks, click the Coalesce button.

   Note: The fragmentation score of the tablespace does not change after a coalesce operation, then the tablespace likely suffers from bubble fragmentation. This can be verified by viewing the tablespace map for the tablespace.

To reorganize any tablespace suffering from bubble fragmentation, do the following:

1. On the toolbar, click the Open Wizard button.

   Space Analyst opens the Reorganization Wizard.

2. Select the New Reorganization option, and then click Next to continue.

   Space Analyst opens the next panel of the Reorganization Wizard.

3. Select Reorganize a Tablespace, and then click Next to continue.

   Space Analyst opens the next panel of the Reorganization Wizard.

4. Select one or more tablespaces you would like to reorganize, and then click Next to continue.

   Space Analyst opens the next panel of the Reorganization Wizard.

5. Select a scratch tablespace that temporarily holds the reorganized tablespace’s objects.
6. Select the option to perform a space analysis so you can be assured that the scratch tablespace has enough room to temporarily hold all of the reorganized tablespace’s objects.
7. If the selected scratch tablespace does not contain enough free space, select another tablespace to serve as the scratch tablespace.
8. Click Next to continue.

   Space Analyst opens the next panel of the Reorganization Wizard.

9. You can choose to perform no customizations for the reorganization, or you can select various global or object-level options to customize your reorganization.
10. Click Finish.

    Space Analyst builds a reorganization script to reorganize your selected tablespace. Note that while the script is being constructed, you can still perform other work in Space Analyst or DBArtisan. Just minimize the script build window and continue to do other work in DBArtisan. You can check back whenever you’d like to see if the script is ready.

After the reorganization script is complete, you can choose to view/run the script in a DBArtisan ISQL window, schedule the script for later execution, or immediately execute the reorganization.

If you would like to convert a dictionary-managed tablespace to locally-managed, you can do one of the following:

• Create a new locally-managed tablespace in DBArtisan and then use the Reorganization Wizard to permanently relocate all objects from the dictionary-managed tablespace to the new locally-managed tablespace. This is the cleanest method for converting a dictionary-managed tablespace to locally-managed.
• Choose the option in Space Analyst that allows a quick conversion of an existing dictionary-managed tablespace to a locally-managed tablespace. This option is only available for Oracle versions 8.1.6 or later. This option does not defragment an existing dictionary-managed tablespace, but converts it so that all space management is conducted through the tablespace’s datafiles instead of the data dictionary and ensure that any new objects added to the tablespace conform to the tablespace’s storage attributes.

Correcting Space-Related Object Problems for Space Analyst for Oracle

Space Analyst can help you quickly fix objects that are experiencing space problems by:

• Modifying various storage-related properties for an object.
• Reorganizing the object to eliminate space inefficiencies.

Storage properties that are affecting the availability of an object (for example, the next extent or maximum extent values for a table or index) can easily be altered by using the integration that exists between Space Analyst and DBArtisan. For example, to modify a table’s next extent value, do the following:

1. On the Tablespace Map tab or the Table Details grid on the Tables tab, select a table you would like to alter.
2. Select the Open toolbar button or the Open shortcut menu option.

   DBArtisan opens a Tablespaces Editor.

3. Select the Storage tab.
4. Change the numeric value for the table’s next extent.

   Note: You can not change next extent values for tables that exist in locally-managed tablespaces.

5. On the toolbar, click the Alter button.

   DBArtisan displays the DDL that change the table’s next extent value.

You can easily reorganize one or more objects with the Reorganization Wizard. To reorganize any table or index that in any grid, do the following:

1. Select the object(s).
2. On the toolbar, click the Reorganization Wizard button, or right-click and select Reorganize.

   Space Analyst opens the Reorganization Wizard.

You can customize object reorganizations by using either the global or specific object customization options in the Reorganization Wizard. For example, you can perform online reorganizations of indexes or index-organized tables.

Space Analyst’s Reorganization Wizard can also intelligently identify objects that need to be reorganized. For example, to reorganize tables in your database that have chained/migrated rows, do the following:

1. On the toolbar, click the Reorg Wizard button.

   Space Analyst opens the Reorganization Wizard.

2. Select the New Reorganization option, and then click Next to continue.

   Space Analyst opens the next panel of the Reorganization Wizard.

3. Select Repair tables with chained/migrate rows, and then click Next to continue.

   Space Analyst runs diagnostics against your database to find any table or table partition that suffers from chained rows. Space Analyst displays the objects under either the Tables or Table Partition(s) node in the Reorg Wizard explorer tree. You can then select one or all table objects and continue with the reorganization process.