正文开始

这篇文章是关于在Oracle数据库23ai版本中如何收缩大文件表空间以回收未使用的空间。以下是文章的主要内容翻译：

设置

我们需要一个表空间来运行一些测试。在Oracle数据库23ai中，默认的表空间文件大小是大文件（bigfile），所以我们不需要明确指定。

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

-- 创建一个表空间和用户进行测试。
drop user if exists reclaim_user cascade;
drop tablespace if exists reclaim_ts including contents and datafiles;

create tablespace reclaim_ts datafile size 10m autoextend on next 1m;

create user reclaim_user identified by reclaim_user default tablespace reclaim_ts quota unlimited on reclaim_ts;
grant create session, create table to reclaim_user;
grant select_catalog_role to reclaim_user;

-- 在测试模式中创建并填充两个表。
conn reclaim_user/reclaim_user@//localhost:1521/freepdb1

create table t1 (
  id   number,
  col1 varchar2(4000),
  col2 varchar2(4000),
  constraint t1_pk primary key (id)
);

create table t2 (
  id   number,
  col1 varchar2(4000),
  col2 varchar2(4000),
  constraint t2_pk primary key (id)
);

insert /*+append*/ into t1
select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')
from dual
connect by level <= 100000;
commit;

insert /*+append*/ into t2
select rownum, rpad('x', 4000, 'x'), rpad('x', 4000, 'x')
from dual
connect by level <= 100000;
commit;

exec dbms_stats.gather_table_stats(null, 't1');
exec dbms_stats.gather_table_stats(null, 't2');

我们检查与表空间关联的数据文件的大小和表的大小。

select tablespace_name, blocks, bytes/1024/1024 as size_mb
from   dba_data_files
where  tablespace_name = 'RECLAIM_TS';

TABLESPACE_NAME                    BLOCKS    SIZE_MB
------------------------------ ---------- ----------
RECLAIM_TS                         427520       3340

SQL>

column table_name format a10

select table_name, blocks, (blocks*8)/1024 as size_mb
from   user_tables
where  table_name in ('T1', 'T2')
order by 1;

TABLE_NAME     BLOCKS    SIZE_MB
---------- ---------- ----------
T1             200696  1567.9375
T2             200694 1567.92188

SQL>

我们截断第一个表，留下数据文件中的一个间隙。

truncate table t1;

exec dbms_stats.gather_table_stats(null, 't1');

我们可以在测试之间重复这个设置，以便从头开始。

分析大文件表空间

我们运行一个分析，看看通过执行收缩可以节省多少空间。我们调用DBMS_SPACE
包中的SHRINK_SPACE
过程，传入大文件表空间的名称和TS_MODE_ANALYZE
收缩模式常量。

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS', shrink_mode => dbms_space.ts_mode_analyze);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 2
Total Movable Size(GB): 1.56
Original Datafile Size(GB): 3.39
Suggested Target Size(GB): 3.19
Process Time: +00 00:00:00.053777

PL/SQL procedure successfully completed.

SQL>

分析结果认为我们不能节省多少空间，这听起来很可疑，因为我们截断了一个表，这个表大约占据了数据文件一半的空间。

收缩大文件表空间

我们通过调用SHRINK_SPACE
过程来运行收缩操作，并传入表空间名称。

set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 2
Total Moved Size(GB): 1.56
Original Datafile Size(GB): 3.26
New Datafile Size(GB): 1.63
Process Time: +00 00:00:30.586722

PL/SQL procedure successfully completed.

SQL>

尽管分析结果如此，我们已经将相关数据文件减少到大约原始大小的一半。

之前的命令相当于调用过程时使用TS_MODE_SHRINK
收缩模式和TS_TARGET_MAX_SHRINK
目标大小。

set serveroutput on
execute dbms_space.shrink_tablespace('RECLAIM_TS', shrink_mode => dbms_space.ts_mode_shrink, target_size => dbms_space.ts_target_max_shrink);

附加信息

以下是关于收缩大文件表空间的一些附加信息：

• 对象被移动以压缩数据文件中的段，因此所有未使用的空间都在数据文件的末尾。这允许数据文件被收缩以回收未使用的空间。
• 通过SHRINK_SPACE
  进行的在线移动没有传统ALTER TABLE ... MOVE
  的所有限制，尽管文档是这么说的。分析阶段将指示是否有不支持的对象。
• 如果表空间没有设置为自动扩展，操作结束时将没有空间供段在末端增长。你需要手动调整表空间的大小以腾出空间。
• 收缩可能会失败，但如果任何移动成功完成，它仍然可以减少数据文件的大小。
• 我们可以收缩SYSAUX表空间。
• SHRINK_TABLESPACE
  过程有一个重载版本，包括一个SHRINK_RESULT
  输出参数，因此操作的结果可以作为CLOB返回，而不是通过DBMS_OUTPUT
  推送。

更多信息请参见：

• 收缩大文件表空间
• DBMS_SPACE

希望这有帮助。此致敬礼，Tim...