Create table ... as select (alongside ORACLE EXP/ IMP, SAPDBA LOAD/ UNLOAD and ORACLE SQL*Loader) is a method for making a data transport during a reorganization, so significantly reducing the total duration of the reorganization. You can use it for all types or reorganization except for table with long columns and tablespaces reorganizations with data files. This method is particularly time-saving when you transport a single table into another tablespace.

If the command Create table ... as select is interrupted (for example, if there is sufficient free space, but too much fragmentation) the affected table is not deleted, so guaranteeing data security at all times. If this happens, the reorganization process is continued with the next table.

Note that the amount of memory space required by a table to be reorganized rises temporarily in the accompanying tablespace, as the old and the new tables coexist during the copying process. In addition to this, the free space of a tablespace is not combined optimally, since the tables are not deleted simultaneously.

Tables whose names are already 30 characters long are not reorganized.

SAPDBA creates a new name for the table to be reorganized with the new parameters by appending the # character and copies the data directly from the old table to the new table. If this is completed successfully then the old table is deleted and the new table is renamed with the old name. In this way a single table, or all tables in an object list or tablespace, which are to be reorganized (reorganization without data files) are processed.