temp不足了怎么办-凯发app官方网站

--检查temp大小
set lin 200 pages 100
col tablespace_name for a20
col file_name for a40
select tablespace_name,file_name,round(bytes/1024/1024)m from dba_temp_files order by 1,2;
col tablespace_name for a20
select tablespace_name,file_id,round(bytes_used/1024/1024,2) bytes_used_mb,
round(blocks_used*8/1024,2) blocks_used_m,bytes_free/1024/1024 free_mb from v$temp_space_header;
--检查当前使用情况
col username for a15
col tablespace for a18
col event for a30
select s.sid,s.username,s.state,s.event,s.sql_id,u.tablespace,u.contents,u.segtype,round(((u.blocks*p.value)/1024/1024),2) mb
from v$session s ,v$sort_usage u,v$parameter p
where s.saddr=u.session_addr
and upper(p.name)='db_block_size'
order by mb desc;
--检查历史使用情况
select *
from (select instance_number, sql_id, max(temp_sum_mb) temp_max
from (select instance_number, sample_time, sql_id, round(sum(nvl(temp_space_allocated, 0))/1024/1024) temp_sum_mb
from dba_hist_active_sess_history
where sample_time between sysdate-1/24 and sysdate
group by instance_number, sample_time, sql_id)
group by instance_number, sql_id
order by temp_max desc)
where rownum <= 10;

--看看sql 内容
col sql_text for a80
set lin 80 pages 1000
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;

--看看sql计划
set long 1000000
set longchunksize 1000000
set lin 300 pages 1000
set trim on
set trimspool on
set echo off
set feedback off
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'text',report_level=>'all') mon_rpt from dual;

参考：

http://blog.chinaunix.net/uid-20687159-id-5853939.html

阅读(163) | 评论(0) | 转发(0) |

上一篇：oracle 19c 侦听日志简单分析

下一篇：修改pdb参数

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们