I'm hope you meant 50M and not 50GB in the system tablespace otherwise
you've got a perfect reason to rebuild your database besides 2GB of
free space sitting in one of the datafiles.
I think rebuilding your database is your only option.
I had a database where the system tablespace needed to be shrunk
because of another dba's actions. I had a lot of free space sitting in
the middle of the system datafile and asked Oracle about trying the
'alter table move' command on the sys objects sitting near the end of
that datafile. Although they didn't have documents to explicitly state
this shouldn't be done the consensus was this was not a wise idea. I
pretty much expected that response but thought it was worth asking.
- Brian
- Sam Bootsma <SamB_at_cpas.com> wrote:
> Hello,
>
> On one of my databases, the SYSTEM tablespace has about 2 GB free
> space, and
> just over 50 GB used space. I am trying to reduce the size of the
> datafile
> using "alter database datafile
> 'F:\oracle\oradata\TelusDB16bit\SYSTEM01.DBF'
> resize 1024M", but receive the error message ORA-03297: file contains
> used
> data beyond requested RESIZE value. I assume this happens becasue
> part of
> the used space is towards the end of the data file.
>
> If this were a non-SYSTEM tablespace, I could reduce the size of the
> datafile by exporting the data, dropping users, resizing the
> datafile, then
> importing the data. However, this won't work for SYSTEM, because SYS
> objects cannot be exported. Only SYS, SYSTEM, and OUTLN own segments
> in the
> SYSTEM tablespace.
>
> Does anybody know how I can reduce the size of the SYSTEM tablespace
> (short
> of recreating the database)? Thanks for any suggestions.
>
> The database is Oracle 8.1.6 on Windows NT 4.0.
>
> Sam Bootsma, OCP
> Technical Support Analyst
> CPAS Systems Inc.
> 416-422-0563 x237
> samb_at_cpas.com
> http://www.cpas.com
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sam Bootsma
> INET: SamB_at_cpas.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).
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
Received on Thu Jan 18 2001 - 06:55:27 CST
