Space Manager with LiveReorg 9.1 - User Guide

Run Scripts from the Editor

Use the View in Editor option in the Reorg Manager (or Partitioning Wizard) to run reorganization scripts interactively from the SQL Editor. As a script runs, you can monitor execution directly from the SQL Editor. You can also use the View in Editor option to open a script and review it before you schedule it.

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 run scripts from the Editor

1. Select View in Editor in the schedule window of Reorg Manager or the Partitioning Wizard. The script is displayed in the SQL Editor.
2. Run the script from the SQL Editor in continuous mode or single-step mode:
   • To run the entire script in continuous mode, click Run in the toolbar or press F9.

   • To run the script in single-step mode (one statement at a time), click Single Step in the toolbar or press F8 from the first statement. Repeat for each statement in the script.

     Note: If the Connection dialog displays, enter the user name and password of an Oracle user with the DBA role or privileges for Space Manager. Then click OK to connect to your database.

3. As the script is executed, the SQL Editor scrolls through script statements. The current statement is highlighted. Check the status bar at the bottom of the window. If a statement is executed successfully, processing time is displayed. If an error occurs, an error message is displayed.
4. When script execution is finished, an execution log for all script statements displays in the Spool tab. View this to see script start and stop times, any errors that occurred, and the output of all executed statements.

Related Topics

About Running and Scheduling Scripts

Schedule Scripts

Schedule Script Execution in Wizards

More Options for Reorg Manager

About Selecting Target Tablespaces

Target tablespace options determine where objects are reorganized and whether they are relocated during reorganization. Objects can be left in their current tablespace or relocated to another tablespace.

When objects are left in place during a standard reorganization, they can be reorganized using free space in the current tablespace or free space in another tablespace on an interim basis. (Interim tablespaces are not used for live reorganizations.)

See also Target Tablespace Considerations for more information about how data movement method, space availability, and speed can factor into choosing a target tablespace.

Specify Target Tablespace Globally or Per Object

Target tablespaces are selected on a global basis in the Target Tablespaces section in the Reorg Manager (see Select Global Reorganization Options). This means they are used for all tables and indexes included in a reorganization. However, you can override global target tablespace for individual objects in the Customize Object Allocations page. See Customize Object Allocations for more information.

One global target tablespace can be selected for tables and another selected for indexes. This allows you to locate tables and their indexes in different tablespaces in order to avoid table/index contention and improve I/O balancing. You can also select one global target tablespace to use for all LOBs and LOB indexes.

Target Tablespace Considerations

• Global target tablespaces for IOTs—The global target tablespace selected for tables is also used for IOT overflows, and IOT overflow partitions.
• Target tablespaces for partitioned objects—All tablespaces chosen for a partitioned object and its partitions or subpartitions must use the same data block size. This is an Oracle requirement.
• Defragmenting a tablespace—If you want to defragment a tablespace, include all of its objects in a standard reorganization. Also select the Use Interim Tablespace option.

When to Leave Objects in Their Current Tablespace

Use the Leave in Original Tablespace option to leave tables, indexes, and LOBs in their current tablespace during a reorganization. In a Standard Reorg, when you leave tables or LOBs in their current tablespace, you can choose whether to reorganize them using space in the current tablespace or space in another tablespace on an interim basis.

When you reorganize using space in the current tablespace, the following space requirements apply:

• For tables without LONGs, the tablespace must have enough space for the largest table being reorganized and a copy of that table. It must also have enough space for the largest set of indexes being reorganized and copies of those indexes.
• For a Standard Reorg on supported tables with LONGs, the filesystem must have enough space for exported data.
• For a LiveReorg on tables where a T-Lock switch is used, the filesystem must have enough space for exported data.

When you reorganize using space in an interim tablespace, the tablespace must have enough concurrent free space for all copy tables it will contain. All are created at the same time.

When to Relocate Objects to a New Tablespace

Use the Relocate to Tablespace option to move tables and indexes to a different tablespace during reorganization. The tablespace must have enough free space for copies of all tables and indexes to be relocated there.

Relocating objects when you reorganize optimizes database layout for efficient use of disk space, better I/O balancing, and improved query performance. For example, separating tables and indexes into different tablespaces distributes I/O requests among datafiles and disks for improved performance of index-based queries.

Best Practices for Locally Managed Tablespaces

Use the following guidelines to relocate tables to locally managed tablespaces:

• If you can accurately predict growth rate for the tables, consider moving them to locally managed tablespaces that use the uniform extent allocation type. Ideally, the uniform extent size should be large enough to prevent allocation of an excessive number of extents for the tables.
• If you cannot predict growth rate for the tables, consider moving them to locally managed tablespaces that use the autoallocate extent allocation type.

Best Practices for SAP Objects

SAP objects should only be relocated to tablespaces that were created with SAP administrative tools. After relocating an SAP object with Space Manager, you should update the SAP ABAP Dictionary. This is recommended in SAP note 154193, “SAPDBA:reorganizations and ABAP-DDIC”.

To update the ABAP Dictionary, run the following SQL statement:

   update sapr3.dd09l
   set tabart = (select tabart
                       from sapr3.taora
                        where  tabspace = '<tsp>')
   where tabname  = '<table>'
     and  as4local = 'A';

Important: Check SAP notes for the latest recommendations on reorganizing SAP objects using external applications.

Target Tablespace Considerations

As you select target tablespace options for a reorganization, consider the object types and datatypes being reorganized, the space available in the database and filesystem, the data movement method used, and the speed of different combinations of options.

Object type and datatype determine which data movement method can be used. If both SQL and FastCopy are used, space and speed can be considered in choosing target tablespace options.

Space Requirements

Space availability and data movement method are a factors to consider when selecting target tablespace options:

• SQL reorganizations use space in the database. Target tablespace options allow you to reorganize objects in their current tablespace or relocate them to a different tablespace. If you choose to leave objects in their current tablespace, you can reorganize them using free space in that tablespace or free space in another tablespace on an interim basis (objects are moved back to their current tablespace during the reorganization process).
• FastCopy reorganizations use space in the database or in the filesystem. Where space is used depends on target tablespace options. If you choose to leave tables in their current tablespace, FastCopy exports data to the filesystem. If you choose to relocate tables to a different tablespace, FastCopy copies data inside the database.

Speed Guidelines

Speed is another factor to consider when selecting target tablespace options. Different combinations of options vary in terms of the number of data moves required. Data moves can be one-way or two-way:

• One-way moves are used for SQL reorganizations in the current tablespace and for SQL and FastCopy relocations to a new tablespace. Data is moved once—from the original table to a copy table (in the current tablespace or a new tablespace). A one-way tablespace move with any data movement method is faster than a two-way move with the same method.
• Two-way moves are used for SQL reorganizations in an interim tablespace and FastCopy reorganizations in the current tablespace. For SQL, the first move is from original table to an interim tablespace. The second move is back to a copy table in the original tablespace. For FastCopy, the first move is an export from the original table to the filesystem. The second move is an import back to a copy table in the original tablespace.