小知识:如何判断数据文件的高水位线

客户这边有一个需求:某个业务系统的数据库数据文件占用空间过大,磁盘空间资源紧张且不允许扩容,业务已经尽可能的做了数据清理,目前DBA想对数据文件尽可能的resize到允许值以释放部分空间,我通过互联网搜索到一个SQL,原版存在些排版格式的错误,我这里实际测试验证结合实际需求,最终修正如下,下面SQL可直接执行,只需按提示输入你要查询的表空间:

col tablespace_name for a30
set lines 140
select 
    a.tablespace_name,
    a.file_id,
    ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "smallest(mb) - hwm",
    ceil(blocks * c.value / 1024 / 1024) "currsize(mb)",
    ceil(blocks * c.value / 1024 / 1024) - ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "savings(mb)"
from dba_data_files a, 
    (select file_id, max(block_id + blocks - 1) hwm from dba_extents group by file_id) b,
    (select value from v$parameter  where name = 'db_block_size') c
where a.file_id = b.file_id(+) 
and a.status <> 'INVALID'
and a.tablespace_name = upper('&tablespace_name')
order by 2;

之前也说过很多次,作为一名合格的DBA,不应该盲目相信网络搜索来的信息(包括本文),一定要自己理解清楚后,才可以比较放心的使用。比如先搞清楚上面这条SQL的思路:
ceil()函数,实现向上取整,因为高水位要用于后续resize参考,所以选择向上取整;
nvl(b.hwm, 1)函数,如果b.hwm值为null,则用指定值1代替null值,这样即使存在没有被使用过的表空间,计算节省空间时也会有相对比较准确的值;
a.status<> ‘INVALID’, 官方文档有解释,File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped)
而这个语句最核心的部分是:在dba_extents中找到 max(block_id + blocks – 1),然后按file_id分组,这样就可以定位每个数据文件中有数据的部分高水位线是多少,再结合实际块大小算出具体数值。
比如在我自己的测试环境下执行,查询表空间DBS_D_JINGYU的数据文件情况:

Enter value for tablespace_name: DBS_D_JINGYU
old  12: and a.tablespace_name = upper('&tablespace_name')
new  12: and a.tablespace_name = upper('DBS_D_JINGYU')

TABLESPACE_NAME                   FILE_ID smallest(mb) - hwm currsize(mb) savings(mb)
------------------------------ ---------- ------------------ ------------ -----------
DBS_D_JINGYU                            6                 21           50          29
DBS_D_JINGYU                            8                  1          100          99

验证下这个值是否准确,比如拿6号数据文件举例:

sys@CRMDB> alter database datafile 6 resize 21M;

Database altered.

sys@CRMDB> alter database datafile 6 resize 20M;
alter database datafile 6 resize 20M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

可以看到还是非常靠谱的,我们可以根据这个来做参考,使用resize进行某些情况下的空间回收。

8号数据文件就是之前说的比较特殊的例子,实际是没有被使用的,用1代替了空值,那么是否可以将还没使用过的数据文件缩小到1M呢?我们来试下:

sys@CRMDB> alter database datafile 8 resize 1M;
alter database datafile 8 resize 1M
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required


sys@CRMDB> alter database datafile 8 resize 2M;

Database altered.

实际测试发现1M是不可以的,原因是1M比最小值还要小,而resize到2M就可以成功。如果你好奇到底最小是多大,可以继续用二分法去验证,但这个实际的意义并不大。

最后把dba_data_files和dba_extents两个视图的字段贴出来参考:

SQL> desc dba_data_files;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 FILE_NAME                                                                  VARCHAR2(513)
 FILE_ID                                                                    NUMBER
 TABLESPACE_NAME                                                            VARCHAR2(30)
 BYTES                                                                      NUMBER
 BLOCKS                                                                     NUMBER
 STATUS                                                                     VARCHAR2(9)
 RELATIVE_FNO                                                               NUMBER
 AUTOEXTENSIBLE                                                             VARCHAR2(3)
 MAXBYTES                                                                   NUMBER
 MAXBLOCKS                                                                  NUMBER
 INCREMENT_BY                                                               NUMBER
 USER_BYTES                                                                 NUMBER
 USER_BLOCKS                                                                NUMBER
 ONLINE_STATUS                                                              VARCHAR2(7)

SQL> desc dba_extents
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 OWNER                                                                      VARCHAR2(30)
 SEGMENT_NAME                                                               VARCHAR2(81)
 PARTITION_NAME                                                             VARCHAR2(30)
 SEGMENT_TYPE                                                               VARCHAR2(18)
 TABLESPACE_NAME                                                            VARCHAR2(30)
 EXTENT_ID                                                                  NUMBER
 FILE_ID                                                                    NUMBER
 BLOCK_ID                                                                   NUMBER
 BYTES                                                                      NUMBER
 BLOCKS                                                                     NUMBER
 RELATIVE_FNO                                                               NUMBER

如果对这类基本视图的某些字段还不熟悉,可以去多看看官方文档,说不定哪天就可以帮助到你实现某个需求。
小结:看起来很简单的一个需求,其实也考验到对Oracle体系原理的理解,如果之前只知道人云亦云的去复述Oracle的段、区、块什么的,一遇到这类简单需求却联想不出可能的解决方案,就需要反思下自己是否学习方法上存在问题了。

This entry was posted in Oracle体系原理 and tagged , , , , , . Bookmark the permalink.