Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

System Tablespace is growing

Posted on 2006-10-19
9
Medium Priority
?
1,340 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
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

580 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