Reorganization of an Object List (SAP Library - Database Administration (Oracle) with SAPDBA)

Reorganization of an Object List

SAPDBA actions to reorganize tables/indexes which were defined in a list (script name):

Preparation Phase

Generates the <timestamp> subdirectory in the working directory and the log <time stamp>.rli.

Generates the SQL and shell scripts and the restart file restart.rli in the < timestamp > subdirectory (see Restart File for Reorganizations).

Checks whether the objects to be reorganized will fit in the freespace which will be available after the objects have been deleted.

The list comprises one object per tablespace:

See Freespace Check During Reorganization of a Single Object.

The list comprises several objects per tablespace:

See Freespace Check During Reorganization of a Tablespace.

SAPDBA: Reorganization Menu

Main Phase

1. If necessary, temporarily renames the tables to be reorganized or to be affected by the reorganization ( nam<TSP>.sql , see HideTab option in the SAPDBA: Reorganization Menu).

2. Only when exporting to tape (due to interactive export mode):
Deletes all the index statistics (alt<TSP>.sql) as well as any constraints (drc<TSP>.sql).

3. Exports the tables (exp<TSP>.sh and if necessary exc<TSP>.sh or unl<TSP>.sh - not in the case of an index reorganization or with Create table ... as select); see also Parallel Export/Import.

4. If necessary, performs a test import to check the readability of the export dump files ( imx<TSP>.sh , see CheckExp option in the SAPDBA: Reorganization Menu).

5. Deletes the objects (dro<TSP>.sql); not with Create table ... as select.

6. Merges all the remaining adjoining freespace fragments of the entire database using Oracle functions.

7. Recovery of the tables, if necessary with comments. With Create table ... as select the old table is deleted in this step and the new table is renamed with the old name ( tab<TSP>.sql ).

8. Imports the table files (possibly with indexes, constraints, and grants). (imp<TSP>.sh or ldr<TSP>.sh and possibly imc<TSP>.sh, ind<TSP>.sql, con<TSP>.sql grants.sql). Only the indexes and maybe the constraints are recreated if there is an index reorganization ( ind<TSP>.sql , con<TSP>.sql ).

9. If necessary, sets back the names of the tables which were temporarily renamed for data security (in step 1) ( ren<TSP>.sql , see HideTab option in the SAPDBA: Reorganization Menu).