ORALCE数据库监控脚本

最近在做数据库SEGMENT的信息监控，具体什么意思不是很明白，就是段的意思,总而言之就是监控你的数据库的一些信息，看看表空间增长如何，哪些表的行数，索引数量，就好像一个PL/SQL一样，脚本都来自网上的搜集脚本,我不是DBA不是很懂这些，只是做了些简单的关联信息,供大家看看,有更好的建议可以提出来。我的SQL真的很丑。。。

第一个是数据库表的监控脚本，去oracle跑一下就知道是什么信息了基本上，一个表的基本信息。

select t.owner,

t.table_name,

c.comments,

d.created,

t.tablespace_name,

t.status,

t.row_movement,

t.partitioned,

t.last_analyzed,

t.logging,

a.bytes,

t.num_rows,

(select count(1) from dba_ind_columns e, dba_indexes f where e.index_name = f.index_name and e.table_name = f.table_name and e.table_name = t.table_name and f.TABLE_OWNER =t.owner and e.INDEX_OWNER = t.owner ) indexnums,

(select COUNT(1) from dba_constraints where owner=t.owner AND TABLE_NAME=t.table_name AND constraint_type ='P' ) keynums

from dba_tables t

inner join dba_tab_comments c on t.OWNER = c.owner and t.TABLE_NAME = c.table_name

left join ( select owner,SEGMENT_NAME,bytes from dba_segments where SEGMENT_TYPE='TABLE' ) A on c.owner = a.owner and c.table_name = a.segment_name

left join dba_OBJECTS d on a.owner = d.owner and a.segment_name = d.object_name and d.object_type='TABLE'

第二个是数据库用户的监控脚本：查看用户的表数量,表大小,是否被锁

select t1.*, t2.tablesize,t3.account_status,t3.lock_date

  from (select a.owner,

               count(TABLE_NAME) tablecount,

               sum(NUM_ROWS) tablerows

          from dba_tables a

         where a.owner is not null

         group by a.owner) t1

  left join (select OWNER, sum(bytes) tablesize

               from dba_segments

              where SEGMENT_TYPE = 'TABLE'

              group by OWNER) t2

    on t1.owner = t2.owner

 left join    dba_users t3 on t2.owner= t3.USERNAME

   order by ACCOUNT_STATUS,LOCK_DATE desc