Oracle臨時表空間刪除和重建過程分享
一、臨時表空間概念
臨時表空間用來管理數據庫排序操作以及用于存儲臨時表、中間排序結果等臨時對象,當ORACLE里需要用到SORT的時候，并且當PGA中sort_area_size大小不夠時，將會把數據放入臨時表空間里進行排序。臨時表空間存儲大規模排序操作(小規模排序操作會直接在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臨時表空間過程
STEP1: Find the existing temp tablespace details
SQL> select tablespace_name,file_name from dba_temp_files
TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------------------------
TEMP /home/oracle/app/oracle/oradata/cdb1/orcl/orcl_temp01201
4-07-30_04-39-23-PM.dbf
STEP2: Create another Temporary Tablespace TEMP1
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE ‘/u01/app/oradata/DBACLASS/temp01′ SIZE 2G;
STEP3: Move Default Database temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
STEP4: If any sessions are using temp space, then kill them.
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
STEP5: Drop the original temp tablespace.
Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
If you want to change the name from TEMP1 to TEMP, then follow the same process as below.
STEP6: Create TEMP tablespace
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;
STEP7: Make TEMP as default tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
STEP8: Drop temporary for tablespace temp1
DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;
三、查詢TEMP TABLESPACE利用率
3.1 script 1
column used_MBytes format 999,999
column free_Mbytes format 999,999
column total_MBytes format 999,999
column collect_time format A15
select
to_char(sysdate,'DD-MON-RR:HH24:MI') collect_time
,round(used_blocks*8192/1024/1024,0) used_Mbytes
,round(free_blocks*8192/1024/1024,0) free_Mbytes
,round(total_blocks*8192/1024/1024,0) total_Mbytes
from
V$sort_segment
where
tablespace_name like '%TEMP%'
/
eg:
COLLECT_TIME USED_MBYTES FREE_MBYTES TOTAL_MBYTES
--------------- ----------- ----------- ------------
17-JUL-16:17:23 5 24 29
3.2 script 2
set lines 180
col FreeSpaceGB format 999,999
col UsedSpaceGB format 999,999
col TotalSpaceGB format 999,999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024 FreeSpaceMB,
(used_blocks*8)/1024 UsedSpaceMB,
(total_blocks*8)/1024 TotalSpaceMB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name='&TEMPTBS' and contents='TEMPORARY') and
i.inst_id=ss.inst_id;
eg:
TABLESPACE_NAME FREESPACEMB USEDSPACEMB TOTALSPACEMB INSTANCE_NAME HOST_NAME
------------------------------ ----------- ----------- ------------ ---------------- ----------------------
TEMP 24 5 29 orcl rac1.rajasekhar.com
3.3 script 3
SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024 as TABLESPACE_SIZE_MB, ALLOCATED_SPACE/1024/1024 as ALLOCATED_SPACE_MB, FREE_SPACE/1024/1024 as FREE_SPACE_MB FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE_MB ALLOCATED_SPACE_MB FREE_SPACE_MB
------------------------------ ------------------ ------------------ -------------
TEMP 30 30 29
