查看系统临时表空间使用情况:
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
1)检查oracle系统临时表空间大小:
select sum(bytes)/1024/1024 "temp size(M)" from dba_temp_files where tablespace_name='TEMP';
2)查询undo表空间大小
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
3)查询SGA和PGA:
SQL> show parameter sga;
获得sga_max_size和sga_target的值
SQL> show parameter pga;
获得pga_aggregate_target的值
参考值:
现场服务器是8G内存,SGA设置为4G,PGA设置为1G
现场服务器是16G内存,SGA设置为10G,PGA设置为2G
现场服务器是32G内存,SGA设置为20G,PGA设置为4G
修改方法:
pfile:
sga_max_size=4G
sga_target=4G
pga_aggregate_target=1G
spfile:
SQL> alter system set sga_max_size=4G scope=spfile;
SQL> alter system set sga_target=4G scope=spfile;
SQL> alter system set pga_aggregate_target=1G scope=spfile;