Re: How to descrease size of System Tablespace?

In article <01bd1f4d$e17c1430$07a0a8c0_at_metalhammer>, "Heinz Waldherr" wrote:

Heinz,

Without creating a new database, you could have a few problems here.

Have you coalesced the tablespaces? This might be the first place to start. (alter tablespace .... coalesce)

Secondly, you might have datafiles which look like this:

 extents   (5M)
 free      (200M)
 extent    (1M)
 free      (800M)
--------------------------

You therefor cannot resize this file smaller than 206M, even though you there is only 6M of real data. If the extent belongs to SYS, then you will have real trouble moving it and will prob. have to recreate the database from scratch. Don't mess with the data dictionary.

For all new DBA's out there, this is a good example of why you should only ever put SYS objects in the SYSTEM tablespace.

regs

Neil Chandler.

>Hi,
>
>I have to reorganize an Oracle 7.3 database.
>
>The problem is thatup to now all tables have been stored in the System
>tablespace.
>Therefore it has grown to the extent of 2.6 GB. Although I have moved all
>the stuff to other tabelspaces now, and there are only the SYS and SYSTEM
>related stuff left,
>I still can't resize the datafile ('file contains xxx blocks of data beyond
>requested RESIZE value'). Which seems a bit odd, as there's only 15 MB of
>data left.
>
>Anybody knows an easy way to get a normal SYSTEM file and tablespace?
>
>Thanks
>
> Heinz
Received on Mon Jan 12 1998 - 00:00:00 CET