废话不多说了,具体代码如下所示:

  --SYSTEM表空间不足的报警   登录之后,查询,发现是sys.aud$占的地方太多。   > select owner, segment_name, segment_type, sum(bytes)/1024/1024 space_m      from dba_segments      where tablespace_name = 'SYSTEM'    group by owner, segment_name, segment_type   having sum(bytes)/1024/1024 >= 20   order by space_m desc   ;    4  5  6  7    OWNER  SEGMENT_NAME   SEGMENT_TYPE SPACE_M   -------- ------------------------------- -------   SYS   AUD$       TABLE      4480   SYS   IDL_UB1$     TABLE       272   SYS   SOURCE$      TABLE       72   SYS   IDL_UB2$     TABLE       32   SYS   C_OBJ#_INTCOL#  CLUSTER      27   SYS   C_TOID_VERSION#  CLUSTER      24   6 rows selected.   SQL>   查看是哪个记得比较多。   col userhost format a30   select userid, userhost, count(1) from sys.aud$    where ntimestamp# >=CAST(to_date('2014-03-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    group by userid, userhost   having count(1) > 500   order by count(1) desc   ;   再继续找哪天比较多。   select to_char(ntimestamp#, 'YYYY-MM-DD') audit_date, count(1)    from sys.aud$    where ntimestamp# >=CAST(to_date('2014-03-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    and userid = 'xxxx' and userhost = 'xxxx'   group by to_char(ntimestamp#, 'YYYY-MM-DD')    order by count(1) desc   ;   select spare1, count(1) from sys.aud$    where ntimestamp# between CAST(to_date('2014-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    and CAST(to_date('2014-03-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)   and userid = 'xxxx' and userhost = 'xxxx'   group by spare1   ;   select action#, count(1) from sys.aud$    where ntimestamp# between CAST(to_date('2014-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    and CAST(to_date('2014-03-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)   and userid = 'xxxx' and userhost = 'xxxx'   and spare1 = 'xxxx'   group by action#   order by count(1) desc   ;   结果如下:     ACTION#  COUNT(1)   ---------- ----------       101   124043       100   124043   SQL>   其实是上次打开的audit一直没有关闭。   关闭:   SQL> noaudit session;   清空:   truncate table sys.aud$;   ------------------------------------------------------------------------   实战   ------------------------------------------------------------------------   --1,查询表空间占用情况   select dbf.tablespace_name as tablespace_name,        dbf.totalspace as totalspace,        dbf.totalblocks as totalblocks,        dfs.freespace freespace,        dfs.freeblocks freeblocks,        (dfs.freespace / dbf.totalspace) * 100 as freeRate         from (select t.tablespace_name,        sum(t.bytes) / 1024 / 1024 totalspace,        sum(t.blocks) totalblocks        from DBA_DATA_FILES t        group by t.tablespace_name) dbf,        (select tt.tablespace_name,        sum(tt.bytes) / 1024 / 1024 freespace,        sum(tt.blocks) freeblocks        from DBA_FREE_SPACE tt        group by tt.tablespace_name) dfs        where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)   --2,查看哪里占的比较多 SYSTEM 为step1中查询 tablespace_name 内容   select owner, segment_name, segment_type, sum(bytes)/1024/1024 space_m      from dba_segments      where tablespace_name = 'SYSTEM'    group by owner, segment_name, segment_type   having sum(bytes)/1024/1024 >= 20   order by space_m desc   --3,查看是哪个记得比较多 count(1) 越大,说明占得比较多   select userid, userhost, count(1) from sys.aud$    where ntimestamp# >=CAST(to_date('2014-03-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    group by userid, userhost   having count(1) > 500   order by count(1) desc   --4,再继续找哪天比较多 userid userhost 为上一步查询内容   select to_char(ntimestamp#, 'YYYY-MM-DD') audit_date, count(1)    from sys.aud$    where ntimestamp# >=CAST(to_date('2015-03-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    and userid = 'userid' and userhost = 'userhost'   group by to_char(ntimestamp#, 'YYYY-MM-DD')    order by count(1) desc   ;   select spare1, count(1) from sys.aud$    where ntimestamp# between CAST(to_date('2016-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    and CAST(to_date('2016-12-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)   and userid = 'userid' and userhost = 'userhost'   group by spare1   ;   --spare1 为上一步查询内容   select action#, count(1) from sys.aud$    where ntimestamp# between CAST(to_date('2016-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)    and CAST(to_date('2016-12-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)   and userid = 'userid' and userhost = 'userhost'   and spare1 = 'Administrator'   group by action#   order by count(1) desc   --5,关闭seeion   noaudit session;   --6,清空:   truncate table sys.aud$; 

总结

以上所述是小编给大家介绍的System表空间不足的报警,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对沃谷博客网站的支持!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注