@ORACLE调整数据文件大小

调整数据文件大小的SQL

SQL来源于网络，但是没保存下原作者的连接，在原作者的基础上稍微增加了一些中文的说明
ORACLE在使用过程中数据文件会变得越来越大，有时候删除了冗余的数据仍然不会释放空间，此时需要调整数据文件的大小。

select a.file# as "数据文件id",
       a.name as "数据文件路径",
       a.bytes / 1024 / 1024 as "当前数据文件大小(MB)",
       ceil(HWM * a.block_size) / 1024 / 1024 as "可调整至大小(MB)",
       (a.bytes - HWM * a.block_size) / 1024 / 1024 AS "释放空间大小(MB)",
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(ceil(HWM * a.block_size) / 1024 / 1024) || 'M;' as "SQL语句"
  from v$datafile a,
       (SELECT file_id, MAX(block_id + blocks - 1) HWM
          FROM DBA_EXTENTS
         GROUP BY file_id) b
 where a.file# = b.file_id(+)
   And (a.bytes - HWM * a.block_size) > 0
   and rownum < 30
 order by "释放空间大小(MB)" desc

执行后，查询结果中自动显示数据文件的当前大小、可收缩的大小和收缩语句，例如：

在这里插入图片描述

将其中需要调整大小的SQL拷贝出来执行就可以，使用起来非常方便，也非常的安全。

SQL分析

如此好用的SQL，忍不住分析学习了一下。
首先这个SQL查询的是v$datafile表和DBA_EXTENTS这两张表
datafile表中存储的ORACLE数据文件的信息。bytes是数据文件当前的大小，maxBytes是最大大小。上面的SQL通过datafile表计算出了当前数据文件的大小。
那么可收缩的空间是怎么计算的呢？主要是通过DBA_EXTENTS表

DBA_EXTENTS表

首先查看DBA_EXTENTS表的信息

select * from DBA_EXTENTS

在查询结果中，重点关注：

字段	说明
OWNER	表示拥有者
FILEID	数据文件的ID，一个表空间可以有多个数据文件，因此也会有多个FILEID，大文件表空间除外
BLOCK_ID	区所在块的ID
BLOCKS	块的数量

数据块

其中涉及oracle的基本概念：块。一般说起ORACLE的基本结构就是表空间、段、区、块；
块是ORACLE中存储信息的最小单位，ORACLE的块和操作系统的中的块无关，这也是ORACLE跨平台的需要，可以将ORACLE理解为在操作系统的块上又封装了一层。
ORACLE涉及到数据的增删改查，基本都是以数据块为单位进行的，例如，我们要select一行数据，ORACLE会读取到该行上所有的数据块，再返回数据块上指定的数据行，具体的数据行用ROWID来标识。ORACLE的数据块是有大小的，无论是在windows或者linux中，oracel数据库块的默认大小是8k，可以通过SQL来查看：

select value from v$parameter where name='db_block_size'

查询结果显示8192，就是8K大小。注意越大的数据块并不一定会提升系统的性能，反而可能造成空间的浪费。例如有的人设置了16K大小，那意味着即时只存储了一条数据，也会占据16K的空间，会导致大量的碎片，影响SQL的执行效率。

数据块的增长

在上面的SQL中使用了MAX(block_id + blocks - 1)来计算当前数据文件的实际大小，其中block_id是数据块号，当创建一张表时，10G会立刻分配一个extent区，11g在插入一条数据以后才会分配（这也是为什么11G经常不导出空表，顺带一提）。如果表空间没有特别指定，那么初始分配8个数据块。为什么是8个，原因在就是表空间默认的INITIAL_EXTENT是65536，默认一个块是8192字节，那么初始区就会分配 65536 / 8192个字节。如果数据块是16K，那么初始分配就是65536 / (16 * 1024) =4个块。
当插入数据时，ORACLE会判断当前的数据块是否能容纳，如果空间足够那么新增的数据行会写到当前连续的8个数据块中， block_id取决于当前有连续8个数据空间时的起始ID，例如当前数据文件中第9个块以后有8个连续的数据块，那么此时的block_id就是9。当数据量不断增长，当前8个数据块已经不能容纳新增的数据时，ORACLE会寻找下一个连续的8个块，例如有可能数据块33后面找到了8个连续的数据块，那么此时BLOCK_ID就会变成33。

计算实际大小

所以如果要计算当前数据文件的实际大小，只要知道当前数据文件有多少个数据块，然后乘block_size就可以了，那么自然就是找到最大的那个数据块号 ，加上这个区所拥有的块的数量，减1（但是为什么减1没想明白，囧，不减1影响也不大，无非就是表空间有可能调整的有偏差），这就是当前数据文件所拥有的数据块的数量然后乘以block_size，就是当前数据文件实际的大小了。

