Reorganization of a Tablespace with Data Files 

SAPDBA actions to reorganize a tablespace with data files (script name):

The data transport method Create table ... as select is not available for this type of reorganization.

Preparation Phase

1. Generates the <timestamp> subdirectory in the working directory and the log <timestamp>.rtd .

2. Defines the number, path and sizes of the new data files of the tablespaces (and possibly of the corresponding index tablespaces). SAPDBA default: if possible a file with the path specification of the first old file of the tablespace which is as large as the sum of all the old data files (see Tablespace Reorganization with Data Files in Reorganization Type).
Exceptions:
a) the data does not fit in one file: multiple files are suggested;
b) Raw devices used: SAPDBA suggests the old distribution; you may have to make manuall adjustments.

3. Checks whether there will be sufficient space for the files to be reorganized in the file systems of the new data files of the tablespace;
see Freespace Check During Reorganization of a Tablespace with Data Files.

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

5. Checks whether the extents to be reorganized fit in the data file(s) of the tablespace; see Freespace Check During Reorganization of a Tablespace.

6. 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 all tables and indexes of the tablespace ( exp<TSP>.sh or unl<TSP>.sh and if necessary exc<TSP>.sh ); 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. Safety mechanisms ( swi<TSP>.sql ).
  6. Deletes the tablespace ( dro<TSP>.sql ).
  7. Deletes all files and directories that belong to the tablespace ( del<TSP>.sh ) when each subdirectory only contains one file. If additional, for example non-database, files are also contained in a subdirectory, only the data file is deleted.
  8. Creates the new directories and files.
  9. Merges all the remaining adjoining freespace fragments of the entire database using Oracle functions.
  10. Creates a new tablespace ( tsp<TSP>.sql ).
  11. Recovers the tables, if necessary with comments ( tab<TSP>.sql )
  12. Imports the table files, indexes, grants, and constraints ( imp<TSP>.sh or ldr<TSP>.sh and if necessary imp<TSP>.sh , ind<TSP>.sql , con<TSP>.sql , grants.sql ).
  13. If necessary, sets back the names of the tables temporarily renamed (under step 1) for data protection ( ren<TSP>.sql , see HideTab option).