Use the Partition Names window to specify names for individual partitions and subpartitions, including local index partitions and subpartitions. This window provides the second and final opportunity to specify names in the Partitioning Wizard. (The first is provided in the Partitioning Method page.)
Names are stored in uppercase characters if you do not enter them with double quotes. Each name can have up to 30 characters. That maximum is shared by all parts of a name, including numbers and parent names. If all parts add up to more than 30 characters, certain parts are truncated. However, sequential numbers are always included.
To specify names for individual objects
You can use the Scripting Options page of the Reorg Manager and Partitioning Wizard to increase reorganization speed or to select the switch on approval option.
To select scripting options
In the Scripting Options page, specify parallel query, dependency, and switch options. Review the following for additional information:
Parallel Query Option—Use parallel query options to speed up reorganization with Oracle’s parallel processing. This type of parallel processing uses multiple server processes to reorganize each table in a script.
Insert—This option lets you parallelize the INSERT statements used to insert data into copy tables. When CREATE TABLE AS SELECT is the SQL used, the Insert option parallelizes creation of the copy table and insertion of data.
Select—This option lets you parallelize the SELECT statements used to select data from original tables.
The maximum number of parallel processes specified should be no more than the number of CPUs in your system. The maximum number Oracle uses is determined by its PARALLEL_MAX_SERVERS initialization parameter.
Unrecoverable—Select this option to speed up reorganization using Oracle’s NOLOGGING mode. This increases reorganization speed by turning off writing to redo logs as tables are recreated. Immediately after a NOLOGGING reorganization, you should back up reorganized tables.
Use Alter Table Move where possible—(Standard Reorg only) Select to use the ALTER TABLE…MOVE statement with the SQL data-movement method during a standard reorganization. It moves table data into a new segment in one move. Constraints and dependencies do not need to be recreated or recompiled.
|Indexes||Use Parallel Query Option—Use parallel query options to speed up reorganization with Oracle’s parallel processing. See Parallel Processing for Indexes for more information.
Create n indexes at the same time—Enter a number from two to 20 to use QSA parallel processing. See Parallel Processing for Indexes for more information.
Unrecoverable—Select this option to speed up reorganization using Oracle’s NOLOGGING mode. This increases reorganization speed by turning off writing to redo logs as indexes are recreated. Immediately after a NOLOGGING reorganization, you should back up reorganized tables and their indexes.
Rebuild where possible—(Standard Reorg only) Select to specify ALTER INDEX…REBUILD as the SQL to use for non-partitioned indexes. Rebuilding an index is faster than dropping and recreating it.
Select this option to compile dependencies on the tables being reorganized.
How much time is required to recompile dependencies depends on the number of dependencies, how busy the database is, and system performance. The time required can range from seconds to hours.
|Drop original table after successful reorganization||
(LiveReog and Partitioning Wizard only) This check box is selected by default so that original tables and indexes are dropped after they are replaced with their reorganized copies. Original objects are also purged from the Recycle Bin.
Do not select this option if you want to save original tables and indexes. Original objects are preserved in their original tablespaces under modified names.
(LiveReorg only) See More About Scripting Options (LiveReorg) for more information about switch options.
Automatically—Select if you want the switch to be made automatically, as soon as a copy table is ready. When multiple tables are included in one reorganization script, the switch is made automatically for each table in turn.
Upon user approval—Select if you want the switch to made after a user approves the switch. Approval should be given as soon as possible after a table is ready for the switch. Until approval is given, QSA keeps the original table in sync with the copy table.
When multiple tables are included in a reorganization script, multiple approvals must be given.
Approval is given from the Scripts/Job Monitor. See Approve the Switch for more information.
Between date/time—Use this option if you want the switch to be made within a time window. Then specify a start date/time and end date/time for the window. The switch is made automatically for all tables that are ready for the switch during the time window specified.
This option allows the switch to proceed automatically at a time when activity is light.
Note: If the window ends before the switch is made for all tables in a script, execution aborts with this message: “QSA-20502: Target completion date/time expired.” This happens after 20 “retry operation” attempts are made.
Tip: After a live reorganization script has been stored and scheduled, you can select a different switch option, if needed. See Change Approval Method for Online Scripts for more information.
Collect statistics for the CBO—Select to update Oracle's cost-based optimizer (CBO).
Do not collect statistics for the CBO—Select if you do not want to update Oracle's cost-based optimizer (CBO).
Restore original statistics in data dictionary for the CBO— (Reorg Manager only) Select this option to preserve existing statistics in the Oracle data dictionary instead of collecting statistics.
Note: Statistics are collected using DBMS_STATS. To collect statistics or restore the data dictionary, the DBMS_STATS package must be installed for the database.
See About Statistics Collection Options for more information about these options in the Reorg Manager.
|Segments being reorganized||Provides information about the objects to be reorganized.|
Note: Scripting options for tables are not used for FastCopy (or DBMS_DataPump).
Important: Parallel and NOLOGGING scripting options do not change the permanent parallel and logging attributes for an index. These options are only used during reorganization.
There are two types of parallel processing you can use for indexes, and they can be used together. Each works in a different way:
What happens when the two types of parallel processing are used together? Multiple QSA processes are used to reorganize multiple indexes simultaneously while multiple Oracle parallel query processes are applied to each index. Keep in mind that using the two together increases the number of CPUs needed for a reorganization.
To calculate the number of CPUs needed for parallel processing of indexes, multiply the number of QSA processes specified by the number of parallel server processes that will be used by Oracle (this is twice the number you specify plus one). For example, if you specified 3 QSA processes and 7 Oracle processes will be used by Oracle, 21 CPUs will be needed for reorganizing indexes.
Use the Customize Object Allocations page of the Reorg Manager or Partitioning Wizard to specify options for individual objects.
In the Physical Attributes sections, current values are displayed in gray beside entry fields for new values.
Note: To see the space currently allocated for an object, as well as the post-reorganization estimate, check the Segment Allocation section.
Names are displayed in gray for objects on the Objects to Reorganize list that are not included in a reorganization. Typically, these objects are related to included objects.
Note: The Partitioning Wizard does not display fields for an object if its partitioning is not changed. Instead, it displays a message: “This functionality does not apply to the currently selected object.”
To customize object allocations
In the Customize Object Allocations page, specify storage and space use options for individual objects as needed. You can also override target tablespace settings for an object.
Select an object in the Objects to Reorganize list. Then customize its settings. Review the following for additional information:
Space Saving tab
Use these fields to modify physical attributes and compression methods.
Compress—Select to use compression. Then select a compression method.
Row Store Compress—(Oracle 12c/19c)
Column Store Compress—(Oracle 12c/19c) Enabled only if underlying storage supports Hybrid Columnar Compression (such as on Oracle Exadata).
Compress—Select to use compression. Then select a prefix length.
Note: Pct Free reserves a percentage of data block space for updates to existing rows. For example, if the PctFree setting is 10, 10% of each of an object’s data block is reserved for UPDATES. The space left (minus block overhead) is the space available for INSERTS of new rows and their overhead. Set a higher value for PctFree if existing rows will be updated heavily and updated values will increase row size. This will help prevent chaining of growing rows to other data blocks.
Use these fields to modify logging, parallelism, indexing, and other object properties.
Use these fields to enable or modify In-Memory Column Store options.
In-Memory—Select this option to enable In-Memory Column Store for the selected object.
Column Clause—Select to specify In-Memory Column Store options per column.
Note: In a Live Reorg, the column clause is only supported if the T-Lock switch is used.
Use these fields to apply or modify attribute clustering.
Attribute Clustering—Select this option to apply attribute clustering to the reorganized table.
Columns—Click to specify one or more columns on which to base clustering.
Joins—Click to specify one or more joined columns on which to base clustering (join attribute clustering).
This tab only displays if you have LOB columns.
SecureFile—Select this option to store LOBs using the SecureFile storage method and to enable additional SecureFile options from which to select.
Note: Oracle only stores data in the row if the data is less than 4K. If the data is greater than 4K, then the LOB value is moved into a LOB segment.
Use this tab to modify physical attributes and storage options applicable to features used in older versions of Oracle database, such as for Dictionary-Managed tablespaces.
If you change values for Initial, Next, and PctIncrease, this overrides global values.
Note: Oracle ignores the PCTUSED, FREELISTS, and FREELIST GROUPS parameters for objects in locally managed tablespaces that use automatic segment space management (ASSM).
This tab only displays if you have encrypted objects. It is recommended that you provide the encryption password to ensure that the reorganization is successful.
Note: If you use the exchange partition or online switch to reorganize a table that uses Transparent Data Encryption (TDE), you must supply the encryption password. If you did not provide a password when you created the table, you cannot reorganize the table with methods.
|Long LONG Columns||Indicates whether or not the object includes one or more long LONG columns.|
(Oracle 12c/19c) Use this section to override the global DataPump settings if necessary. You can specify DataPump options for individual objects.
Use DataPump—Select this option to use the DataPump method for the selected object. You can then modify the Max File Size and Parallel Degree options if necessary.
The Use DataPump check box is not editable for tables with LONG columns, regardless of the global setting.
|Tablespace||Use this section to override the global tablespace specification if necessary. See Select Global Reorganization Options for more information.|
|Sorted Reorg||Perform sorted reorg—Select this option if you want to sort a table’s data by a certain index. Then select the index to sort by from the Index to Use field.|
(Reorg Manager) To add an index, select a table and click Add Index. Once you add an index, you can modify or drop it from the Customize Object Allocations page.
Note: If you want to create a partitioned index, it must be locally partitioned. You cannot create a globally partitioned index.
To modify or drop an index, select the index and click Modify Index or Drop Index.
Use this section to view current allocation, as well as post-reorganization allocation estimates.
Use the Schedule Script Execution page of the Reorg Manager or Partitioning Wizard to run, schedule, or store a script. You can also open the script in the Editor to review it (or run it).
Prior to scheduling the script, review space usage information on the Space Usage Summary tab. See Review Space Usage for more information.
From the Schedule Script Execution page you can do any of the following:
Note: For LiveReorg scripts, if you want to schedule the script to run on a recurring basis, return to the Choose Scheduling Method page and select Schedule Script Generation. See Choose a Scheduling Method (LiveReorg) and About Scheduling a Recurring Reorganization for more information.
See About Running and Scheduling Scripts to learn more about your script execution options and to review important considerations and best practices.
To view (or run) the script in the Editor
You can also run the script interactively from the Editor window. See Run Scripts from the Editor for more information.
Important: Live reorganization scripts and scripts that use FastCopy or parallel QSA processes cannot be run from the SQL Editor. They must be scheduled and run by QSA. These scripts contain commands that must be interpreted by QSA. To run a script in QSA, it must be scheduled (even when executed immediately).
To schedule the script to run once
Select Run at to run the script at a certain date and time.
Note: Reorganization scripts should be run as soon as possible after they are generated. If the data structures of the objects in the script are modified between script generation and execution, the DDL and DML statements in the script can cause errors and/or data loss during script execution.
See Schedule Scripts, to learn more about scheduling scripts.
To execute the script immediately
To store the script without scheduling it
Tip: Click Auto-Refresh in the Script/Job Monitor window to specify a frequency at which to automatically refresh the scripts list.