| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't Reduce System Tablespace Datafile Size
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-----
From: Boivin, Patrice J [mailto:BoivinP_at_mar.dfo-mpo.gc.ca]
Sent: Thursday, January 18, 2001 7:51 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Can't Reduce System Tablespace Datafile Size
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.=20
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=E8mes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique=20 Maritimes Region, DFO | R=E9gion des Maritimes, MPO
E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>=20
-----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--=20
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20 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).
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 - 07:30:46 CST
 |
 |