system表空间占用率90%以上

chunchun2012

数据库版本为10.2.0.1，system表空间  Extent Management管理方式为local,segment management管理方式为manual，近期发现system表空间使用率为90.4%，请问system表空间使用率超过90%时，是否会影响数据库性能，是否需要为system表空间增加数据文件？

xifenfei

当前system 表空间大小多大？
默认是自动扩展的
8k默认是32G

wind

select owner, sum(bytes)/1024/1024, tablespace_name from dba_segments group by owner, tablespace_name order by 1, 3


跑跑这个看看，是不是其他用户东西放在system表空间？

Stone

补充一下： 我的理解是找到具体的原因，然后对症下药啦 ~
空间占用的%百分比应该是不会影响到具体性能的，但是如果因为意外扩展的快的话，很有可能影响数据库的正常运行。比如说system表空间满了，就有可能使数据库查询之外的功能受到影响。

Your car won't run fast or slow if your gas tank is completely full or half full! The tablespace being full or not completely full won't decide the performance of the database. Since the tablespace is System tablespace which is supposed to store the data dictionary, it being full may hinder the working of the database since there is no more space left the dictionary. But if you would purely do queries without doing any changes at all, those queries performance won't be determined from the tablespace, whichever it may be, being full or not being full.

That said, this is a rather wrong criteria to check the performance. If you want to check performance, first take a feedback from the users and than get a report using Statspack or AWR( if you have license for it) and see what does the report says. If you find anything wrong in it than only imagine it as an issue and try to fix it.

HTH
Aman....

https://forums.oracle.com/forums/thread.jspa?threadID=1048644

所以：
1. 查看当前的system表空间多大？具体剩余多少？表空间的增长趋势如何，就是大概每天的增长量？有了这些数据心里相对就有数了，到底加不加就可以好决定啦。（这个思路同 xifenfei ：）

2. 然后就是如果真的有异常占用大量的空间，应该分析具体是什么object占用的，由什么引起的，这样就比较方便对症下药啦。（同 wind :）

1. -- chk top 10 segments in a tablespace
   
2. -- 查system,可以具体查询的时候制定，看看top spacex消耗元凶 :)
   
3. set lines 130
   
4. col OWNER for a20
   
5. col SEGMENT_NAME for a31
   
6. col TABLESPACE_NAME for a30
   
7. select * from
   
8. (select owner,SEGMENT_NAME,segment_type,TABLESPACE_NAME,bytes/1024/1024 MB
   
9. from dba_segments
   
10. where TABLESPACE_NAME='&tbs_name'
    
11. order by mb desc)
    
12. where rownum < 11
    
13. /

复制代码