Link to home
Start Free TrialLog in
Avatar of hussainkhan22
hussainkhan22Flag for United States of America

asked on

Free space tablespace

How do we find how much is the free space available in oracle tablespace.
System tablespace has 23MB free. It grew by 34 MB in the past month and 26 MB in the previous month. Please advice as to if this tablespace should be increased or if this is a purge job that is not running.
ASKER CERTIFIED SOLUTION
Avatar of point_pleasant
point_pleasant
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hussainkhan22

ASKER

Can you please provide "Prompt be sure that you have run free_space.sql prior to this script". What exactly we must look in the above output for system tablespace.
System tablespace has 23MB free. It grew by 34 MB in the past month and 26 MB in the previous month. Please advice as to if this tablespace should be increased or if this is a purge job that is not running. What do you suggest me to inform client and do.
I really appreciate your help.

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark Geerlings
I'm not clear on the exact problem you are trying to solve.  Is it shrinking free space in the SYSTEM tablespace?  Or, are you concerned about free space in other tablespaces also?  When you say: "It grew by 34 MB in the past month and 26 MB in the previous month", what exactly is "it"?  Is that free space, or used space?

The SYSTEM tablespace should *NOT* contain any custom objects.  This tablespace should have *ONLY* those objects that were part of this tablespace when the database was created, which is *BEFORE* any custom or application objects get created.  All application or custom objects should be in other tablespaces.  But, depending on who created objects in this database, it is possible that you have custom objects in this tablespace.  If you do, they should be moved to other tablespaces.

What does this query show you about objects in the SYSTEM tablespace?
select owner, segment_type, count(*) Qty
from dba_segments where tablespace_name = 'SYSTEM'
group by owner, segment_type
order by owner, segment_type;

Also, please tell us which Oracle version you have.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial