Thanks!! I didn't know about this document.
Hitarth
-----Original Message-----
From: Brian Wisniewski [mailto:brian_wisniewski_at_yahoo.com]
Sent: Thursday, January 18, 2001 1:01 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Can't Reduce System Tablespace Datafile Size
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!
Doc ID: Note:112951.1
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
> (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
--------------------------------------------------------------------
Received on Thu Jan 18 2001 - 12:35:03 CST
