在网上搜索了很多次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;

 

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

 

参考: