一、oracle查占用临时表空间较大的SQL语句
示例：
1、创建测试用户并授权
create user lcam_zc_0130 identified by 123456789;
grant dba to lcam_zc_0130;
2、创建表空间
Create tablespace WZ
datafile 'E:\app\Administrator\oradata\orcl\WZ.dbf'
size 32m
autoextend on
next 32m maxsize 5000m
extent management local;
3、导表
E:\app\Administrator\product\11.2.0\dbhome_1\ log=C:\Users\ADMINI~1\AppData\Local\Temp\plsimp.log file=D:\PMS_MESSAGE.dmp userid=lcam_zc_0130/123456789@ORCL buffer=30720 commit=no full=yes grants=yes ignore=yes indexes=yes rows=yes show=no constraints=yes
4、执行SQL
SELECT B.RECEIVERE_NAME,A.RECEIVER_ID, B.PROJECT_ID, B.MODULE_ID from
(
SELECT * FROM PMS_MESSAGE WHERE PROJECT_ID <> '03170101284032'
)sql语句查询不包含
A,
(
SELECT * FROM PMS_MESSAGE WHERE RECEIVER_ID <> '00036530'
)
B WHERE A.RECEIVER_ID = B.RECEIVER_ID order by A.REMIND_TIME asc, B.MODULE_ID desc
5、条件1：造成临时表空间暴涨的SQL还在运行中：
（1）可以看到正在运行的会话使用临时表空间的情况。
select username,session_addr,sql_id,contents,segtype,blocks*8/1024/1024 gb 
from v$sort_usage order by blocks desc; 
（2）查看正在运行的会话临时表空间占用量大的SQL的SID：
select sid from v$session s where saddr='000007FFC772CC60';
（3）查询占用大量临时表空间的sql，即为sql_text中的sql。
select sql_id,s.sql_text,sorts,rows_processed/executions from v$sql s
where parsing_schema_name='LCAM_ZC_0130' and executions>0 and sorts>0 
and sql_id in (select sql_id from v$open_cursor where sid=69) 
order by 3;
6、条件2：造成临时表空间暴涨的SQL已经运行过了：
dba_hist_active_sess_history这个视图Oracle每秒钟会收集一次v$session里面的信息，查看表中event，统计direct path write temp和direct path read temp多的sql_id，这两个等待事件就是对临时表空间的操作产生的。
SELECT sql_id,temp_space_allocated FROM dba_hist_active_sess_history WHERE EVENT='direct path write temp' order by TEMP_SPACE_ALLOCATED DESC;
SELECT sql_id,temp_space_allocated FROM dba_hist_active_sess_history WHERE EVENT='direct path read temp' order by TEMP_SPACE_ALLOCATED DESC;
然后通过sql_idgv$sql(sql还在内存中),dba_hist_sqltext（sql不在内存中）
SELECT * FROM v$sql WHERE SQL_ID='byab3vfarbmjz';
SELECT * FROM dba_hist_sqltext WHERE SQL_ID='byab3vfarbmjz';
从而到sql进行诊断。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。