Reorganizing Tables with BR*Tools

You can use BR*Tools for Oracle to reorganize tables online.

Note Note

This section describes how you reorganize a table with BR*Tools.

For more information on the approach to table reorganization, see:

End of the note.

You can perform the following types of reorganization:

Reorganize tables online or offline
Reorganize table partitions online or offline
Check tables for reorganization
Clean up tables after aborted reorganization
Convert LONG and LONG RAW fields to CLOB or BLOB online (Oracle 10g or higher)
Convert BasicFile LOBs to SecureFile LOBs online (Oracle 11g or higher)
Convert heap tables to IOTs online
Convert IOTs to heap tables online
Stop reorganization (command-line mode only)
Suspend reorganization (command-line mode only)
Resume reorganization (command-line mode only)

Note Note

Online reorganization is performed with the Oracle package DBMS_REDEFINITION. The SAP system can remain online during this process.
Offline reorganization is performed with the SQL command ALTER TABLE MOVE, which locks the affected tables. Therefore, make sure you stop the SAP system for this process.

End of the note.

For more information, see -f tbreorg.

Prerequisites

You cannot perform online reorganization for tables with LONG or LONG RAW fields but you can convert them to CLOB or BLOB online. After this conversion, you can reorganize all tables online. For more information, see SAP Note 646681.

Procedure

Start the procedure using BRGUI or BRTOOLS, or from the command line:

BRGUI or BRTOOLS:

Choose Segment Management Reorganize tables .
BRGUI or BRTOOLS displays the menu BRSPACE options for reorganization of tables, where you specify the options with which you call BRSPACE.

Set the required options:

Menu Entry	Equivalent BRSPACE Command Option
BRSPACE profile (profile)	-p\|-profile
Database user/password (user)	-u\|-user
Reorganization action (action)	-f tbreorg -a\|-action
Tablespace names (tablespace)	-f tbreorg -s\|-tablespace
Table owner (owner)	-f tbreorg -o\|-owner
Table names (table)	-f tbreorg -t\|-table
Table partitions (tabpart)	-f tbreorg -tp\|-tabpart
Confirmation mode (confirm)	-c\|-confirm
Extended output (output)	-o\|-output
Scrolling line count (scroll)	-s\|-scroll
Message language (language)	-l\|-language
BRSPACE command line (command) BRSPACE	This shows you the BRSPACE -f tbreorg command that is to be executed using the current settings.

Note Note

If required, in Table names you can enter the names of multiple tables. You can use wildcards. For more information, see Selecting Objects in Segment Management with BR*Tools.

You can also specify a list of tables to be processed with reorg_table in the initialization profile init<DBSID>.sap.

In Tablespace names and Table owner, you can specify multiple objects but you cannot use wildcards. BRSPACE processes all the tables in the specified tablespace name(s) or all tables belonging to the specified table owner(s). But these entries and reorganization action entry are optional.

End of the note.

Choose Continue.
BRGUI or BRTOOLS prompts you to start BRSPACE.
Choose Continue to start BRSPACE.

Command line:
Enter at least the following command:

brspace -f tbreorg

You can enter more parameters, including the table names, if required. For more information, see BRSPACE -f tbreorg.

Note Note

Whichever way you start the procedure – with BRGUI or BRTOOLS, or from the command line – you can use quick mode if you know the final object names, in this case the table names. For more information, see How to Use BR*Tools.

BRSPACE starts and you see a message that includes Start of BRSPACE processing. From now on, BRSPACE writes a detail log.

End of the note.

If you have already entered the table names, continue with step 5 (quick mode).

Note

If you have entered multiple tables, BRSPACE displays as confirmation a List of tables for reorganization. If you have not already made a final selection, you can make a selection from this list.

Continue with step 5 (quick mode).

End of the note.

BRSPACE displays the Table reorganization main menu.

Choose Reorganize tables.

BRSPACE displays the table list:

List Entry	Meaning
Pos.	List sequence number
Owner	Table owner
Table	Table name
Pt.	Partitioned
DgPk.	Parallel degree / primary key: “ ” means no “+” means yes / enabled “-” means yes / disabled
Rows	Number of rows
Used [KB]	Space used by the table (HWM – high-water mark)
Data [KB:%]	Amount of data in the table: percentage of occupied space

Select a table or multiple tables.

Example
These examples only apply to input in character mode.

To select the first three tables in the list, enter 1-3.

To select the first and third tables, enter 1,3.

To select the first three tables and the fifth, enter 1-3,5.

To select all tables, enter 0.

End of the example.

BRSPACE displays the menu, Options for reorganization of tables.

Set the required options:

Menu Entry	Meaning
Reorganization action (action)	-f tbreorg -a\|-action
Reorganization mode (mode)	-f tbreorg -m\|-mode
Create DDL statements (ddl)	-f tbreorg -d\|-ddl
New destination tablespace (newts)	-f tbreorg -n\|-newts
Separate index tablespace (indts)	-f tbreorg -i\|-indts
Parallel threads (parallel)	-f tbreorg -p\|-parallel
Table/index parallel degree (degree)	-f tbreorg -e\|-degree
Category of initial extent size (initial)	-f tbreorg -l\|-initial
Sort by fields of index (sortind)	-f tbreorg -r\|-sortind
Index for IOT conversion (iotind)	-f tbreorg -r\|-iotind
Compression action (compress)	-f tbreorg -c\|-compress
LOB compression degree (lobcompr)	-f tbreorg -lc\|-lobcompr
Index compression method (indcompr)	-f tbreorg -ic\|-indcompr

Note Note

There is no SQL command line here because the online reorganization is performed in several steps by an Oracle package, which contains multiple procedures.

End of the note.

To start processing with the selected options, choose Continue.

Result

Check the results in the BRSPACE logs:

The summary log space<DBSID>.log displays the return code.
The detail log s<encoded timestamp>.idr displays the details.

For more information on how to view the logs with BR*Tools, see Showing Profiles and Logs with BR*Tools.

For online reorganization, the file ddl.sql is created in the subdirectory <encoded timestamp> of the directory if the option -d|-ddl is set to a value other than no. The file contains the Data Definition Language (DDL) statements used for the creation of interim tables during the reorganization. For more information, see Reorganization. If the -d|-ddl option is set to only, then only DDL statements are created and the actual reorganization is not performed.

More Information

For more information about LOB converison and table compression, see SAP Note 1431296. For more information about conversion between heap-organized tables and IOTs, see SAP Note 1325304.