Solved

System Tablespace is growing

Posted on 2006-10-19
9
1,329 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 350 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 11

Assisted Solution

by:mohammadzahid
mohammadzahid earned 100 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 50 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

690 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