收缩数据文件回收空间

基础理论

--- Oracle数据库逻辑存储结构

Oracle Database allocates logical space for all data in the database.
Oracle数据库逻辑存储单元有 data blocks, extents, segments, and tablespaces， 而从物理层来说， 数据存储在磁盘的数据文件中，存储数据block是存在于操作系统块中。
原文--https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html#GUID-13CE5EDA-8C66-4CA0-87B5-4069215A368D

---

--- data blocks, extents, segments, and tablespaces关系



segments由多个extent组成，
---- 段从用途上分为三类，即用户段，临时段以及回滚段，其中用户段包括，如表段或表分区段，table索引簇段，LOB或者LOB分区段，索引或者索引段

extents由逻辑上【连续】的多个data block组成，默认至少包含一个数据文件即便是没有写入数据

data blocks由多个操作系统块组成

Oracle Database manages segment space automatically or manually,关系如图
(一个数据文件只能属于一个表空间，而一个表空间可以包含多个不同的段或者数据文件)

---

通过脚本收缩数据文件大小

-- 数据库磁盘空间爆满且无法立即释放磁盘空间时 可以使用此脚本，迅速尽可能释放一些空间 ，当然除此之外，我们在另外一篇中介绍满足某种条件的释放空间的方法
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/