摘要:Oracle Catalog View
當使用者反映查詢資料庫很慢,往往DBA需要找出兇手是誰??
這時就需要System View來幫助我們
搭配Catalog View編寫管理相關PL/SQL語法
快速找到拖慢DB的兇手
附檔是ORACLE10g全部的system view,也都分類好了,給需要的朋友
--查看tablespace usage:
select b.file_id,b.tablespace_name,b.bytes,sum(nvl(a.bytes,0)) left,sum(nvl(a.bytes,0))/(b.bytes)*100 left_percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;
--查看shared pool usage:
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
(1-sum(reloads)/sum(pins) *100) libcache
from v$librarycache;
--查看pga
select a.sid,b.name,a.value from v$sesstat a,v$statname b
where (b.name like '%uga%' or b.name like '%pga%') and a.statistic# = b.statistic#
order by sid ;