Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't Reduce System Tablespace Datafile Size

RE: Can't Reduce System Tablespace Datafile Size

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Thu, 18 Jan 2001 09:58:26 -0400
Message-Id: <10745.126984@fatcity.com>


PCTINCREASE is still set at 50%, for some reason.

They haven't changed that in the default installation.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

	-----Original Message-----
	From:	Trivedi, Hitarth [SMTP:HTrivedi_at_telergy.net]
	Sent:	Thursday, January 18, 2001 9:36 AM
	To:	Multiple recipients of list ORACLE-L
	Subject:	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-----
	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.=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>


		-----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

=09

                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).
		--=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

=09

                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).
	--=20
	Please see the official ORACLE-L FAQ: http://www.orafaq.com
	--=20
	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).
	--=20
	Please see the official ORACLE-L FAQ: http://www.orafaq.com
	--=20
	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
Received on Thu Jan 18 2001 - 07:58:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US