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