• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1352
  • Last Modified:

System Tablespace is growing

Hello:
I want to know why my system tablespace is growing slowly but steady.

thanks

~Keyur
0
Keyurkumar
Asked:
Keyurkumar
3 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
konektorCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
mohammadzahidCommented:
-- 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
 
MohanKNairCommented:
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
 
KeyurkumarAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now