Re: how to release system tablespace space?

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 02 Aug 2006 13:57:37 -0700
Message-ID: <1154552257.964614@bubbleator.drizzle.com>

joel garry wrote:
> DA Morgan wrote:

>> joel garry wrote:
>>> wyys.cn_at_gmail.com wrote:
>>>> cause advance replication doesn't work. a lot of data segment locate
>>>> in sytem tablespace, so system tablespace space is much beeter than
>>>> before, any ways to release system tablespace space? thanks in advance!
>>> You can only shrink a tablespace as far as there isn't any data.  You
>>> can find a script or use OEM tablespace map (on some versions) to see
>>> where the data ends in the tablespace.  There are more advanced things
>>> one can do, but you want a good DBA in there if you are messing with
>>> system.  If your database isn't too big, it might even be worthwhile to
>>> recreate it from scratch, especially if it has been migrated from DMT
>>> to LMT.
>>>
>>> There is some dependence on version and platform.
>>>
>>> jg
>> Look at the dbms_space built-in package. It has two procedures
>> for identifying shrink candidates which may help with shrinking
>> some segments.

>
> True. While we're mentioning things I forgot in my previous reply, we
> should say "get stuff out of the system tablespace that shouldn't be
> there, and check that the default tablespace for all users exists, and
> that the tablespace_name exists and is not system in dba_tables and
> dba_indexes for all non-Oracle-default users!"
>
> jg
> --
> @home.com is bogus.
> Size matters.
> http://www.signonsandiego.com/uniontrib/20060802/news_1b2condo.html

My basic build script these days contains the following:

CREATE USER ...
IDENTIFIED BY ...
DEFAULT TABLESPACE ...
TEMPORARY TABLESPACE ...
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX Just to remind everyone what they are never to do.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Aug 02 2006 - 15:57:37 CDT