RE: SYSTEM TABLESPACE IS SO HUGE ?

Paul ,
Yours is an excellent idea, but I have never implemented it in SYSTEM tablespace.
I could see some Indexes & Tables owned by SYSTEM and OUTLN users in the SYSTEM tablespace .. can I rebuild these indexes ?? Or can I move all objects ( tables & indexes to SYSTEM & OUTLN default tablespaces )..

I too do not want to touch any SYS objects ... Would like to know the possibilities with no downtime !!!

Thanks again
Madhu

-----Original Message-----
Sent: Thursday, August 15, 2002 4:44 PM
To: Multiple recipients of list ORACLE-L

You've already been given the best and simplest method to defragment the SYSTEM tablespace: export, recreate with locally  managed tablespaces, do a full import. You _could_ take a look at which SYS objects have many extents and edit sql.bsq **caution**caution**caution** before recreating your new database.

Before you do that, figure out if the tablespace is really fragmented or just heavily used. Each version of Oracle has required more SYSTEM tablespace. Oracle Apps could be filling it up. Or you could just have lots and lots of source code.
Take a look at dba_extents and dba_free_space to see if you have lots of unusable space. It's pretty conceivable, since Oracle likes to put a 50% increase on many of its segment definitions.

If the tablespace has lots of unusable free space, you could possibly try reclaiming the space the way I've often had to reclaim space in a live database with no downtime allowed. If Oracle will allow alter index rebuild and alter table move on the objects (I've never tried to move anything belonging to SYS), you could construct a tablespace map showing where each extent begins and ends. Hopefully you'll have an overextended index or table sitting at the bottom of a datafile. With nobody else able to access the database, do an alter index rebuild or alter table move, perhaps with new storage parameters. With luck, you'll now have a more compressed segment and space freed at the end of the file. Resize the data file to reclaim the space. Make sure your boss knows you might have to restore and do a point in time recovery if something goes wrong.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, August 15, 2002 2:18 PM

> Yes Steve, System tablespace was fragmented heavily and that's why its
> taking 10 Gig .
> The database size is 75 gig , and would you suggest me to take Full
database
> EXPORT and import it back after creating a new database with same
structure
> ??
>
> or is there any best or simple way ??
>
> Thanks
> Madhu
>
> -----Original Message-----
> Sent: Thursday, August 15, 2002 1:51 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 10G? Must have a lot of objects not belonging to sys/system in there.
> (Unless someone turned auditing on and forgot about it.) I'd say the
system
> tablespace must be so fragmented that it's best to create another database
> and recreate the users and import their data with default and quota set to
> locally managed tablespace(s).
>
>
> -----Original Message-----
> Sent: Thursday, August 15, 2002 12:07 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello Listers,
> We have a database for which the SYSTEM tablespace size is nearly 10 gig ,
> Now I have a plan to reduce the size of it . And database is running on
> 8.1.7.2.
> What would be the best and faster way to do it. Your ideas will be very
much
> appreciated !!
>
> Thank in advance,
> Madhu
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Reddy, Madhusudana
> INET: Madhusudana.Reddy_at_bestbuy.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Orr, Steve
> INET: sorr_at_rightnow.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Reddy, Madhusudana
> INET: Madhusudana.Reddy_at_bestbuy.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tom Pall
  INET: tom_at_pall.name

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reddy, Madhusudana
  INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).