北京网罗SEO网站策划网站建设网站优化技术交流博客

看到Oracle环境里各个后台进程的实际内存利用率，还可以查看每个到数据库的连接所需要的内存。
col c1 heading 'Program|Name' format a50
col c2 heading 'PGA|Used|Memory' format 999,999,999
col c3 heading 'PGA|Allocated|Memory' format 999,999,999
col c4 heading 'PGA|Maximum|Memory' format 999,999,999
set lines 160
select
program c1,pga_used_mem c2,pga_alloc_mem c3,pga_max_mem c4
from
v$process
order by
c4 desc;

# 显示出数据库中所有表空间的状态
set lines 160
col "表空间名" for a20
SELECT upper(f.tablespace_name) "表空间名",
d.Tot_grootte_Mb "表空间大小(M)",
d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",
to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "使用比",
f.total_bytes "空闲空间(M)",
f.max_bytes "最大块(M)"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM
sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;

检查表空间的使用情况
SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, '99.99')
|| '%' AS pct_free
FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS max_m, count(blocks) AS count_blocks,
sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name;

检查剩余表空间
SELECT tablespace_name, sum ( blocks ) as free_blk ,
trunc ( sum ( bytes ) /(1024*1024) ) as free_m,
max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space GROUP BY tablespace_name;