行链接和行迁移

SQL是整明白了，然后对于数据块的学习和理解，又把以前的几个知识点串起来了。
当新增一行数据时，如果一个数据块（注意是一个，不是8个）已经容纳不下这行数据了，就会把这行数据存储在几个连续的快中，同时数据块的数据区域存储下一个块的地址。这就是行链接；
当修改一行数据时，如果一个数据块已经容纳不下该行数据了，就会把这行数据迁移到其他的数据块中，这叫行迁移。
当一个数据块被频繁的读取、修改，就出现了“热块”
行链接、行迁移、热块都会影响SQL的执行效率，所以就想明白了以前的一些说法：

避免一行数据包含过多的数据。

因为超过了数据块的大小，就会发生行迁移和行链接，从而导致IO性能下降而影响SQL执行效率，此时在AWR报告中可以看到IO消耗明显偏高

高水位线HWM

在ORACLE中创建一张表时，会为这个表分配一个段，这个段里会创建一个初始区，在段中第一个区的第一个块就称为段头(SEGMENT HEADE)，HWM存储在段头中，一个形象的比喻是可以把段表理解成一个杯子，当数据保存时，这个杯子里的水位线就上升，一直到上升到一个阈(yu，四声)值的时候，ORACLE就会认为当前数据块空间不足，然后把数据劈开保存下一个数据块。
当插入数据时，HWM就向“杯子”的顶端移动，当删除数据时，数据块被清空，但是HWM仍然在原地，在原地，在原地~！因此HWM上面都是可用空间，HWM下面都是已经存在的数据块和保留空间。那么就有一个有意思的问题。当我们发送一个SELECT的语句的时候，有可能出现HWM下面什么都没有，但是仍然要进行扫描，因为此时ORACLE并不知道数据块的具体位置，所以要扫描一个段中所有的块，段是什么？段其实就是表，扫描所有的块通俗的讲就是全表扫描。
那么怎么避免？只要让ORACLE知道要查找的数据块在什么位置就好了，这就是索引。
ROWID是ORACLE获取数据块最快的方法，为什么索引快？因为索引中储存的就是ROWID（这么讲其实不够准确，但是方便理解）。
扫描100万个数据块找到其中10个数据块，不如通过索引直接告诉ORACLE这10个数据块的ROWID，这就是索引对效率的提升
所以我们平常工作的时候经常说“我擦，SQL慢是因为全表扫描了要建索引”，但是从ORACLE的原理上其实我们应该说：我擦，SQL慢是因为扫描了段表中所有的数据块导致数据库的IO升高因此要建索引让ORACLE直接获取到对应的ROWID以降低扫描所带来的IO消耗。
这才是专业的说法，不怕被其他人锤死的同学可以尝试一下这种表达方法
想想AWR报告，那些IO消耗高的SQL，是不是大部分原因都是全表扫描和索引不合理？

热块

被频繁读取和修改的数据块，称之为热块，热块也是导致IO性能的原因之一。
为什么会出现热块，是因为数据快中存储了太多的数据，
怎么解决？那就是避免一个数据块中存储过多的数据，
网上有说加大表或者索引的PCTFREE，让一个数据块中存储更少的数据，但是这样会导致数据存储在更多的数据块中，意味着ORACLE读取一行数据时要扫描更多的数据块，所以我觉得这个解决方法需要针对具体的场景。
网上有说设置block_size更小的表，但是这样的话一个生产环境上不同的表有不同的block_size，难道不会增加运维的成本吗？
有说使用Keep Buffer Pool，我觉得这个是比较靠谱的方法，用ORACLE的机制来解决ORACLE的问题。但是Keep Buffer Pool要比“大多数”表的数据行大小更大一些，不然就会导致Keep Buffer Pool中的数据被频繁的清理，在AWR报告中就体现为逻辑读超高，启用的方法执行：alter system set db_keep_cache_size=50m;但是谨慎，我对这个参数并没有太多的研究，在这里写出来仅仅是为了抛砖引玉，给大家提供更多的思路。

结束语

一个调整数据文件大小的SQL，细细分析一下却能学到不少的知识点，就像沙滩上的点点贝壳，以前为了尽快解决问题而不求甚解，但是往往就陷入了知其然但是不知其所以然的窘境。碰到网上没有的问题就变得一筹莫展甚至束手无策。所以还是要更多的了解技术上的实现细节：我们不提倡重复的造轮子，但是总得知道轮子是怎么造的吧，不然真碰到问题了连排查的思路都没有。