FAQ
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"
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).

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 8 of 14 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 15, '02 at 6:06p
activeAug 16, '02 at 12:33a
posts14
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase