SAP Note 12921 - Reorganization of SYSTEM tablespace

Symptom:

Reorganization of tablespace SYSTEM

Cause and prerequisites

Tablespace SYSTEM is extremely fragmented. Because it was extended early, it consists of too many individual data files and is to be reduced (for example, erroneously extended too much). A corrupt block in object in Tablespace SYSTEM exists.

Solution
The only way to reorganize the SYSTEM tablespace completely is to carry out a full database export, delete the database completely (including all physical files apart from init.ora), regenerate the database with CREATE DATABASE ... and carry out a full import. Before doing this, a full database backup must be made (for data security). Prior to CREATE DATABASE ... you can adjust the number, path, name, and size of the data files of the SYSTEM tablespaces and the storage parameters of the objects from SYS to actual requirements by editing the file $ORACLE_HOME/dbs/sql.bsq. After the FULL import, a full backup is once again necessary.

Otherwise (that is, without deleting the database), the following
applies:

The number and size of the physical files of tablespace SYSTEM
cannot be reduced (the number can only be increased).

Objects belonging to user SYS cannot be reorganized (in certain
exceptional cases, it may be necessary to change storage parameters
NEXT, MAXEXTENTS, and PCTINCREASE). Consequently, you should never
generate your own objects under user SYS (in order to be able to
distinguish them from SYS objects). Further, no objects belonging
to other users should be created in tablespace SYSTEM. (If such
objects already exist, they can be exported, deleted and imported
into another tablespace, e.g. also objects by user SYSTEM.)

You can display all occupied extents and free fragments of tablespace
SYSTEM in their physical sequence with SAPDBA (main menu ->
Reorganization -> Check -> Tsp. fragmentation, all extents) or,
by a rather more time-consuming procedure, with sqldba or sqlplus.
It is not possible to rearrange or move the extents belonging to
user SYS or to change them in any other way by means of a
reorganization.

Exception:
It is possible to delete almost 2/3 of all User SYS Indexes (in ver. 7.0.16 those are 38 of 58 Indexes), but they should be recreated. (Undeletable Indexes produce message ORA-00701 if a delete is attempted.) SAPDBA supports an export/structure export of the tables and indexes from User SYS as well, but not a reorganization. If a reorganization of indexes is absolutely necessary, it must be done (at least partly) manually. To do so, first create a backup of Tablespace SYSTEM. Then execute a Structure export with SAPDBA: e:Export -> b:Export Structures -> f:SYSTEM -> g:all -> i:yes ->s:start. Then copy script indSYSTEM.sql to droSYSTEM.sql; with the editor, change every "CREATE INDEX SYS. ..." to "DROP INDEX SYS.; start "sqlplus system/ @droSYSTEM.sql (@ = at sign). The ORA-00701 messages will be imported. Then, as described below, you should execute a defragmentation of the free space. Following this, the deleted indexes will be recreated with script indSYSTEM.sql, analog to the above call of sqlplus.
CAUTION: This procedure will not work for the SYS Tables. Some of them can, in fact, be deleted, but not later recreated: this destroys the databank, which must then be restored and recovered.

Defragmenting free areas in Tablespace SYSTEM:
Finally, adjoining free fragments can be brought together to form a large free fragment in the same physical position. This should happen automatically from ORACLE 7, but does not always take place. To combine adjoining free fragments, add together the values for all free areas that are directly adjacent to one another, sort the totals by ascending order, use then to generate dummy tables in SYSTEM, and finally delete them again. (If the INITIAL value is not smaller than 5 ORACLE blocks, and is not a multiple of 5 ORACLE blocks, you must reduce it to the next smallest multiple of 5 ORACLE blocks):
CREATE TABLE DUMMY001(A CHAR) TABLESPACE SYSTEM STORAGE(MINEXTENTS 1
INITIAL );
...
CREATE TABLE DUMMYnnn(A CHAR) ...;
DROP TABLE DUMMY001;
...
DROP TABLE DUMMYnnn;
(This procedure will not work, however, or else will become somewhat
more complicated if, whilst the dummy tables are being created, a
SYS object, such as TAB$ increases in size (unlikely).
The result of this activity is that you get precisely one free
fragment at every free location.

Reorganization activities other than those described here are not
possible in tablespace SYSTEM.

Key word: SYSTEM reorg.