RE: Can't Reduce System Tablespace Datafile Size

I'm sure you are referring to locally managed tablespaces which you cannot use for the system tablespace.

• Brian
  • "Trivedi, Hitarth" <HTrivedi_at_telergy.net> wrote:
    > I had similar problem in past ad I too could no get any solution. But
    > Oracle
    > claimed that they had changed the tablespace structure in 8i entirely
    > such
    > that it is less prone to such kind of fragmentation. Anybody has any
    > idea
    > about it?
    >
    > Regards,
    > Hitarth Trivedi
    > -----Original Message-----
    > Sent: Thursday, January 18, 2001 7:51 AM
    > To: Multiple recipients of list ORACLE-L
    >
    >
    > Fyi
    >
    > On my test server, the 8i release 2 database (8.1.6.) also has a 240M
    > system
    > tablespace, that could be the default for the sample database it
    > creates
    > when using the installation GUI tool.
    >
    > If you are really concerned about wasted space you can try to shrink
    > it to
    > progressively smaller file sizes, but Oracle will refuse to do it as
    > soon as
    > you are trying to "reduce" a block that is in use. This doesn't mean
    > there
    > won't be any empty holes inside the tablespace, because fragmentation
    > does
    > occur. I always like to leave empty space inside SYSTEM, if that
    > tablespace
    > fills up you could be in big trouble. (/begin rant Moving the
    > sys.aud$
    > table was for that reason! What is Oracle doing.... /end rant)
    >
    > I know money is tight, but you have to leave at least some leeway in
    > there.
    >
    > My Canadian CDN$0.02. Which is just about 2/3 of 1 cent American.
    > Ah,
    > where did the CDN$1.10 go? <g>
    >
    > Regards,
    > Patrice Boivin
    > Systems Analyst (Oracle Certified DBA)
    >
    > Systems Admin & Operations | Admin. et Exploit. des systèmes
    > Technology Services | Services technologiques
    > Informatics Branch | Direction de l'informatique
    > Maritimes Region, DFO | Région des Maritimes, MPO
    >
    > E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>
    >
    > -----Original Message-----
    > From: Mark Leith [SMTP:mark_at_cool-tools.co.uk]
    > Sent: Thursday, January 18, 2001 6:16 AM
    > To: Multiple recipients of list ORACLE-L
    > Subject: RE: Can't Reduce System Tablespace Datafile Size
    >
    > Sam
    >
    > I'm a little confused.. You say that the SYSTEM tablespace has 50
    > gig used
    > space, yet you are trying to resize it to around a single gig! This
    > kind of
    > makes that error seem pretty reasonable huh? Why not try resizing it
    > to a
    > couple of gig over the 50 that you already have?
    >
    > Also, is a 50 gig SYSTEM tablespace normal? Sorry if this is naive..
    > lol..
    > Here I am sat on my test system, with around a 240M SYSTEM
    > tablespace,
    > thinking it is a little large considering the actual database is
    > around a
    > 1/4 of that!
    >
    > Regards
    >
    > Mark
    >
    > -----Original Message-----
    > Sent: Wednesday, January 17, 2001 04:08
    > To: Multiple recipients of list ORACLE-L
    >
    >
    > 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).
    >
    > --
    > Please see the official ORACLE-L FAQ: http://www.orafaq.com
    > --
    > Author: Mark Leith
    > INET: mark_at_cool-tools.co.uk
    >
    > 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: Boivin, Patrice J
    > INET: BoivinP_at_mar.dfo-mpo.gc.ca
    >
    > 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: Trivedi, Hitarth
    > INET: HTrivedi_at_telergy.net
    >
    > 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).