RE: Can't Reduce System Tablespace Datafile Size

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Thu, 18 Jan 2001 09:58:21 -0800 (PST)
Message-Id: <10745.127031@fatcity.com>

Unless this changed in 8.1.7 and I didn't hear about it I stand by me earlier statement that you CANNOT have a locally managed system tablespace. The docs for 8.1.5 and 8.1.6 are WRONG!

• Brian

Doc ID: Note:112951.1
Subject: Creating a Locally Managed System Tablespace Type: BULLETIN
Status: PUBLISHED
 Content Type: TEXT/PLAIN
Creation Date: 19-JUN-2000
Last Revision Date: 25-AUG-2000
Language: USAENG  

PURPOSE

---

To address locally managed system tablespaces.  

SCOPE & APPLICATION

---

Database Administrators, and Oracle Support employees.

CREATING A LOCALLY MANAGED SYSTEM TABLESPACE

---

 

The Oracle 8i Concepts guide page. 3-8, states that you can specify that the system tablespace be created as locally managed by including the extent management local clause in your create database script......It states as follows:

"For the SYSTEM tablespace, you can specify EXTENT MANAGEMENT LOCAL in the CREATE DATABASE command"

The truth however, is that neither Oracle version 8.1.5 OR 8.1.6 supports the concept of a locally managed system tablespace.

In Oracle 8.1.5, any attempt to add the EXTENT MANAGEMENT LOCAL clause to your create database command will result in a syntax error OR an ORA-600 [3810] error. In Oracle 8.1.6, any attempt to do so, results in no error....But the clause is ignored.

Locally managed system tablespaces are simply not yet possible.

RELATED DOCUMENTS

---

[BUG:809225]

• "Trivedi, Hitarth" <HTrivedi_at_telergy.net> wrote:
  > You are right, now I remember that feature. But looking into manuals
  > (admin
  > guide and concepts)
  > reveal that,
  >
  > ----------------------
  > The LOCAL option of the EXTENT MANAGEMENT clause specifies this
  > method of
  > space management in various CREATE commands:
  >
  > For the SYSTEM tablespace, you can specify EXTENT MANGEMENT LOCAL in
  > the
  > CREATE DATABASE command. If the SYSTEM tablespace is locally managed,
  > other
  > tablespaces in the database can be dictionary-managed but you must
  > create
  > all rollback segments in locally-managed tablespaces.
  >
  > For a permanent tablespace other than SYSTEM, you can specify EXTENT
  > MANGEMENT LOCAL in the CREATE TABLESPACE command.
  >
  > For a temporary tablespace, you can specify EXTENT MANGEMENT LOCAL in
  > the
  > CREATE TEMPORARY TABLESPACE command. (See "Temporary Tablespaces".)
  > --------------------------
  >
  > so locally managed system tablespace is possible!!
  >
  > - Hitarth
  > -----Original Message-----
  > Sent: Thursday, January 18, 2001 9:12 AM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > 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).
  >
  >
  > __________________________________________________
  > Do You Yahoo!?
  > Get email at your own domain with Yahoo! Mail.
  > http://personal.mail.yahoo.com/
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.com
  > --
  > Author: Brian Wisniewski
  > INET: brian_wisniewski_at_yahoo.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: 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
  

Received on Thu Jan 18 2001 - 11:58:21 CST