Oracle 臨時表空間SQL語句的實(shí)現(xiàn)
臨時表空間概念
臨時表空間用來管理數(shù)據(jù)庫排序操作以及用于存儲臨時表、中間排序結(jié)果等臨時對象,當(dāng)ORACLE里需要用到SORT的時候，并且當(dāng)PGA中sort_area_size大小不夠時，將會把數(shù)據(jù)放入臨時表空間里進(jìn)行排序。像數(shù)據(jù)庫中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能會用到臨時表空間。當(dāng)操作完成后，系統(tǒng)會自動清理臨時表空間中的臨時對象，自動釋放臨時段。這里的釋放只是標(biāo)記為空閑、可以重用，其實(shí)實(shí)質(zhì)占用的磁盤空間并沒有真正釋放。這也是臨時表空間有時會不斷增大的原因。
臨時表空間存儲大規(guī)模排序操作(小規(guī)模排序操作會直接在RAM里完成，大規(guī)模排序才需要磁盤排序Disk Sort)和散列操作的中間結(jié)果.它跟永久表空間不同的地方在于它由臨時數(shù)據(jù)文件（temporary files）組成的,而不是永久數(shù)據(jù)文件（datafiles）。臨時表空間不會存儲永久類型的對象，所以它不會也不需要備份。另外，對臨時數(shù)據(jù)文件的操作不產(chǎn)生redo日志，不過會生成undo日志。
創(chuàng)建臨時表空間或臨時表空間添加臨時數(shù)據(jù)文件時，即使臨時數(shù)據(jù)文件很大，添加過程也相當(dāng)快。這是因?yàn)镺RACLE的臨時數(shù)據(jù)文件是一類特殊的數(shù)據(jù)文件:稀疏文件（Sparse File）,當(dāng)臨時表空間文件創(chuàng)建時，它只會寫入文件頭部和最后塊信息(only writes to the header and last block of the file)。它的空間是延后分配的.這就是你創(chuàng)建臨時表空間或給臨時表空間添加數(shù)據(jù)文件飛快的原因。
另外，臨時表空間是NOLOGGING模式以及它不保存永久類型對象，因此即使數(shù)據(jù)庫損毀，做Recovery也不需要恢復(fù)Temporary Tablespace。
以下總結(jié)了關(guān)于 Oracle 數(shù)據(jù)庫臨時表空間的相關(guān) SQL 語句：
Oracle 臨時表空間創(chuàng)建和添加數(shù)據(jù)文件：
--創(chuàng)建臨時表空間 tempdata create temporary tablespace tempdata tempfile '/oradata/orcl/tempdata01.dbf' size 30g autoextend off; --新增臨時表空間數(shù)據(jù)文件 alter tablespace tempdata add tempfile '/oradata/orcl/tempdata02.dbf' size 30g autoextend off; --刪除臨時表空間數(shù)據(jù)文件 alter tablespace tempdata drop tempfile '/oradata/orcl/tempdata02.dbf' including datafiles; --調(diào)整臨時表空間數(shù)據(jù)文件大小 alter database tempfile '/oradata/orcl/tempdata01.dbf' resize 2G; --設(shè)置自動擴(kuò)展 alter database tempfile '/oradata/orcl/tempdata01.dbf' autoextend on; --切換默認(rèn)臨時表空間 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';
查看當(dāng)前默認(rèn)臨時表空間：
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(+)
查看臨時表空間對應(yīng)的臨時文件的使用情況：
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;
查詢實(shí)時使用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是不準(zhǔn)確的*/
查詢歷史的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;
到此這篇關(guān)于Oracle 臨時表空間SQL語句的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)Oracle 臨時表空間語句內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信