Oracle 臨時表空間SQL語句的實現

Posted on by

臨時表空間概念

臨時表空間用來管理數據庫排序操作以及用於存儲臨時表、中間排序結果等臨時對象,當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 臨時表空間語句內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：