执行tablespace shrink，表空间未缩减

首页  ꁇ  知识库  ꁇ  常见问题  ꁇ  执行tablespace shrink，表空间未缩减

问题现象

客户测试环境磁盘空间被占满，USERS表空间对应文件有243G，执行如下步骤，发现表空间对应文件没有减少，磁盘空间依然被占满

●  truncate 表空间USERS下所有的表

●  alter tablespace USERS shrink sapce;

 

问题的风险及影响

表空间释放不了造成服务器空间不足，业务无法开展。 

 

问题影响的版本

客户详细版本：23.1.1.200

 

问题发生原因

●  表空间只能自动增长，不会自动缩减，truncate table 不能释放表空间回操作系统，需要使用alter tablespace <tablespace name> shrink

●  alter tablespace shrink会根据ctrl文件记录的表空间信息自动判断表空间文件的情况，在文件末尾有没有数据的block的时候，调用文件操作truncate掉末尾的空间，归还末尾没有使用的空间给操作系统。ADS_TRMS_ASSETS_REALTIME_PRICE_A这个索引在USERS，表不在USERS，从dba_segments看存在190G的索引数据，和一线确认是该索引占用的空间，虽然现场删除了USERS下53G的表数据，但由于数据是按block分散写，53G不完全是在末尾，因此释放不了。

执行 组 1

●  alter table <table name>shrink可以让数据变紧密，加快全表扫描的速度，但是不能整体移动，如果已删除的53G的数据在前面，ADS_TRMS_ASSETS_REALTIME_PRICE_A 数据在后面，不能释放表空间。oracle有alter table move命令，可以消除前面的空洞，但是崖山不支持。

 

解决方法及规避方式

规避方式：在紧急的情况下，可以考虑缩减redo文件，在确保归档同步已经完成的情况下，可以删除较老的归档文件，释放空间使服务器能正常使用。

现场再次对ADS_TRMS_ASSETS_REALTIME_PRICE_A表数据做了清理，保留了少量数据，之后执行了alter tablespace USERS shrink sapce后USERS空间缩减到3.3G。

 

问题分析和处理过程

检查环境配置

RECYCLEBIN_ENABLED、UNDO_RETENTION参数配置正常，均为默认值（RECYCLEBIN_ENABLED如果为ON，会导致删除的数据进入回收站， 空间不会释放）

SQL> show parameter rec

 

NAME VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

RECOVERY_PARALLELISM 32

RECYCLEBIN_ENABLED OFF

DIN_RECONNECT_TIME 5000

INTERCONNECT_RECEIVE_TIMEOUT 5

CLUSTER_RECONNECT_TIME 5000

 

5 rows fetched.

 

SQL> show parameter undo

 

NAME VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

UNDO_RETENTION 300

UNDO_SHRINK_ENABLED TRUE

UNDO_SHRINK_INTERVAL 3600

 

3 rows fetched.

 

检查表空间相关视图

发现ADS_TRMS_ASSETS_REALTIME_PRICE_A表索引在USER下，进一步检查发现该索引占用190G空间

(lowrisk) -bash-4.2$ ys -c "select * from dba_tables where tablespace_name = 'USERS'"

(lowrisk) -bash-4.2$ ys -c "select * from dba_indexes where tablespace_name = 'USERS'"

执行 组 2

 

2个怀疑点，核对相关机制排除

怀疑点：

●  segment高水位机制，高水位block被索引数据占据（segment上有高水位线，高水位线以上是未使用的block）。segment是逻辑概念，每个表在DBA_SEGMENT中有对应的一条记录，记录使用的block情况，实际物理存储是block。核查tablespace shrink逻辑，发现shrink过程和segment无关。该怀疑点排除。

●  稀疏文件，block中有大量空闲，每个block分散写入了不同表的数据，其他表被truncate之后，ADS_TRMS_ASSETS_REALTIME_PRICE_A的索引数据还分散在绝大部分block上，每个block都还在使用中，因此不能释放。从block的数据结构分析，dataOid对应segment id，即每个block只能属于一个segment，从dba_segments中看，对象（表、索引）和segment是一一对应的，那么我们的一个block必然只能记录一个对象的数据，不可能有不同表的数据记录在同一个block上。该怀疑点不成立。

 

得出结论

得出结论：末尾的空间是没有使用的，才能改变。

结合表USERS表空间存放了190的索引数据看，确认问题是文件末尾还有在使用的数据，即使中间有空洞，但是崖山不支持ALTER TABLE MOVE命令，无法释放。 

 

