Oracle临时表空间删除和重建过程

共计 3531 个字符，预计需要花费 9 分钟才能阅读完成。

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象, 当 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。

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;

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

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

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

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配

腾讯云新客低至 82 元 / 年，老客户 99 元 / 年

代金券：在阿里云专用满减优惠券