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:
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.
Before you can use the Partitioning Wizard for a database, the following requirements must be met:
Live reorganizations with the Partitioning Wizard automatically use a subset of the features available for live reorganizations with Reorg Manager:
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.
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.
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.
Select objects and launch the Partitioning Wizard
Use the Partitioning Method page to specify partitioning method, number of partitions, and index locality:
Use the Partitioning Keys page to specify the columns to use in the partitioning keys that defines partitions and subpartitions.
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.
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.
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.
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.
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.
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.
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.
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.
Monitor script execution from the Script/Job Monitor and its Live Reorg Detail pane:
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.
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.
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.
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
Step 2: Launch 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.
For a detailed list of objects not supported by the Partitioning Wizard, see What is Not Supported for the Partitioning Wizard.
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.
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:
Note If an object is already partitioned, the Partitioning Wizard displays its current partitioning scheme. You can modify this as needed.
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:
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.
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Nutzungsbedingungen Datenschutz