Re: Re-sizing the SYSTEM tablespace

I have a 8.0.5.2.2 EE HPUX server where the SYSTEM table space extended badly ;-(
I was advised that you could not resize the SYSTEM data file but must remake the whole DB.
Specifically I have a SYSTEM table space on two data files (says TOAD)

• 55 Mb (original oracle installation size) 25 Mb free
• 1258 Mb (we had the "default table space" wrong for a user and got this monster) (1000Mb free)

What I would consider trying.

1. Try this idea below on a test system (Linux).
2. Perform a cold backup on the HPUX box.
3. Run the command "alter database data file '/p02/oradata/XX01/system02.dbf' resize 512 Mb
4. How long would it take for this alter? 1 GB of ram. SGA of 64 Mb.
5. What mode does the database have to be in to do this?
6. Alternately, could I grow the 55 Mb upto 500 Mb and then coalesce SYSTEM and drop the 'system02.dbf'? That would be very good too.

Sybrand Bakker wrote:

Alter database datafile <name of the system tablespace datafile> resize 200M
(or something, but 200 should definitely be adequate). You need to make of
course there's no data beyond the 200M. This means, as it it the SYSTEM
tablespace, you potentially can be forced into rebuilding the complete
database. If this is 8i there seems to be an option to move extents. This
option definitely doesn't exist in 7.3 and before.

Hth,

--
Sybrand Bakker, Oracle DBA
Lynux <lynux@netvigator.com> wrote in message
news:87cksn$iq016@imsp212.netvigator.com...
> We come to a problem that we have created our user tables into the
> system tablespace.  And now the tablespace is extended to nearly 2G
> of size.
> Our question is how can we shrink the SYSTEM tablespace into its normal
> size after all the user tables are moved to another tablesapce?
> Would someone give your suggestion on this?
> Thanks in Advance!

--
STeve
 

Received on Fri Feb 04 2000 - 11:50:03 CST