[Oracle][Maintain]Oracle System Views

摘要:Oracle Catalog View

10g_view.rar

當使用者反映查詢資料庫很慢,往往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 ;