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