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
indexes=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.
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,
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
SYSTEM tablespace because somebody has
been using it. Good luck!
|
|
|