system table free space 0 and pct free also 0

[Asif_8]

Ihave hp-ux ii1
application ovo
oracle 9i
when i get qurey dba_free_space igot
system tablespace zero space
it impact to database

[Sanjay_6]

Hi,

Isn't this something controlled by the oracle database sid init parameter file.

Hope this helps.

regds

[Indira Aramandla]

Hi Asif,

If your qurey from dba_free_space shows zero space for System tablespace, increase the SYSTEM tablespace's space immediately. There is no hard & fast rule for how much free space a system tablespace should have, keep monitoring for sometime, you will have an idea on it's growth rate.

Increase the size by adding new datafile or using the resize the current datafile.

Add a datafile to system (example): alter tablespace system add datafile 'path/name_system02.dbf' size ???M; Resize a tablespace (example): alter database datafile 'path/name_system01.dbf' resize ???M;

The system tablespace contains the data dictionary. Whenever you create any database object including views, the structure information goes into the data dictionary. This obviously uses the space in SYSTEM TABLESPACE. And the other reason system tablespace gwos is during migration.

You should not have user data / objects in system tablespace. It is always better to have all your users other than â sysâ point away from the system tablespace. Make sure that the users are not creating objects in system tablespace. Check the default and the temporary tablespace of the users. If you do not specify the tablespace name while creating the users then any objects that the users create will be going to system tablespace.

Rollback segments should always be in a different tablespace other tbale system. Only your system RBS should be there in the System Tablespace.

I hope this helps.


Ind

[T G Manikandan]

Add another datafile to the system tablespace.

alter tablespace system add datafile 'datafile-path' size 200M autoextend on maxsize 400M;


Just do a
select username,default tablespace from dba_users;

Make sure that normal users do not use the tablespace 'SYSTEM'

[Yogeeraj_1]

hi,

attached a script to query free space on a database.

if the free space on system tablespace has reach 100%, which should not be the case, you should do either of the following:
a. move all "user owned" objects to another tablespace
b. add more datafiles to the tablespace as mentioned by previous posters.

with best regards
Yogeeraj

[Yogeeraj_1]

free.sql