Space Manager with LiveReorg 9.1 - User Guide

About the Partitioning Wizard

Designed to streamline the partitioning process, the Partitioning Wizard lets you easily divide existing tables and their indexes into smaller units for better performance and easier maintenance. You can use it to:

• Convert nonpartitioned tables and indexes into partitioned tables and indexes. For example, you can partition a very large table across multiple tablespaces and storage devices for better I/O balancing.
• Convert partitioned tables and indexes into nonpartitioned tables and indexes. For example, you can unpartition a table that is too small to be partitioned and is not likely to grow in the future.
• Change the partitioning scheme for a table that is already partitioned. For example, you can add or remove partitions, change the partitioning method, or change the partitioning key.

After you define a partitioning scheme, the Partitioning Wizard automatically generates a script that contains all the Oracle commands needed to implement the scheme. The script applies your changes to a table and its indexes with a live reorganization. Because the reorganization takes place online, users and applications can continue transactions against a table as it is restructured. This allows you to perform critical database maintenance without interrupting access to data.

The Partitioning Wizard supports all of the partitioning methods available for Oracle. However, you cannot start with the parent of a reference-partitioned table. Additionally, you can start with system- or reference-partitioned tables, but you cannot produce them.

See Launch Partitioning Wizard to get started using the Partitioning Wizard.

Requirements for the Partitioning Wizard

Before you can use the Partitioning Wizard for a database, the following requirements must be met:

• The LiveReorg option must be licensed.
• The QSA Server Agent must be installed, current, and running for the database.
• Oracle’s Partitioning Option must be installed and licensed for the database.

How Live Reorganizations Are Performed

Live reorganizations with the Partitioning Wizard automatically use a subset of the features available for live reorganizations with Reorg Manager:

• SQL is the data movement method. The SQL statements available are CREATE TABLE AS SELECT and CREATE TABLE and INSERT. FastCopy (or DBMS_DataPump) is not used.
• All objects are dropped and recreated.
• Entire tables and their indexes are reorganized. Partitions and subpartitions are not reorganized individually. Indexes cannot be reorganized without their tables.
• The switch is made at the parent table level. It is not made individually for partitions or subpartitions.
• The T-Lock switch is always used. The online switch is not used. If an object is not supported for the T-Lock switch, it is not included in a partitioning script.

Because the Partitioning Wizard automatically uses certain live reorganization features, it does not display windows that contain the LiveReorg option, switch styles, and partition reorganization methods.

The only live reorganization options you select are the following: the option that determines whether original objects are dropped and the option that determines how the switch proceeds from original table to reorganized copy table proceeds. It can begin automatically, upon user approval, or in a time window.

See How Live Reorganizations Work for more information.

Smart Start for the Partitioning Wizard

This Smart Start provides an overview of all the major steps involved in using the Partitioning Wizard.

Note: The Partitioning Wizard is only available when the LiveReorg option is licensed.

1. Partitioning Wizard Defaults

   Specify default settings for partitioning options in the Options dialog. To open this dialog, select Tools | Options | Partitioning Wizard. You can override default settings from the Partitioning Wizard as needed.

2. Select objects and launch the Partitioning Wizard

   Note: See Launch Partitioning Wizard for more information.

3. Specify Partitioning Method

   Use the Partitioning Method page to specify partitioning method, number of partitions, and index locality:

   • For tables—Specify partitioning method (range, list, or hash) and number of partitions.
   • For partitions—To subpartition a range-partitioned table, specify a partitioning method for partitions (list or hash). Also specify the number of subpartitions each partition should contain. This can vary by partition.
   • For indexes—Specify locality for each index associated with a table:
     • Local—Select this to partition an index in the same way as its table. Each index partition or subpartition corresponds to a certain a certain table partition or subpartition. The partitioning scheme for the table is automatically applied to the index.
     • Global—Select this to partition an index independently of its table. The index can be partitioned or nonpartitioned regardless of its table’s structure. If both are partitioned, they can have different partitioning schemes.

4. Specify Partitioning Key Columns

   Use the Partitioning Keys page to specify the columns to use in the partitioning keys that defines partitions and subpartitions.

5. Specify Partitioning Parameters

   Use the Partitioning Parameters page to set partitioning options for individual objects. These options can be used to automate various aspects of partitioning. Initially, this window displays default settings from Partitioning Wizard options. You can override defaults as needed. The settings selected for a partitioned object apply to all of its partitions or subpartitions.

   Note: If you want to specify target tablespaces for individual partitions and subpartitions, you can do this in the Customize Object Allocation page.

6. Specify Partitioning Key Values

   Use the Partition Key Values page to specify the column values to use in partitioning keys. These define boundaries that determine how rows are assigned to partitions and subpartitions. If you chose to have the Partitioning Wizard set key values, the values are entered automatically. You can override them as needed.

7. Specify Partition Names

   Use the Partition Names page to specify names for individual partitions and subpartitions, if needed. This window provides the final opportunity to specify names. The first is provided in the Partitioning Method page. Options in the first page let you specify names on a global basis or for parent objects. Options in Partition Names let you specify names for individual object.

8. Select script options in the Scripting Options page.

   These options let you increase reorganization speed with Oracle’s NOLOGGING mode and two types of parallel processing. See Select Scripting Options for more information.

9. Set Storage Strategies

   Define a global extent allocation strategy in the Storage Strategy window. This window displays when the SYSTEM tablespace is data dictionary managed. It allows you to size extents and objects on a global basis, for all objects in a script. You can override global values for individual objects in Physical Properties window.

10. Customize Object Allocations.

    Adjust storage for individual objects in the Customize Object Allocations window. If needed, you can override extent values set on a global basis. You can also override global settings for target tablespace and sorted reorganization. See Customize Object Allocations for more information.

11. Check the Space Usage Summary tab to see if target tablespaces and the LiveReorg tablespace have enough free space for a reorganization.

    This tab identifies tablespaces that do not have enough free space overall or enough contiguous free space. To provide free space, select different target tablespaces or adjust storage values. To provide space in the LiveReorg tablespace, add datafiles or increase datafile size with Tablespace Properties. You can leave the Partitioning Wizard open as you do this.

12. Generate a reorganization script and schedule it in the Schedule Script Execution window.

    Partitioning scripts must be run on a scheduled basis. This ensures that they are executed by QSA. Only QSA can process the LiveReorg commands they contain. See Manage and Monitor QSA for more information.

    CAUTION: Reorganization commands must not be modified or removed from a script. If they are changed in any way, a reorganization can fail or unpredictable results can occur. Most commands begin with QUEST_EXEC.

13. Using Job Monitor to Monitor Scripts

    Monitor script execution from the Script/Job Monitor and its Live Reorg Detail pane:

    • Script/Job Monitor—This window displays execution status and executing statements.

    • Live Reorg Detail—This pane displays activity statistics for live transactions during the first three stages of a live reorganization. It also identifies which stages are complete. You can use the statistics to estimate when a table will be ready for the switch.

      Tip: If the switch option is Upon User Approval, check the Script/Job Monitor to see when each table in a script is ready for approval. “Waiting for approval” is displayed in the Status Message column.

14. Approve the Switch

    When a table is ready for the switch to be approved, right-click its script in the Script/Job Monitor and select Approve Switch. The switch from original table to reorganized copy table is made and script execution continues. Monitor execution to see when the next table in the script is ready for approval.

Launch Partitioning Wizard

You can launch the Partitioning Wizard from multiple points in the interface. Select one or more objects to reorganize, then launch the Partitioning Wizard.

Review the following notes about selecting objects.

• When you select objects for the Reorg Manager or Partitioning Wizard, their associated objects are added to the object list. These objects include indexes, LOBs, and IOT overflows. IOTs (index organized tables) are replaced with IOT indexes.
• In most cases, related objects descend from their base objects in a hierarchical view.
• Partitions and subpartitions can be selected from all launch points. When a partition/subpartition is selected in the Partitioning Wizard, the entire partitioned object is added to the object list.

How to Launch the Partitioning Wizard

You can launch the Partitioning Wizard from the following areas in the Space Manager workspace. You can select a single object or multiple objects to partition simultaneously.

Step 1: Select objects to partition

