Specify Partition Names
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
- Specify names for multiple partitions in an object
- Select the table or index in the Object list.
- Click in the Partition Name field for a partition and modify its current name. Repeat this step for each partition name you want to modify. Names for partitions in the same object can use different formats.
- Specify names for a partition and its subpartitions
- Select the partition in the Object list.
- Click in the Partition field and modify the partition’s current name.
- Click in the Subpartition Name field for a subpartition and modify its name. Repeat this step for each subpartition name you want to modify. Names for subpartitions in the same partition can use different formats.
- Specify name for one subpartition
- Select the subpartition in the Object list.
- Click in the SubpartitionName field and modify the current name.
- Click Next to proceed to the next page. See Select Scripting Options for next steps.
Select Scripting Options
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
- After specifying options on the first page of the Reorg Manager, click Next to open the Scripting Options page.
- Or in the Partitioning Wizard, after reviewing the Partition Names page, click Next.
-
In the Scripting Options page, specify parallel query, dependency, and switch options. Review the following for additional information:
Tables 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.
- When a table is reorganized with ALTER TABLE…MOVE, its indexes can be recreated or rebuilt with the Rebuild where possible option.
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.
- Rebuild ONLINE—(Standard Reorg only) Indexes can be rebuilt offline or online. When an index is rebuilt online, users can update its table throughout the rebuild process. Select this option if you want to allow DML access to associated tables.
Compile Dependencies Select this option to compile dependencies on the tables being reorganized.
- Compile n objects at the same time—Select this option to speed up the compile process with parallel processing by QSA.
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.
See More About Scripting Options (LiveReorg) for more information.
Switch Options (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.
Analysis 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.
- Click Next to proceed to the next page. For next steps, see Customize Object Allocations.
Parallel Processing for Indexes
There are two types of parallel processing you can use for indexes, and they can be used together. Each works in a different way:
- Oracle parallel processing uses multiple server processes to reorganize one index at a time. All are applied to the same index. If degree of parallelism for a reorg is four, all four parallel processes are applied to the first index being reorganized. All four are then applied to the next index being reorganized, and so on. To use this option, select Use Parallel Query Option.
- QSA Server Agent parallel processing uses multiple processes to reorganize multiple indexes at the same time. One QSA process is used per index. If three QSA processes are being used, one process is applied to the first index in a series of indexes, one is applied to the second index in the series, and one is applied to the third index in the series. To use this option, select Create n indexes at the same time.
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.
Customize Object Allocations
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
- After specifying options in the Scripting Options page, click Next to open the Customize Object Allocations page.
-
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:
Object Properties Space Saving tab
Use these fields to modify physical attributes and compression methods.
For Tables
Compress—Select to use compression. Then select a compression method.
Row Store Compress—(Oracle 12c/19c)
- Basic—Select to use ROW STORE COMPRESS BASIC to enable basic table compression.
- Advanced—Select to use ROW STORE COMPRESS ADVANCED to enable Advanced Row Compression.
Column Store Compress—(Oracle 12c/19c) Enabled only if underlying storage supports Hybrid Columnar Compression (such as on Oracle Exadata).
- Query—Select to use COLUMN STORE COMPRESS FOR QUERY to enable query compression.
- Archive—Select to use COLUMN STORE COMPRESS FOR ARCHIVE to enable archive compression.
For Indexes
Compress—Select to use compression. Then select a prefix length.
- Advanced Low—(Oracle 12c/19c) Select to use Advanced Index Compression. Available only if supported in the underlying Oracle database.
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.
Detail tab
Use these fields to modify logging, parallelism, indexing, and other object properties.
Indexing—(Oracle 12c/19c)
- For Tables. Use this field to specify which partitions are indexed by turning INDEXING ON or OFF. The table-level setting can be overwritten by each partition-level setting.
- For Indexes. Use this field to indicate whether the selected index is PARTIAL or FULL.
In-Memory
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.
- MemCompres—Select to use in-memory compression. Then select the level of compression to use.
- Priority—Select a priority to use for this table when populating tables in the IM column store.
- Distribute—Specify how the table is distributed among Oracle RAC instances.
- Duplicate / All—Select to duplicate data on another or on all Oracle RAC instances.
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.
Attribute Clustering
Use these fields to apply or modify attribute clustering.
Attribute Clustering—Select this option to apply attribute clustering to the reorganized table.
- Specify the type of attribute clustering to apply: Linear Order or Interleaved Order.
- Select the operations for which clustering is triggered: on Load and/or on Data Movement.
Columns—Click to specify one or more columns on which to base clustering.
- Add Group—Click to specify a column group if necessary. Enabled for Interleaved Order clustering.
Joins—Click to specify one or more joined columns on which to base clustering (join attribute clustering).
LOB tab
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.
Deprecated tab
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).
Encryption tab
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. DataPump (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.
See Select Global Reorganization Options for more information.
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. Add Index (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.
Segment Allocation Use this section to view current allocation, as well as post-reorganization allocation estimates.
- Click Next to proceed to the next page.
- For a LiveReorg, see Choose a Scheduling Method (LiveReorg)for next steps.
- For a Standard Reorg or for the Partitioning Wizard, see Schedule Script Execution in Wizards for next steps.
Schedule Script Execution
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:
- View (or run) the script in the Editor
- Schedule the script to run once
- Execute the script immediately (schedule script to run immediately)
- Store the script without scheduling it
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
- Click View in Editor to open the script in the Editor window to review it before you schedule it.
-
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.
- Select a date and time at which you want the scheduled script to run.
- Enter a name for the script.
- Click Submit. Space Manager stores the scheduled script and displays a confirmation message.
- Click Yes in the prompt to open the Script/Job Monitor (or select Manage | Job/Script Monitor).
- Your script displays in the list of scripts/jobs in the Job Monitor. If not, click
to refresh the list.
- Use the Script/Job Monitor window to manage your script. You can reschedule, execute, or delete the script. You can monitor status of the script or change the approval option (automatic or wait for approval). You can open the script or view script logs. See Using Job Monitor to Monitor Scripts for more information.
- After the script is stored, you can close the Reorg Manager or Partitioning Wizard.
To execute the script immediately
- Select Execute immediately to run the script immediately, as soon as it is stored in the Space Manager repository.
- Enter a name for the script.
- Click Submit. The script is scheduled to run immediately by QSA. Monitor script execution from the Scripts/Job Monitor.
To store the script without scheduling it
- Select Store without scheduling to store the script and schedule it later from the Script/Job Monitor.
- Enter a name for the script.
- Click Submit. Space Manager displays a confirmation message that the script is successfully stored.
- Click Yes in the prompt to open the Script/Job Monitor (or select Manage | Job/Script Monitor).
- Your script displays in the list of scripts/jobs in the Job Monitor. If not, click
- To schedule the script, right-click the script and select Schedule. Select a date and time to execute the script.
- Or you can right-click the script and select Properties. Then select one of the Scheduling Info options in the Script Attributes tab.
Tip: Click Auto-Refresh in the Script/Job Monitor window to specify a frequency at which to automatically refresh the scripts list.