Use the first page of the Reorg Manager to specify target tablespaces and to specify the table switch style (Live Reorg only).
You can specify one target tablespace to be used for all tables, one to be used for all indexes, and one to be used for all LOBs. If necessary, you can override this setting for individual objects in the Custom Object Allocations page of the Reorg Manager.
Tip: You can save Reorg Manager settings as a Reorg Plan to edit and reuse later. See Save Reorg Manager Settings for more information. You can also use saved settings to automate reorganization script execution. See About Scheduling a Recurring Reorganization for more information.
Select table switch mode (LiveReorg only)
If you are creating a LiveReorg script, on the first page of the Reorg Manager (see Launch Reorg Manager for more information) in the LiveReorg Table Switch Mode section, select the switch mode (style). Switch mode determines how the switch from the original table to the reorganized copy table is made.
To use the default setting, which is a combination of online and T-Lock switch, take no action.
With the default setting, the switch mode is determined dynamically. The online switch is used for all tables supported for this switch style. The T-Lock (trigger lock) switch style is used for objects that are supported for LiveReorg, but not supported by the online switch. See What is Not Supported for Live Reorganizations for a list of objects not supported by the online switch.
For more information about switch styles, see Switch Mode.
To use the online switch only, select Exclude tables requiring the T-Lock switch (online switch only).
Select this mode to use the online switch for all tables supported for this switch style. Unsupported objects are excluded from a reorganization. When the online switch is used, the table being reorganized remains available during the switch.
See What is Not Supported for Live Reorganizations for a list of objects not supported by the online switch.
Select target tablespaces
On the first page of the Reorg Manager (see Launch Reorg Manager for more information), use the Target Tablespaces section to select whether or not to move objects to another tablespace. You can also use this section to specify a different tablespaces to use during reorganization (Standard Reorg only). For more information about target tablespaces, see About Selecting Target Tablespaces.
Review the following for additional information:
| Tables |
Leave in Original Tablespace—Select to leave tables in their current tablespace. Reorganize In Place—(Standard Reorg only) Select this option to reorganize tables using space in their current tablespace. Use Interim Tablespace—(Standard Reorg only) Select this option to leave tables in their current tablespace but reorganize them using free space in another tablespace. Then select the other tablespace. Tables are copied to the current tablespace after they are reorganized. Relocate to Tablespace—Select to move tables to a different tablespace. Then select the tablespace from the list. |
| Indexes |
Leave in Original Tablespace—Select to leave indexes in their current tablespace. Relocate to Tablespace—Select to move indexes to a different tablespace. Then select the tablespace from the list. |
| LOBs and LOB Indexes |
Leave in Original Tablespace—Select to leave LOBs and LOB indexes in their current tablespace. Reorganize In Place—(Standard Reorg only) Select this option to reorganize LOBs using space in their current tablespace. Use Interim Tablespace—(Standard Reorg only) Select this option to leave LOBs in their current tablespace but reorganize them using free space in another tablespace. Then select the other tablespace. LOBs are copied to the current tablespace after they are reorganized. Relocate to Tablespace—Select to move LOBs and LOB indexes to a different tablespace. Then select the tablespace from the list. |
|
In the Relocate to Tablespace field, the list displays names and free space for all tablespaces in a database except offline and read-only tablespaces. | |
Tip: Select the Use Interim Tablespace option to defragment a tablespace during a standard reorganization. A tablespace is defragmented when all of its objects are included in a reorganization.
Specify FastCopy or DataPump data movement method
FastCopy (Oracle 11)
Use FsCopy—Use this option to specify whether or not to use FsCopy. Review the following:
Select this check box to use FsCopy for all tables that are supported for this method. Tables that are not supported are excluded from reorganization.
Caution: Care should be taken when selecting this method. FsCopy and DataPump are not always the most efficient data movement methods and should be used only for large tables where table copy might cause Oracle errors.
See About Data Movement Methods for more information about this option.
Use DataPump for all tables—Select this option to use DataPump for all tables that are supported for this method. Tables that are not supported are excluded from reorganization.
Caution: Care should be taken when selecting this method. FsCopy and DataPump are not always the most efficient data movement methods and should be used only for large tables where table copy might cause Oracle errors.
See About Data Movement Methods for more information about this option.
If necessary, you can override this setting for individual objects in the Customize Object Allocations page of the Reorg Manager. See Customize Object Allocations.
Note: For the exported data, FastCopy and DataPump use the location/locations specified in the WORKDIR and OVERFLOW_DIR_nn parameters. See QSA Server Agent Parameters for more information.
After proceeding to the next page of the wizard
View any messages that display when you proceed to the next window. One message identifies objects that Reorg Manager is excluding. (Their names are displayed in gray.) Another message identifies tables with constraints that can be validated for use in posting live transactions. See Respond to Checks for a Posting Index for more information. Reorg Manager excludes the following:
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:
| 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.
|
| 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.
|
| Compile Dependencies |
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. 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.
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:
| 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)
Column Store Compress—(Oracle 12c/19c) Enabled only if underlying storage supports Hybrid Columnar Compression (such as on Oracle Exadata).
For Indexes 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. |
|
Detail tab |
Use these fields to modify logging, parallelism, indexing, and other object properties. Indexing—(Oracle 12c/19c)
|
|
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.
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.
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). |
|
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. |
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
