临时表空间概念
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当oracle里需要用到sort的时候，并且当pga中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： create index、 analyze、select distinct、order by、group by、 union all、 intersect、minus、sort-merge joins、hash join等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。
临时表空间存储大规模排序操作(小规模排序操作会直接在ram里完成，大规模排序才需要磁盘排序disk sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。
创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为oracle的临时数据文件是一类特殊的数据文件:稀疏文件（sparse file）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。
另外，临时表空间是nologging模式以及它不保存永久类型对象，因此即使数据库损毁，做recovery也不需要恢复temporary tablespace。
以下总结了关于 oracle 数据库临时表空间的相关 sql 语句：
oracle 临时表空间创建和添加数据文件：
--创建临时表空间 tempdata create temporary tablespace tempdata tempfile '/oradata/orcl/tempdata01.dbf' size 30g autoextend off; --新增临时表空间数据文件 alter tablespace tempdata add tempfile '/oradata/orcl/tempdata02.dbf' size 30g autoextend off; --删除临时表空间数据文件 alter tablespace tempdata drop tempfile '/oradata/orcl/tempdata02.dbf' including datafiles; --调整临时表空间数据文件大小 alter database tempfile '/oradata/orcl/tempdata01.dbf' resize 2g; --设置自动扩展 alter database tempfile '/oradata/orcl/tempdata01.dbf' autoextend on; --切换默认临时表空间 alter database default temporary tablespace tempdata; --删除临时表空间 drop tablespace temp including contents and datafiles cascade constraints; --收缩临时表空间 alter tablespace temp shrink space keep 8g; alter tablespace temp shrink tempfile '/oradata/orcl/tempdata01.dbf';
查看当前默认临时表空间：
select property_name, property_value from database_properties where property_name='default_temp_tablespace';
查询temp表空间使用率：
select df.tablespace_name "tablespace",
df.totalspace "total(mb)",
nvl(fs.usedspace, 0) "used(mb)",
(df.totalspace - nvl(fs.usedspace, 0)) "free(mb)",
round(100 * (1-( nvl(fs.usedspace, 0) / df.totalspace)), 2) "pct. free(%)"
from (select tablespace_name, round(sum(bytes) / 1048576) totalspace
from dba_temp_files
group by tablespace_name) df,
(select tablespace_name, round(sum(bytes_used) / 1024 / 1024) usedspace
from gv$temp_extent_pool
group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name(+)
查看临时表空间对应的临时文件的使用情况：
select tablespace_name as tablespace_name ,
bytes_used/1024/1024/1024 as tablesapce_used ,
bytes_free/1024/1024/1024 as tablesapce_free
from v$temp_space_header
order by 1 desc;
查询实时使用temp表空间的sql_id和sid：
set linesize 260 pagesize 1000
col machine for a40
col program for a40
select se.username,
sid,
serial#,
se.sql_id
machine,
program,
tablespace,
segtype,
(su.blocks*8/1024/1024) gb
from v$session se, v$sort_usage su
where se.saddr = su.session_addr
order by su.blocks desc;
/*需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的*/
查询历史的temp表空间的使用的sql_id：
select a.sql_id,
a.sample_time,
a.program,
sum(trunc(a.temp_space_allocated / 1024 / 1024)) mb
from v$active_session_history a
where temp_space_allocated is not null
and sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by a.sql_id,a.sample_time,a.program
order by 2 asc,4 desc;
到此这篇关于oracle 临时表空间sql语句的实现的文章就介绍到这了,更多相关oracle 临时表空间语句内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！