?
Solved

System Tablespace is growing

Posted on 2006-10-19
9
Medium Priority
?
1,332 Views
Last Modified: 2008-01-09
Hello:
I want to know why my system tablespace is growing slowly but steady.

thanks

~Keyur
0
Comment
Question by:Keyurkumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1400 total points
ID: 17767481
select segment_name,segment_type
from dba_segments
where tablespace_name = 'SYSTEM';

This should give u a list of objects present in the system tablespace. Then you can find out who is creating them etc..

My guess is that may be system tablespace is set as default tablespace for some users and without knowledge they might be creating some tables etc and they might be in this tablespace.

select username, default_tablespace , temporary_tablespace
from dba_users
where default_tablespace = 'SYSTEM' or temporary_tablespace = 'SYSTEM';

This query will help u to identify all such users.

Thanks
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 17767720
Which Oracle users/schemas own the tables and other objects used by your application?  I hope it is a user other than SYSTEM!  All of the objects for your users and schemas should be in other tablespaces, are they?

Which version of Oracle do you have?  Prior to Oracle10, the default tablespace was SYSTEM, even though that is a very bad idea.
0
 
LVL 9

Expert Comment

by:konektor
ID: 17767898
as nav_kum_v wrote, probably some user has SYSTEM tablespace as his default or temporary tablespace. when you create new user and do not specify "default tablespace" and/or "temporary tablespace" clausule, user has assigned SYSTEM tablespace as his default/temporary tablespace.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:mohammadzahid
mohammadzahid earned 400 total points
ID: 17769529
-- SQL : Return username that have objects on SYSTEM tablespace
select owner, segment_name, segment_Type
from dba_segments
where owner NOT IN ('SYS','SYSTEM')
and tablespace_name = 'SYSTEM'
order by 1
/

-- SQL : Return username that have DEFAULT tablespace='SYSTEM'
select username, default_Tablespace, temporary_tablespace
from dba_users
where TEMPORARY_TABLESPACE='SYSTEM'
/



0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17771823
Check whether

1) Any users default tablespace is SYSTEM
2) Any users default TEMPORARY tablespace is SYSTEM
3) If you are using MANUAL UNDO, check the tablespaces assigned for rollback segments

0
 
LVL 3

Author Comment

by:Keyurkumar
ID: 17773779
I have following user's object in system tablespace other than SYS and SYSTEM.

I have 3 more user using system as temp tablespaces;

OWNER
==============
MDSYS
ORDSYS
OUTLN

Do we need to move this objects in any other tbs.

Thanks You

~Keyur
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 17773898
No, those users also are part of a "standard" Oracle install, and their object usually reside in the SYSTEM tablespace.

Does your application use a schema other than SYSTEM?   I hope so, but I have seen some applications that actually use the SYSTEM schema for their data!  This is not wise in Oacle.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17773935
select owner, segment_name, segment_Type
from dba_segments
where owner NOT IN ('SYS','SYSTEM')
and tablespace_name = 'SYSTEM'
order by 1;

do you get output for this query ? apart from whatever you have pasted here.

Thanks
0
 
LVL 35

Expert Comment

by:johnsone
ID: 17774857
I do not see it mentioned here, so I will add this:

All code for triggers, procedures, funcitons and packagare are also stored in the SYSTEM tablespace.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question