Reorganizing Tables in 8i

Reorganizing tables and indexes are much easier in Oracle8i, with the new clauses available in the ALTER TABLE and ALTER INDEX commands.

The need to reorganize tables arises when you did not plan the size of the extents properly, and the table has grown to many extents. Though experts say, there is not much performance degradation because of having many extents, performance of some operations are directly related to the number of extents, and how they are "scattered" on the disk. 

You may want to reorganize the table to save space, sometimes you would have set a PCTFREE value to the default 10 or more, and the table rows are never updated. Imagine 10% of a big table of size 50GB, that is 5GB you have allocated and never/cannot be used!

Though you can reorganize a table in the same tablespace, it is better to create a new tablespace and "move" all tables to the new tablespace with proper storage parameters. If you reorganize in the same tablespace, there are more chances that the tablespace will get fragmented, that is, lot of room available, but are in such small chunks that cannot be used. Remember that an extent is allocated in contiguous blocks.

The MOVE clause in the ALTER TABLE command can be used to reorganize non-partitioned tables. You can specify new tablespace name and storage parameters. For example, let's consider that you want to reorganize the JOB_STATUS table, which is now stored in the JOB_DATA tablespace. The default storage parameters for JOB_DATA tablespace are INITIAL 512K, NEXT 256K, MINEXTENTS 1, MAXEXTENTS 255, PCTINCREASE 0.

If you specify "ALTER TABLE JOB_STATUS MOVE", a new segment is created for JOB_STATUS table in the JOB_DATA tablespace, with extent sizes of 512K, 256K, 256K...

Find the space used by the table, and allocate the extent sizes appropriately. You can query the DBA_SEGMENTS view to get the total size of the table (allocated size). If you have the table analyzed, query the (BLOCKS - EMPTY_BLOCKS) from DBA_TABLES to find out exactly how many blocks are used. Let's move the table to a new tablespace, with storage parameters specified.

Remember, prior to Oracle8i, to accomplish what we did in the above command would be the following steps :-(

1. Prevent users from modifying the JOB_STATUS table (make the database in restricted mode or make the JOB_DATA tablespace read only)
2. Export the JOB_STATUS table.
3. Remove any foreign key references to the JOB_STATUS table's primary/unique key (save the code before removing)
4. Drop the JOB_STATUS table
5. Create the JOB_STATUS table with the new parameters
6. Import table (this gives the grants on the table, creates indexes, constraints and triggers)
7. Recreate the foreign key references using the script we saved in step 3.
8. AND there is NO WAY to specify PARALLEL or NOLOGGING (UNRECOVERABLE in the Pre-8i releases) in the import.