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.
ALTER TABLE JOB_STATUS MOVE TABLESPACE NEW_JOB_DATA STORAGE (INITIAL 500M NEXT 500M MINEXTENTS 2 MAXEXTENTS 200 PCTINCREASE 0) PCTFREE 0 PCTUSED 70 NOLOGGING PARALLEL 6;
Remember, prior to Oracle8i, to accomplish what we did in the above command would be the following steps :-(