查询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/secondsoco/p/11136852.html

你可能感兴趣的文章