Re: Defrag system tablespace

Dan Campbell <dc_at_onthenet.com.au> wrote:
>I'm new to the DBA role and I was wondering if there is any way of
>defragmenting the system tablespace? The only thing I can think of is to
>create a new database with more appropriate parameters then import the
>old database.
>
>Thanks in advance.
>- Dan. dc_at_onthenet.com.au
>

You got it. The base data dictionary tables (owned by sys) are OFF LIMITS. If your problem is with non-sys objects (e.g. system owned) you can use export/import to move them to another tablespace. It is advisable to restrict use of the system tablespace to only those objects owned by sys.

Note that one possible exception to the above are the tables associated with stored PL/SQL code. Many DBAs move these and re-compile the packages, etc.

Ken Friday

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Q: How many Zen masters does it take to screw in a light bulb?
 
A: None.
   The Universe spines the bulb, and
   the Zen master stays out of the way.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~