构造用例验证结论

在测试环境新建表空间，2个表的大小一样，但是在表空间中的位置不一样，构造2组用例分别truncate对比，可以看出在文件末尾有空闲block的时候，才能被shrink释放：

步骤

用例1

用例2

建表、建库

create TABLESPACE yashan1;

ALTER TABLESPACE yashan1 SHRINK SPACE;

CREATE TABLE "AREA"

("AREA_NO" VARCHAR(100),

"AREA_NAME" VARCHAR(100),

"DHQ" VARCHAR(100)

) PCTFREE 8 INITRANS 2 MAXTRANS 255

LOGGING

TABLESPACE "yashan1"

ORGANIZATION HEAP;

 

CREATE TABLE "AREA2"

("AREA_NO" VARCHAR(100),

"AREA_NAME" VARCHAR(100),

"DHQ" VARCHAR(100)

) PCTFREE 8 INITRANS 2 MAXTRANS 255

LOGGING

TABLESPACE "yashan1"

ORGANIZATION HEAP;

同用例1

使用dbeaver插入1,000,000数据

执行 表5

初始表空间是1M

area、area2表插入数据后各执行，可验证1,000,000数据约为70M，2表个总大小140M，检查area2表的数据， 

ALTER TABLESPACE yashan1 SHRINK SPACE;

area表大小70M

执行 表 组6

area+area2表大小140M

执行 表 组7

同用例1

检查表的位置

SELECT * FROM DBA_SEGMENTS ds WHERE TABLESPACE_NAME ='YASHAN1';

从header_block可以看出2个表的位置

执行 表8

同用例1

删除数据

TRUNCATE TABLE AREA;

TRUNCATE TABLE AREA2;

shrink

ALTER TABLESPACE yashan1 SHRINK SPACE;

ALTER TABLESPACE yashan1 SHRINK SPACE;

检查表空间大小变化

表空间大小不变为141M

执行 表 组9

表空间大小71M

执行 表 组 10

 

怎样确认shrink tablespace能释放多少空间

23.2版本，可以通过dba_free_space查看tablespace空闲的连续block，block_id最大的一行对应的空间大小即shrink tablespace能释放的空间。

执行 组 11

shrink之后标红的这条记录不存在，这又带来另一个问题：如果有人执行过shrink，但是其他人不知道，那么查看dba_free_space还有空间，但是执行之后，空间不变化！

 

最终确认方法：

●  由于必须是在文件末尾的block才能被释放，那么free block的最后一块，必须等于文件的total block，而这个在DBA_DATA_FILES中可以查到。

●  由于计算的时候，是按文件来计算的，而一个tablespace可以对应多个文件，这里根据文件的全路径做split得到文件名，dba_free_space 中的TABLESPACE_NAME和RELATIVE_FNO和文件名对应，DBA_DATA_FILES有文件blocks的总数，两表关联，即可得到在文件末尾的空闲block，即可以释放的空间。

SQL> SELECT * FROM DBA_DATA_FILES ddf WHERE TABLESPACE_NAME = 'YASHAN1';

 

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS MAXBYTES MAXBLOCKS AUTO_EXTEND NEXT_SIZE USER_BYTES USER_BLOCKS

---------------------------------------------------------------- ------------ ---------------------------------------------------------------- --------------------- ------------ --------- --------------------- ----------- ----------- --------------------- --------------------- ------------

/home/yashan/yasdb_data/db-1-1/dbfiles/YASHAN10 8 YASHAN1 152043520 18560 ONLINE 549755813888 67108864 ON 67108864 2097152 256

/home/yashan/yasdb_data/db-1-1/dbfiles/YASHAN11 9 YASHAN1 67108864 8192 ONLINE 549755813888 67108864 ON 67108864 66060288 8064

 

2 rows fetched.

 

 

SQL> SELECT * FROM dba_free_space WHERE TABLESPACE_NAME = 'YASHAN1';

 

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

---------------------------------------------------------------- ------------ ------------ --------------------- ------------ ------------

YASHAN1 8 17280 2097152 256 0

YASHAN1 9 128 66060288 8064 1

 

2 rows fetched.

 

SQL>

 

