一、创建测试环境
我们需要一个表空间来运行一些测试。在Oracle数据库23ai中，表空间的默认文件大小是bigfile，因此我们不需要显式指定它。
1.1 创建测试用户
1）创建用户
sqlplus sys/oracle@db1:1521/freepdb1 as sysdba
-- 创建测试的用户和表空间
drop user if exists reclaim_user cascade;
drop tablespace if exists reclaim_ts including contents and datafiles;
--设置OMF
alter system set db_create_file_dest='/opt/oracle/oradata/FREE';
--创建表空间
create tablespace reclaim_ts datafile size 10m autoextend on next 1m;
select tablespace_name,bigfile from dba_tablespaces where TABLESPACE_NAME='RECLAIM_TS';
TABLESPACE_NAME BIG
------------------------------ ---
RECLAIM_TS YES2）给用户授权
--创建用户指定默认表空间reclaim_ts并授权
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;1.2 创建测试表
-- 连接到测试用户
conn reclaim_user/reclaim_user@db1: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');1.3 查看表空间和表的体积
我们检查与表空间和表关联的数据文件的大小。
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>二、模拟数据GAP
2.1 截断表
我们截断第一个表t1，在表段开始之前在数据文件中留下一个GAP。
truncate table t1;
exec dbms_stats.gather_table_stats(null, 't1');2.2 分析大文件表空间
我们进行分析，看看通过执行收缩可以节省多少空间。我们在DBMS_SPACE包中调用SHRINK_SPACE过程，传入大文件表空间名称和TS_MODE_ANALYZE收缩模式常量。
sqlplus sys/oracle@db1: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.26
Suggested Target Size(GB): 3.19
Process Time: +00 00:00:00.622483
PL/SQL procedure successfully completed.
SQL>它认为我们不能节省太多空间(3.26-3.19=0.7GB)，这听起来很可疑，因为我们截断了一个表，该表占用了数据文件中大约一半的空间。
2.3 收缩大文件表空间
我们通过使用表空间名称调用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:09.344545
PL/SQL procedure successfully completed.
不管分析如何，我们已将关联的数据文件减小到原始大小的大约一半。
前面的命令相当于调用收缩模式为 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 相关的所有限制。分析阶段将指示是否存在不受支持的对象。
- TS_MODE_SHRINK_FORCE 的收缩模式将为不支持在线移动的对象执行离线移动。如果脱机移动会导致应用程序出现问题，请勿使用此选项。
- 如果表空间未设置为自动扩展，则在操作结束时段将没有空间增长。您将需要手动调整表空间的大小以腾出空间。
- 收缩可能会失败，但如果任何移动成功完成，它仍然可能会减小数据文件的大小。
- 我们可以收缩 SYSAUX 表空间。
- SHRINK_TABLESPACE 过程存在一定的负载，其中包含 SHRINK_RESULT 输出参数，因此操作结果可以作为 CLOB 返回，而不是使用 DBMS_OUTPUT 推出。