Moving Segments out of the
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
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
There is a trick to importing data segments under a different user
name. The first step is to use the Oracle
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
command for multiple tables owned by a single user is shown here.
The utility that reads such files is called
exp scott/tiger file=exp.dmp log=exp.log tables=(emp,dept) rows=yes
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.
If you execute the file
imp other/user file=exp.dmp tables=(emp,dept) log=imp.log
indexfile=ddl.sql rows=no indexes=yes
ddl.sql as a SQL*Plus script, it
will create the tables that you need. You can then import the data,
ignore= option to prevent the utility from
failing on table ownership errors.
Now comes the truly amusing part: explaining to the DBA that he or she
has to rebuild the
imp other/user file=exp.dmp tables=(emp,dept) log=imp.log ignore=yes
SYSTEM tablespace because somebody has
been using it. Good luck!