1. In the Explorer window, select a tablespace or owner.
2. In the Segment or Object grid, select one or more objects to partition.
3. Alternatively, you can select one or more objects to partition from the Advanced Search window.

Step 2: Launch the Partitioning Wizard

1. Use the previous method to select one or more objects in the Explorer window.
2. Right-click and select Partitioning Wizard.
3. The Partitioning Wizard opens to the Partitioning Method page. See Specify Partitioning Method for next steps.

Objects Not Supported by the Partitioning Wizard

If objects are not supported for the Partitioning Wizard, they are excluded after you select them. The following messages identify which objects are excluded and why.

• Are not supported for partitioning: Objects that are not supported by Oracle for partitioning are excluded. These objects include tables with LONG columns.
• Cannot be reorganized with the T-Lock switch: Partitioning scripts use the T-Lock switch for all objects. Objects are excluded if they are not supported for the T-Lock switch.
• Do not contain columns that can be used in keys: Objects need at least one column that can be used in the partitioning key. Objects are excluded if none of their columns can be used. Columns that cannot be used in partitioning keys are those defined with the following data types:
  • LOB
  • LONG
  • RAW
  • LONG RAW
  • ROWID
  • TIMESTAMP WITH TIME ZONE
  • User-defined data types (including VARRAY, REF, OBJECT, and NESTED TABLE)

For a detailed list of objects not supported by the Partitioning Wizard, see What is Not Supported for the Partitioning Wizard.

How the Wizard Streamlines Partitioning

One way the Partitioning wizard streamlines the partitioning process is with a graphical user interface that lets you easily define and see a partitioning scheme. Another is by automating tasks such as specifying partitioning key values, sizing partitions, and selecting tablespaces for I/O balancing.

Graphic Features in the Wizard

Wizard windows let you quickly select or enter partitioning method, number of partitions, index locality, partitioning key columns, and partitioning key values. Tabs beside these windows let you review a partitioning scheme as you define it.

The main windows for defining a partitioning scheme are as follows:

• Partitioning Method—This window lets you select partitioning and subpartitioning methods from lists. It also lets you enter number of partitions and subpartitions. You can specify a default number of subpartitions for a table or set a different number for each partition.
• Partitioning Keys—This window lets you specify partitioning key columns by selecting the columns from lists in grids. The lists include all columns in a table or index that are eligible to serve as partitioning columns. They exclude columns that are not supported by Oracle for partitioning keys.
• Partition Values—This window provides grids and lists for specifying partitioning key values. Both identify partitioning key columns and their datatypes. This allows you to see the types of values that can be entered as partition boundaries. The key values grid for range partitions is arranged in columns that let you easily compare the high values for boundaries. The key values lists for list partitions and subpartitions provide a function that lets you retrieve existing values in the partitioning key column. You can then select the values to use for a partition or subpartition.

Note If an object is already partitioned, the Partitioning Wizard displays its current partitioning scheme. You can modify this as needed.

Automated Features in the Wizard

The Partitioning Wizard provides multiple options for automating the process of defining a partitioning scheme. You can specify all values yourself or use wizard options to automatically:

• Specify index locality as local or global.
• Set partitioning key values so that rows are evenly distributed among partitions or subpartitions (upper bounds for range partitions; list values for list partitions and subpartitions).
• Estimate partition sizes based on row count (which is calculated based on partitioning boundaries you specify).
• Validate partitioning key values against existing data to ensure there is a partition or subpartition for every row in a table.
• Specify sampling method and sample size for options that automatically set partitioning key values and estimate partition sizes.
• Set partition and subpartition names using naming patterns you specify.
• Assign partitions to tablespaces in order to balance I/O operations across multiple tablespaces.

As you define a partitioning scheme, the Partitioning Wizard ensures that settings conform to Oracle’s rules. For example, when you enter partitioning key values, the wizard ensures that syntax is valid; that values do not exceed the maximum length, precision, or scale defined for a key column; that high values for range partitions increase in order; and that list partitions do not use duplicate values.

When you change a setting for an existing partitioning scheme, the Partitioning Wizard tries to preserve other settings. If the change to one setting causes another setting to become invalid, the wizard displays a message. You can adjust the invalid setting based on information in the message.