Oracle 臨時(shí)表空間SQL語(yǔ)句的實(shí)現(xiàn)
臨時(shí)表空間概念
臨時(shí)表空間用來(lái)管理數(shù)據(jù)庫(kù)排序操作以及用于存儲(chǔ)臨時(shí)表、中間排序結(jié)果等臨時(shí)對(duì)象,當(dāng)ORACLE里需要用到SORT的時(shí)候,并且當(dāng)PGA中sort_area_size大小不夠時(shí),將會(huì)把數(shù)據(jù)放入臨時(shí)表空間里進(jìn)行排序。像數(shù)據(jù)庫(kù)中一些操作: CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能會(huì)用到臨時(shí)表空間。當(dāng)操作完成后,系統(tǒng)會(huì)自動(dòng)清理臨時(shí)表空間中的臨時(shí)對(duì)象,自動(dòng)釋放臨時(shí)段。這里的釋放只是標(biāo)記為空閑、可以重用,其實(shí)實(shí)質(zhì)占用的磁盤空間并沒有真正釋放。這也是臨時(shí)表空間有時(shí)會(huì)不斷增大的原因。
臨時(shí)表空間存儲(chǔ)大規(guī)模排序操作(小規(guī)模排序操作會(huì)直接在RAM里完成,大規(guī)模排序才需要磁盤排序Disk Sort)和散列操作的中間結(jié)果.它跟永久表空間不同的地方在于它由臨時(shí)數(shù)據(jù)文件(temporary files)組成的,而不是永久數(shù)據(jù)文件(datafiles)。臨時(shí)表空間不會(huì)存儲(chǔ)永久類型的對(duì)象,所以它不會(huì)也不需要備份。另外,對(duì)臨時(shí)數(shù)據(jù)文件的操作不產(chǎn)生redo日志,不過(guò)會(huì)生成undo日志。
創(chuàng)建臨時(shí)表空間或臨時(shí)表空間添加臨時(shí)數(shù)據(jù)文件時(shí),即使臨時(shí)數(shù)據(jù)文件很大,添加過(guò)程也相當(dāng)快。這是因?yàn)镺RACLE的臨時(shí)數(shù)據(jù)文件是一類特殊的數(shù)據(jù)文件:稀疏文件(Sparse File),當(dāng)臨時(shí)表空間文件創(chuàng)建時(shí),它只會(huì)寫入文件頭部和最后塊信息(only writes to the header and last block of the file)。它的空間是延后分配的.這就是你創(chuàng)建臨時(shí)表空間或給臨時(shí)表空間添加數(shù)據(jù)文件飛快的原因。
另外,臨時(shí)表空間是NOLOGGING模式以及它不保存永久類型對(duì)象,因此即使數(shù)據(jù)庫(kù)損毀,做Recovery也不需要恢復(fù)Temporary Tablespace。
以下總結(jié)了關(guān)于 Oracle 數(shù)據(jù)庫(kù)臨時(shí)表空間的相關(guān) SQL 語(yǔ)句:
Oracle 臨時(shí)表空間創(chuàng)建和添加數(shù)據(jù)文件:
--創(chuàng)建臨時(shí)表空間 tempdata create temporary tablespace tempdata tempfile '/oradata/orcl/tempdata01.dbf' size 30g autoextend off; --新增臨時(shí)表空間數(shù)據(jù)文件 alter tablespace tempdata add tempfile '/oradata/orcl/tempdata02.dbf' size 30g autoextend off; --刪除臨時(shí)表空間數(shù)據(jù)文件 alter tablespace tempdata drop tempfile '/oradata/orcl/tempdata02.dbf' including datafiles; --調(diào)整臨時(shí)表空間數(shù)據(jù)文件大小 alter database tempfile '/oradata/orcl/tempdata01.dbf' resize 2G; --設(shè)置自動(dòng)擴(kuò)展 alter database tempfile '/oradata/orcl/tempdata01.dbf' autoextend on; --切換默認(rèn)臨時(shí)表空間 alter database default temporary tablespace tempdata; --刪除臨時(shí)表空間 drop tablespace temp including contents and datafiles cascade constraints; --收縮臨時(shí)表空間 alter tablespace temp shrink space keep 8G; alter tablespace temp shrink tempfile '/oradata/orcl/tempdata01.dbf';
查看當(dāng)前默認(rèn)臨時(shí)表空間:
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(+)
查看臨時(shí)表空間對(duì)應(yīng)的臨時(shí)文件的使用情況:
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í)時(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 臨時(shí)表空間SQL語(yǔ)句的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)Oracle 臨時(shí)表空間語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
在Mac OS上安裝Oracle數(shù)據(jù)庫(kù)的基本方法
這篇文章主要介紹了在Mac OS上安裝Oracle數(shù)據(jù)庫(kù)的基本方法,Oracle是甲骨文公司的一款收費(fèi)數(shù)據(jù)庫(kù)軟件,需要的朋友可以參考下2015-12-12oracle實(shí)現(xiàn)將字段按逗號(hào)拼接/按逗號(hào)分為多行實(shí)例代碼
這篇文章主要給大家介紹了關(guān)于oracle實(shí)現(xiàn)將字段按逗號(hào)拼接/按逗號(hào)分為多行的相關(guān)資料,因?yàn)樽罱?xiàng)目表里的某個(gè)字段存儲(chǔ)的值是以逗號(hào)分隔開來(lái)的,所以這里給大家總結(jié)下,需要的朋友可以參考下2023-07-07oracle ora-00054:resource busy and acquire with nowait speci
這篇文章主要介紹了oracle ora-00054:resource busy and acquire with nowait specified解決方法,需要的朋友可以參考下2015-12-12PL/SQL登錄Oracle數(shù)據(jù)庫(kù)報(bào)錯(cuò)ORA-12154:TNS:無(wú)法解析指定的連接標(biāo)識(shí)符已解決(本地未安裝Oracle
這篇文章主要介紹了PL/SQL登錄Oracle數(shù)據(jù)庫(kù)報(bào)錯(cuò)ORA-12154:TNS:無(wú)法解析指定的連接標(biāo)識(shí)符已解決,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-11-11