From: roy@mda.ca (Tim Roy) Subject: Re: Help: SYSTEM Table Space Not Being Released Date: 1996/04/04 Message-ID: <4k1gji$bj7@mowgli.mda.ca>#1/1 references: <4jsd3f$12a@mowgli.mda.ca> <31641BD2.330B@hq.icb.chel.su> content-type: text/plain; charset=us-ascii organization: MacDonald Dettwiler, 13800 Commerce Parkway, Richmond, BC, Canada V6V 2J3 mime-version: 1.0 newsgroups: comp.databases.oracle In article <31641BD2.330B@hq.icb.chel.su>, LEV wrote: >Tim Roy wrote: >-> >-> Hi, >-> >-> I'm having the problem that Oracle is continually running out of >-> SYSTEM table space. It occurs with all of the several independent >-> Oracle installations that we have at our site. It looks like >-> Oracle is not releasing any SYSTEM table space when we delete users. >-> >-> For example: >-> >-> Amount of free SYSTEM table space at start: 500K >-> >-> Perform the following actions: >-> - create user account >-> - create tables and populate with data in user account >-> - drop user account cascade >-> >-> Amount of free SYSTEM table space at end: 400K >-> >-> As a result we lose 100K of SYSTEM table space every time we run >-> through this cycle. It looks like the SYSTEM table space is >-> never being freed because there is never any fragmentation >-> of the SYSTEM table space. We have checked for objects in the >-> system that do not belong to any valid users and not found any. >-> We have phoned Oracle and they do not have any answers. >-> We're running Oracle 7 server release 7.0.16.4.0 on >-> HP/UX machines. >-> >-> Any suggestions, comments or help greatly appreciated. >-> >-> -- >-> Tim Roy - roy@mda.ca MacDonald Dettwiler and Associates >-> 13800 Commerce Parkway >-> Phone: (604)231-2429 Richmond, B.C., Canada >-> Fax: (604)278-2533 V6V 2J3- > >Try to create tablespaces USERS,TEMP and create users with > default tablespace USERS and default temporary tablespace TEMP Yep, this is what we do. > >By the way: I have troubles in my database when the free space > is near 100Mb and if it's near 10Mb - it's near a catastrophe. > If you operate in terms 100Kb, well, hm... I choose a poor set of numbers for the example. We usually start with 100Mb of system table space and our data set is quite small. Thanks for the advice. -- Tim Roy - roy@mda.ca MacDonald Dettwiler and Associates 13800 Commerce Parkway Phone: (604)231-2429 Richmond, B.C., Canada Fax: (604)278-2533 V6V 2J3