Oracle 临时表空间SQL语句的实现

目录

• 临时表空间概念
• 以下总结了关于 Oracle 数据库临时表空间的相关 SQL 语句：
• Oracle 临时表空间创建和添加数据文件：
• 查看当前默认临时表空间：
• 查询temp表空间使用率：
• 查看临时表空间对应的临时文件的使用情况：
• 查询实时使用temp表空间的sql_id和sid：
• 查询历史的temp表空间的使用的SQL_ID：

临时表空间概念

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当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）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生@R_406_2903@o日志，不过会生成undo日志。

创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only wrITes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。

另外，临时表空间是NOLOGginG模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。

以下总结了关于 Oracle 数据库临时表空间的相关 SQL 语句：

Oracle 临时表空间创建和添加数据文件：

--创建临时表空间 tempdatacreate 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_FREEFROM V$TEMP_SPACE_HEADERORDER BY 1 DESC;
    

查询实时使用temp表空间的sql_id和sid：

set linesize 260 pagesize 1000col machine for a40col program for a40SELECT 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 临时表空间语句内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

• Oracle 12CR2查询转换教程之临时表转换详解
• Oracle 12CR2查询转换教程之cursor-duration临时表详解
• oracle 临时表详解及实例
• 对比Oracle临时表和SQL Server临时表的不同点
• Oracle临时表空间删除和重建实现过程

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！