数据库学习其二 从表空间到临时表空间

在网上搜索了很多次oracle表空间查询语句，现在记录下来

查询oracle表空间大小：

SELECT * FROM (
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)

其中包含了当前的表空间和临时表空间总空间大小和当前使用情况，突然看到其中包含临时表空间语句，产生了一些兴趣，度了一番之后，如下：

临时表空间使用情况

创建临时表空间，比起普通表空间多了temporary关键字

create temporary tablespace x_temp 
tempfile 'F:\app\Admin\oradata\orcl\temp_x.dbf' 
size 10m 
autoextend on 
next 10m maxsize 2048m 
extent management local

查询临时表空间使用情况时，如果临时表空间是dictionary managed temporary tablespace，可以使用下面SQL：

SELECT (S.TOT_USED_BLOCKS/F.TOTAL_BLOCKS)*100 AS "PERCENT USED"
FROM
  (SELECT SUM(USED_BLOCKS) TOT_USED_BLOCKS
  FROM V$SORT_SEGMENT
  WHERE TABLESPACE_NAME='TEMP'
  ) S,
  (SELECT SUM(BLOCKS) TOTAL_BLOCKS
  FROM DBA_DATA_FILES
  WHERE TABLESPACE_NAME='TEMP'
  ) F;

如果临时表空间是Locally Manageed Temporary Tablespace，可以使用下面SQL：

SELECT  T.TABLESPACE_NAME,
            ( U.TOT_USED_BLOCKS / T.TOTAL_BLOCKS ) * 100 AS "PERCENT USED" 
     FROM   (SELECT TABLESPACE_NAME,
                    SUM(USED_BLOCKS) TOT_USED_BLOCKS 
             FROM   V$SORT_SEGMENT 
             WHERE  TABLESPACE_NAME = 'TEMP'       
      GROUP BY TABLESPACE_NAME) U, 
            (SELECT TABLESPACE_NAME,
                    SUM(BLOCKS) TOTAL_BLOCKS 
             FROM   DBA_TEMP_FILES 
             WHERE  TABLESPACE_NAME = 'TEMP'
             GROUP BY TABLESPACE_NAME) T;

也可以使用如下SQL:

SELECT D.tablespace_name,
       SPACE "SUM_SPACE(M)",
       blocks "SUM_BLOCKS",
       used_space "USED_SPACE(M)",
       Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
       SPACE - used_space "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,
               Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
          FROM v$sort_usage
         GROUP BY tablespace) F
 WHERE D.tablespace_name = F.tablespace(+)

在最开始查询临时表空间的临时表部分sql语句是记录了temp文件在某一时刻使用过的最大大小，视图v$temp_space_header显示的是每一个temp文件在某一个时刻使用过的最大大小，从本质上说，它显示的是每一个tempfile的初始化大小，而不是实际分配的块大小，而v$sort_usage中可以看到临时表空间的当前使用情况

临时表空间爆满处理

临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序，进行导入导出expdp/impdp等，当临时表空间满了之后，根据查找的内容，有以下处理方式

1、重启数据库会释放部分临时表空间，不太适用于生产环境

2、增加临时表空间数据文件大小

 

alter tablespace temp add tempfile '/data/prod/proddata/temp013.dbf' size 8G

 

3、重建临时表空间

 

create temporary tablespace TEMPA TEMPFILE
'F:/app/Admin/oradata/orcl/temp02.dbf ' SIZE 8192M REUSE AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED; 
alter database default temporary tablespace tempa;
drop tablespace temp including contents and datafiles;

 

当tempa表空间满了之后亦可以切换回temp表空间

create temporary tablespace TEMP TEMPFILE
'F:/app/Admin/oradata/orcl/temp01.dbf ' SIZE 8192M REUSE AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED; 
alter database default temporary tablespace temp;
drop tablespace tempa including contents and datafiles;

这样实现默认临时表空间切换，临时表空间组这些后面再考虑，一般要求drop的临时表空间最好不存在活动的排序操作，若删除过程中出现卡住的现象，可以用以下查询语句查出进程，验证影响后，可将进程杀掉

 

Select se.username,
       se.sid,
       se.serial#,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid;

 

alter system kill session '66,6666; (假如某一条运行的sql语句的SID为66，serial#为6666)

4、收缩临时表空间

 

 alter tablespace temp shrink space keep 4G

 

KEEP 选项用来指定压缩时表空间或者数据文件shrink的最小值，如果没有执行该命令，那么表空间或数据文件将被压缩到最小值。如下

alter tablespace temp shrink space

TEMP表空间被压缩到最小，Temp 表空间过小对性能是有影响的，所以在shrink时，还是建议使用keep 指定最小值

 

SELECT  *
FROM DBA_TEMP_FREE_SPACE;

 

该语句也可以查看临时表空间使用情况

 

参考:https://www.cnblogs.com/kerrycode/p/5797233.html