Oracle 临时表空间相关操作

查询用户默认临时表空间

select temporary_tablespace from dba_users where username='用户名';

补充知识

不要创建单个文件超过32GB的表空间

如果你创建的表空间超过32GB，请把这个表空间存储为多个数据文件，每个文件不大于32GB。这样，就可以成功的创建表空间。

查看表空间利用率

SELECT
  D.TABLESPACE_NAME,
  SPACE || 'M'                                            "SUM_SPACE(M)",
  BLOCKS                                                  "SUM_BLOCKS",
  SPACE - NVL(FREE_SPACE, 0) || 'M'                       "USED_SPACE(M)",
  ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
  FREE_SPACE || 'M'                                       "FREE_SPACE(M)"
FROM (SELECT
        TABLESPACE_NAME,
        ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
        SUM(BLOCKS)                          BLOCKS
      FROM DBA_DATA_FILES
      GROUP BY TABLESPACE_NAME) D,
  (SELECT
     TABLESPACE_NAME,
     ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
   FROM DBA_FREE_SPACE
   GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+)
UNION ALL                                           
  
--如果有临时表空间    
SELECT
  D.TABLESPACE_NAME,
  SPACE || 'M'                                      "SUM_SPACE(M)",
  BLOCKS                                            SUM_BLOCKS,
  USED_SPACE || 'M'                                 "USED_SPACE(M)",
  ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
  NVL(FREE_SPACE, 0) || 'M'                         "FREE_SPACE(M)"
FROM (SELECT
        TABLESPACE_NAME,
        ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
        SUM(BLOCKS)                          BLOCKS
      FROM DBA_TEMP_FILES
      GROUP BY TABLESPACE_NAME) D,
  (SELECT
     TABLESPACE_NAME,
     ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
     ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
   FROM V$TEMP_SPACE_HEADER
   GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+); 
ORDER BY 1;

临时表空间释放

alter tablespace temp shrink space;

临时表空间增加数据文件

PS:为表空间增加数据块

alter tablespace temp add tempfile 'D:\app\Administrator\oradata\vlsi\TEMP02.DBF' size 1024 M;

删除临时表空间

-- temp02为表空间的名称
DROP TABLESPACE temp02 INCLUDING CONTENTS AND DATAFILES;

创建临时表空间

CREATE TEMPORARY
  TABLESPACE temp03
  TEMPFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP03.DBF'
  SIZE 1024M
  AUTOEXTEND ON
  MAXSIZE 31200M;

临时表空间相关信息

SELECT
  FILE_NAME 临时表文件路径,
  FILE_ID,
  TABLESPACE_NAME 表空间名称,
  bytes 当前所占有的字节长度,
  blocks,
  status,
  autoextensible 是否自动扩展,
  maxbytes 最大空间字节数,
  maxblocks,
  increment_by,
  user_bytes 已使用空间的字节数,
  user_blocks
FROM dba_temp_files;

参考文献

• 1