System Tablespace is growing

Keyurkumar
Keyurkumar used Ask the Experts™
on
Hello:
I want to know why my system tablespace is growing slowly but steady.

thanks

~Keyur
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Production Manager / Application Support Manager
Commented:
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
Mark GeerlingsDatabase Administrator

Commented:
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.

Commented:
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.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

-- 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'
/



Top Expert 2006

Commented:
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

Author

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
Mark GeerlingsDatabase Administrator
Commented:
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.
Naveen KumarProduction Manager / Application Support Manager

Commented:
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
johnsoneSenior Oracle DBA

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial