以下总结了关于 Oracle 数据库临时表空间的相关 SQL 语句：
Oracle 临时表空间创建和添加数据文件：
--创建临时表空间tempdatacreatetemporarytablespacetempdatatempfile'/oradata/orcl/tempdata01.dbf'size30gautoextendoff; --新增临时表空间数据文件altertablespacetempdataaddtempfile'/oradata/orcl/tempdata02.dbf'size30gautoextendoff; --删除临时表空间数据文件altertablespacetempdatadroptempfile'/oradata/orcl/tempdata02.dbf'includingdatafiles; --调整临时表空间数据文件大小alterdatabasetempfile'/oradata/orcl/tempdata01.dbf'resize2G; --设置自动扩展alterdatabasetempfile'/oradata/orcl/tempdata01.dbf'autoextendon; --切换默认临时表空间alterdatabasedefaulttemporarytablespacetempdata; --删除临时表空间droptablespacetempincludingcontentsanddatafilescascadeconstraints; --收缩临时表空间altertablespacetempshrinkspacekeep8G; altertablespacetempshrinktempfile'/oradata/orcl/tempdata01.dbf';
查看当前默认临时表空间：
SELECTPROPERTY_NAME, PROPERTY_VALUEFROMDATABASE_PROPERTIESWHEREPROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
查询temp表空间使用率：
selectdf.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 (SELECTtablespace_name, round(SUM(bytes) /1048576) TotalSpaceFROMdba_TEMP_filesGROUPBYtablespace_name) df, (SELECTtablespace_name, ROUND(SUM(bytes_used) /1024/1024) UsedSpaceFROMgV$temp_extent_poolGROUPBYtablespace_name) fsWHEREdf.tablespace_name=fs.tablespace_name(+)
查看临时表空间对应的临时文件的使用情况：
SELECTTABLESPACE_NAMEASTABLESPACE_NAME , BYTES_USED/1024/1024/1024ASTABLESAPCE_USED , BYTES_FREE/1024/1024/1024ASTABLESAPCE_FREEFROMV$TEMP_SPACE_HEADERORDERBY1DESC;
查询实时使用temp表空间的sql_id和sid：
setlinesize260pagesize1000colmachinefora40colprogramfora40SELECTse.username, sid, serial#, se.sql_idmachine, program, tablespace, segtype, (su.BLOCKS*8/1024/1024) GBFROMv$sessionse, v$sort_usagesuWHEREse.saddr=su.session_addrorderbysu.BLOCKSdesc; /*需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的*/
查询历史的temp表空间的使用的SQL_ID：
selecta.SQL_ID, a.SAMPLE_TIME, a.program, sum(trunc(a.TEMP_SPACE_ALLOCATED/1024/1024)) MBfromv$active_session_historyawhereTEMP_SPACE_ALLOCATEDisnotnullandsample_timebetweento_date('&date1', 'yyyy-mm-dd hh24:mi:ss') andto_date('&date2', 'yyyy-mm-dd hh24:mi:ss') groupbya.sql_id,a.SAMPLE_TIME,a.PROGRAMorderby2asc,4desc;
本次分享到此结束啦~
