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


	(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