資料庫維護經驗

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

select name,used,free, pctused, max_allocated , sysdate opdt from (
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused, round(MAX_ALLOCATED,2) MAX_ALLOCATED from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(SELECT SUM (bytes/1024/1024)  FROM V$SGAINFO WHERE NAME='Maximum SGA Size') MAX_ALLOCATED ,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2) pctused,round(MAX_ALLOCATED,2)   from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(SELECT (value/1024/1024)  FROM V$PGASTAT WHERE NAME = 'maximum PGA allocated') MAX_ALLOCATED,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual));

1
2
3
4
5
6

SQL> show parameter PGA 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 10G
pga_aggregate_target                 big integer 2006M