关于表空间碎片整理的疑问

xiaoyu

数据库版本：12.1.0.1.0  
操作系统为:AIX 7.1
生产数据库表空间有许多临时表。应用经常性的有增删改。导致表空间的碎片较多。
参考：OSM Database Space Management (文档 ID 1609156.1）
查询表空间的的结果为：
TABLESPACE_NAME                   SIZE_MB    FREE_MB MAX_SIZE_MB MAX_FREE_MB   FREE_PCT USED_PCT
------------------------------ ---------- ---------- ----------- ----------- ---------- --------------------------------------------
GGTBS                                1024       1023       32767       32766         99  ----------
HGIC_CIF_TS                          1000        999        1000         999         99  ----------
HGIC_CIRC_TS                        10240       9822       10240        9822         95  ----------
HGIC_CORE_TS                        10240       8659       10240        8659         84  XX--------
HGIC_ETL_TS                          1000        998        1000         998         99  ----------
HGIC_ILOG_TS                         1000        999        1000         999         99  ----------
HGIC_MASTERDATA_TS                   2000       1980        2000        1980         99  ----------
HGIC_PAY_TS                          1000        999        1000         999         99  ----------
HGIC_REPORT_TS                       1000        999        1000         999         99  ----------
HGIC_RESERVE_TS                     10240       8316       10240        8316         81  XX--------
HGIC_TRANS_TS                        1000        998        1000         998         99  ----------
OGG                                  3000       1917       32767       31684         96  ----------
SYSAUX                              10240       5922       32767       28449         86  X---------
SYSTEM                               1460        319       32767       31626         96  ----------
TBLSPACE_CLAIM                      30000      28536       32767       31303         95  ----------
TBLSPACE_CLAIMIDX                    1000        605       32767       32372         98  ----------
TBLSPACE_CTUSER                     30000      29996       32767       32763         99  ----------
TBLSPACE_OTH                        10000       6703       32767       29470         89  X---------
TBLSPACE_PAYMENT                    30000      28528       32767       31295         95  ----------
TBLSPACE_PRP                       339247      86460      339247       86460         25  XXXXXXX---
TBLSPACE_PRPIDX                     10240       7072       32767       29599         90  X---------
TBLSPACE_REINS                      30000      29573       32767       32340         98  ----------
TBLSPACE_VISA                       30000      28638       32767       31405         95  ----------
TBLSPACE_VISAIDX                     1000        256       32767       32023         97  ----------
UNDOTBS1                             2403       1880       65535       65012         99  ----------
UNDOTBS2                             2948       2329       65535       64916         99  ----------
USERS                               10240       8397       32767       30924         94  X---------




里面释放合并表空间碎片的语句为：
ALTER TABLESPACE tablespace COALESCE

疑问是：ALTER TABLESPACE tablespace COALESCE;  合并行为是否会锁表是否可以在线进行合并表。




另：参考
http://www.askmaclean.com/archiv ... BA%93%E7%A2%8E.html
文章中提到的查询脚本的查询结果如下：

select a.tablespace_name,
       sqrt(max(a.blocks) / sum(a.blocks)) * (100 / sqrt(sqrt(count(a.blocks)))) FSFI
  from dba_free_space a, dba_tablespaces b
  where a.tablespace_name = b.tablespace_name
and b.contents not in ('TEMPORARY','UNDO')
group by a.tablespace_name
order by FSFI;



TABLESPACE_NAME                      FSFI
------------------------------ ----------
TBLSPACE_PRP                   9.31295725
HGIC_CORE_TS                   10.5187465
HGIC_CIRC_TS                   13.8695951
SYSAUX                         18.5536671
HGIC_RESERVE_TS                18.6053107
TBLSPACE_REINS                 20.5983377
TBLSPACE_PAYMENT               20.9723375
TBLSPACE_CLAIM                 21.5293793
TBLSPACE_CTUSER                21.6262721
TBLSPACE_VISA                  22.1330871
SYSTEM                         44.0016156
USERS                          48.6066654
TBLSPACE_OTH                   51.4518059
TBLSPACE_PRPIDX                52.9670109
TBLSPACE_CLAIMIDX              84.0288988
TBLSPACE_VISAIDX               84.0691193
OGG                            84.0704571
HGIC_REPORT_TS                        100
HGIC_ETL_TS                           100
HGIC_ILOG_TS                          100
HGIC_PAY_TS                           100
HGIC_MASTERDATA_TS                    100
HGIC_CIF_TS                           100
HGIC_TRANS_TS                         100
GGTBS                                 100

botang

这两天事情比较多，至于你提到的coalease所引起的并发性问题比alter table move tablespace小。最好的做法是redefination在线重定义，对并发性几乎没有影响。还有一条命令：

1. alter table ... deallocate unused

复制代码

一个表一个表做，也行。要用到execute immediate来做。
所有这些当中对并发性影响最大的是：

1. alter table ... shrink space;

复制代码

lujiaguai

看到此贴，想起一个EBS上的类似问题
EBS上有个物化视图日志很大，150G以上 BOM.MLOG$_BOM_COMPONENTS_B
但是它实际上已经很久不更新了，查过文档，说可以直接truncate掉，然后重新收集表统计信息，再锁定统计信息，再刷新快照，再重新收集快照统计信息。
而后遇到跟这个帖子类似的问题，这个表空间已经很大了，我trancate掉这150G的表，并不能降低表空间的物理磁盘占用率。
当时建表空的时候，一个数据文件 4GB，建了几十个齐刷刷一排，这样resize几乎不可能有效果吧？？

由此引起2点问题
1，如果表空间300G，数据文件是74个4G的好呢？还是10个30G的好？
     如果考虑restore的因素，似乎是4G的好；但是如果set newname 或者控制文件脚本，似乎是30G的好，工作量小很多。
     那么其他方面呢，比如性能上? 唐老师怎么看待这个问题。
2，如果既成事实就是由nn个数据文件组成一个大的表空间，如果resize无望的情况下，有没有更好的思路来缩小这个表空间的大小？
     想过直接把对象move到新的表空间，但是对象类型太多，索引，表，分区表，分区索引等等，还有一堆的约束，令人绝望~~
     唐老师有没有更好的办法？？

xiaoyu

lujiaguai 发表于 2017-9-7 16:26
看到此贴，想起一个EBS上的类似问题
EBS上有个物化视图日志很大，150G以上 BOM.MLOG$_BOM_COMPONENTS_B
...

我们是用30G一个数据文件啊。。定期做好备份才是王道