Oracle库中进行排序,分组汇总,索引等时,会产生很多的临时数据。
通常情况下,oracle会先把这些临时数据存放到内存的PGA内。在PGA内有一个叫排序区的地方,专门用来存放这些因为排序产生的临时数据,但是这个分区的大小不足以容纳排序产生的数据时,oracle就会把临时数据存放到临时表空间中。这就是临时表空间的作用。
注意几点:
一:创建用户时需要为用户创建临时表空间
如果这个用户因为排序等操作需要用到临时表空间,oracle会利用系统表空间system来创建临时段,
Oracle系统表空间:
oracle查看正在占用临时表空间的sql语句:
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;
8、查找消耗资源比较的sql语句
Select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
本文出自 “原歌轩” 博客,转载请与作者联系!