Moving Segments out of the system Tablespace

David Clement

September 2004

Oracle versions 7-8

Oracle defines a default user, SYS, who owns the system's data dictionary. This is stored in its own tablespace, SYSTEM. This tablespace should never be used by any other user for any purpose, and if it has been, the problem should be resolved immediately.

Two reasons for this are data integrity and tablespace integrity. The tables and views in the data dictionary are critical for the Oracle instance to maintain its referential integrity and security. To give just two examples, the data dictionary records all primary keys and all user access privileges. To allow anyone to write into this space is to risk that this enterprise-critical information will be updated or deleted, whether advertently or inadvertently.

No matter how careful the user may be never to compromise data integrity, it is impossible not to compromise tablespace integrity. The allocation of space in the SYSTEM tablespace is complex and not readily predictable, except possibly to the database engineers who designed the algorithms, so if somebody else is allocating space in that tablespace, the risk of fragmentary allocations is high. Fragmentary allocations are those in which chunks of space are left unused that cannot be allocated, because of their odd sizes. These will reduce performance, and the performance of accesses to the SYSTEM tablespace is critical.

So if anyone has installed an application into the SYSTEM tablespace or granted that tablespace to a user other than SYS, the intrusive segments need to be removed at once. If they cannot simply be dropped, they will have to be exported, dropped, and imported into a different tablespace under a different user name.

There is a trick to importing data segments under a different user name. The first step is to use the Oracle exp utility to export the segments. This will dump all the information into a file, the format of which is independent of the hardware platform and operating system environment. A typical sample exp command for multiple tables owned by a single user is shown here.

exp scott/tiger file=exp.dmp log=exp.log tables=(emp,dept) rows=yes
The utility that reads such files is called imp. On the import side, the trick is to create the tables ahead of time. If you have no DDL script, the imp utility provides a way to create one with the indexfile= option, as follows.
imp other/user file=exp.dmp tables=(emp,dept) log=imp.log
indexfile=ddl.sql rows=no indexes=yes
If you execute the file ddl.sql as a SQL*Plus script, it will create the tables that you need. You can then import the data, using the ignore= option to prevent the utility from failing on table ownership errors.
imp other/user file=exp.dmp tables=(emp,dept) log=imp.log ignore=yes
rows=yes indexes=yes
Now comes the truly amusing part: explaining to the DBA that he or she has to rebuild the SYSTEM tablespace because somebody has been using it. Good luck!