SELECT dfs.*, ddf.FILE_NAME FROM dba_free_space dfs, DBA_DATA_FILES ddf WHERE dfs.TABLESPACE_NAME = 'YASHAN1' AND ddf.TABLESPACE_NAME = dfs.TABLESPACE_NAME

   2 AND (dfs.TABLESPACE_NAME || DFS.RELATIVE_FNO) = SPLIT(ddf.file_name, '/', -1) AND (dfs.BLOCK_ID + dfs.BLOCKS) = ddf.BLOCKS;

 

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO FILE_NAME

---------------------------------------------------------------- ------------ ------------ --------------------- ------------ ------------ ----------------------------------------------------------------

YASHAN1 9 128 66060288 8064 1 /home/yashan/yasdb_data/db-1-1/dbfiles/YASHAN11

 

1 row fetched.

 

SQL>

 

Oracle是怎么做的

Oracle alter tablespace shrink只支持临时表空间的缩减：

上面讲述中提到了alter table move，实际上oracle不支持对永久存储的表空间做shrink！否则报错：

SQL 错误 [12916] [72000]: ORA-12916: 无法压缩永久表空间或字典管理的表空间

SQL> ALTER TABLESPACE USERS SHRINK SPACE;

ALTER TABLESPACE USERS SHRINK SPACE

*

ERROR at line 1:

ORA-12916: cannot shrink permanent or dictionary managed tablespace

 

 

SQL>

ALTER TABLESPACE yashan1 SHRINK SPACE;SQL>

ALTER TABLESPACE yashan1 SHRINK SPACE

*

ERROR at line 1:

ORA-12916: cannot shrink permanent or dictionary managed tablespace

 

SQL>

ALTER TABLESPACE TEMP SHRINK SPACE;SQL>

 

Tablespace altered.

 

SQL>

参考oracle官网说明也可以确认：

E:\周文琴（wonchinjuice）\2024年\1、官网需求\3、知识库文章上传-0411至0430\已排版\0411（3篇）\配图\执行tablespace shrink，表空间未缩减\1600PX\执行 12.png执行 12

那么Oracle怎么做的呢：

●  计算空间实际大小，使用resize，参考（ORACLE缩小表空间的数据文件 - 菜鸟大明儿哥 - 博客园 (cnblogs.com)）

●  创建临时空间，使用alter table move把表挪到额外表空间，确认空间下没有数据之后，去掉空间数据文件（ALTER TABLESPACE *** DROP DATAFILE '***';），之后再把表挪回来，再删除额外的表空间，参考（ORACLE 缩减表空间大小（删除存有数据的数据文件文件或缩减数据文件大小）-CSDN博客）

 

经验总结

●  提前规划好大表对应空间。USER表空间是默认表空间，ADS_TRMS_ASSETS_REALTIME_PRICE_A大表的数据不应该放在USERS空间下，需要提前规划好其对应空间。

●  一个segment对应一个对象（表、索引），一个block不可能记录2个对象（表、索引）的数据。segment是逻辑概念，block是物理概念，segment方便做表扫描等操作。

●  table shrink 不能释放空间，只能使得表数据变得紧密。tablespace shrink需要根据文件末尾是否有空闲的block，释放对应空间。在有其他数据存在的情况下，table shrink 后再执行 tablespace shrink，也不一定能释放表空间，需要支持alter table move才行。

●  23.2版本，可以通过dba_free_space查看tablespace空闲的连续block，block_id最大的一行对应的空间大小即shrink tablespace能释放的空间。核查语句：

SELECT dfs.*, ddf.FILE_NAME FROM dba_free_space dfs, DBA_DATA_FILES ddf WHERE dfs.TABLESPACE_NAME = 'YASHAN1' AND ddf.TABLESPACE_NAME = dfs.TABLESPACE_NAME 2 AND (dfs.TABLESPACE_NAME || DFS.RELATIVE_FNO) = SPLIT(ddf.file_name, '/', -1) AND (dfs.BLOCK_ID + dfs.BLOCKS) = ddf.BLOCKS;

 

TODO

需要补充功能：

●  提供接口或视图，可以识别到表在使用中、未使用、空闲的block编码、高水位，以便清晰的看到数据的分布。参考oracle dba_extents视图和DBMS_SPACE的需求包（unused_space、free_blocks、TABLE_SPACE_USAGE）

dba_extents可以看到一个表使用的哪些block（开始使用block，连续多少个block，一个表对应多行数据）

●  支持ALTER TABLE MOVE，挤出空闲的块

altertable支持move功能，重新将数据分配到一个segment

 

ꄘ浏览量：0
뀷上一篇： 无
뀠下一篇： 无