我们的文章会在微信公众号IT民工的龙马人生和博客网站 ( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
今天谈谈很早之前朋友问到的一个案例，表空间使用率异常增加，新增对象都是临时字段，并且段名是很奇怪，由数字和.构成，如（11.123）这种方式。这个案例相对来说比较极端，客户采用的文件系统，并且数据文件为自动扩张，最后出现空间耗尽，数据文件无法收缩的现象。今天就好大家一起来模拟一下这种现象：
环境准备
由于是自己的环境，也为了保证后续的操作不报错，所以这里看起了归档日志强制删除的脚本。
[oracle@oracleadg sql]$ sh ./arch_delete_force_all.sh
2025-08-23 21:46:09: Using ORACLE_SID=htz191, interval=10 seconds.
2025-08-23 21:46:09: Press Ctrl+C to stop the script.
2025-08-23 21:46:09: Starting RMAN archived log cleanup.
2025-08-23 21:46:14: Cleanup finished. Sleeping for 10 seconds.
2025-08-23 21:46:24: Starting RMAN archived log cleanup.
2025-08-23 21:46:28: Cleanup finished. Sleeping for 10 seconds.
2025-08-23 21:46:38: Starting RMAN archived log cleanup.
创建测试表
create table htz as select * from dba_objects;
多次执行下面语句
insert into htz as select * from htz;
commit;
确保测试表足够的大，下面是我环境中的htz表的大小。
SQL> @segment_size.sql
Enter Search owner Name (i.e. SCOTT|ALL) :
Enter Search Segment Name (i.e. DEPT|ALL) :
Enter Search Tablespace Name (i.e. DEPT|ALL) : system
Enter Display rows Name (i.e. 20) :
Name Total
OWNER SEGMENT_NAME SEGMENT_TYPE Partition size(M)
-------------------- ----------------------------------- --------------- ----------------------------------- ---------
SYS HTZ TABLE 11518
SYS IDL_UB1$ TABLE 398
SYS SYS_LOB0000022516C00008$$ LOBSEGMENT 192
SYS C_TOID_VERSION# CLUSTER 49
SYS SOURCE$ TABLE 40
我这里是11G左右的大小。
确认数据库状态
SQL> @db_status.sql
flashback Switchover Database Force
OPEN_MODE LOG_MODE On Status Role PROTECTION_MODE Loggin
-------------------- ------------------------ ---------- -------------------- -------------------- -------------------- ------
READ WRITE ARCHIVELOG YES NOT ALLOWED LOGICAL STANDBY MAXIMUM PERFORMANCE YES
确认表空间的容量
SQL> @db_size.sql
*******************tablespace size**************************
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------------------------------------ ---------- ---------- ---------- ----------
TEST 1024 1023 100 .1
TEMP 224 190 85 15.18
UNDOTBS1 995 542.75 55 45.45
UNDOTBS2 25 13.1875 53 47.25
USERS 18.75 2.0625 11 89
SYSAUX 1140 59.625 5 94.77
SYSTEM 12688 15.1875 0 99.88
取消归档日志自动删除
将前面的归档日志自动删除脚本取消执行，因为后面我们要用到归档日志文件去解析，查看logmnr中的SQL语句。
开启表空间自动扩容脚本
因为后续的操作要生成11G的表，可能出现表空间容灾不够，所以开启表空间自动扩容脚本。
[oracle@oracleadg sql]$ watch -n 2 ./tbscale -l TEST:50
Every 2.0s: ./tbscale -l TEST:50 Sat Aug 23 22:13:44 2025
[INFO] 2025/08/23 22:13:44 This script will connect to the database using 'sqlplus / as sysdba'. Please ensure OS authentication is configured correctly.
[INFO] 2025/08/23 22:13:44 Database status: OPEN
[INFO] 2025/08/23 22:13:44 Threshold mode: Only checking tablespaces and thresholds specified by -l
[INFO] 2025/08/23 22:13:44 ========== Processing tablespace: TEST (threshold 50.00%) ==========
[INFO] 2025/08/23 22:13:44 Tablespace [TEST] free space 48.34% < threshold 50.00%, expansion required.
[INFO] 2025/08/23 22:13:44 Underlying storage has sufficient space for tablespace [TEST] (Available 99065.52 MB >= Needed 1024 MB)
[INFO] 2025/08/23 22:13:44 Expansion SQL: ALTER TABLESPACE TEST ADD DATAFILE '/oracle/app/oracle/oradata/HTZ19TWO/datafile/TEST_14.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
[INFO] 2025/08/23 22:14:02 Successfully added a new datafile to tablespace [TEST].
[INFO] 2025/08/23 22:14:02 Script execution finished.
创建新表
这里要保证htz表足够的大，这样才方便后续的实验和观察，否者还没有观察到，SQL语句就执行完了。
SQL> create table htz1 tablespace test as select /*+ parallel(a 5) */ * from htz a;
确认对象大小
SQL> @segment_size.sql
Enter Search owner Name (i.e. SCOTT|ALL) :
Enter Search Segment Name (i.e. DEPT|ALL) :
Enter Search Tablespace Name (i.e. DEPT|ALL) : TEST
Enter Display rows Name (i.e. 20) :
Name Total
OWNER SEGMENT_NAME SEGMENT_TYPE Partition size(M)
-------------------- ----------------------------------- --------------- ----------------------------------- ---------
SYS 11.130 TEMPORARY 5760
这里看到生成一个临时段，对应段名为：11.130，现象跟之前朋友说的案例一模一样。
取消创建表语句
取消创建表语句的执行。
SQL> create table htz1 tablespace test as select /*+ parallel(a 5) */ * from htz a;
^Ccreate table htz1 tablespace test as select /*+ parallel(a 5) */ * from htz a
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
这里直接终止命令的运行。
查看表空间对象
已经没有任何对象了
SQL> @segment_size.sql
Enter Search owner Name (i.e. SCOTT|ALL) :
Enter Search Segment Name (i.e. DEPT|ALL) :
Enter Search Tablespace Name (i.e. DEPT|ALL) : TEST
Enter Display rows Name (i.e. 20) :
no rows selected
表空间的使用率也降下来了。
SQL> @db_size.sql
*******************tablespace size**************************
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------------------------------------ ---------- ---------- ---------- ----------
TEST 11264 11253 100 .1
TEMP 224 190 85 15.18
UNDOTBS1 995 542.75 55 45.45
UNDOTBS2 25 13.1875 53 47.25
USERS 18.75 2.0625 11 89
SYSTEM 13618 945.1875 7 93.06
SYSAUX 1140 51.5625 5 95.48
上面的现象跟朋友说的一模一样了，临时段，表空间使用率增加后又恢复正常。
解析归档日志
SQL> @logmnr_arch.sql '2025-08-23 23:03:57' '2025-08-23 23:04:53' 'Y'
Starting LogMiner...
LogMiner started successfully.
这里通过脚本来解析归档日志，查询解析记录，可以得到下面这条唯一的SQL语句。
SQL_REDO
--------------------------------------------------------------------------------
update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '8', "EXTENTS" = '1', "INIEXTS
" = '8', "MINEXTS" = '1', "MAXEXTS" = '2147483645', "EXTSIZE" = '128', "EXTPCT"
= '0', "USER#" = '0', "LISTS" = '0', "GROUPS" = '0', "BITMAPRANGES" = '214748364
5', "CACHEHINT" = '0', "SCANHINT" = '0', "HWMINCR" = '77446', "SPARE1" = '419456
1' where "FILE#" = '13' and "BLOCK#" = '130' and "TYPE#" = '3' and "TS#" = '6' a
nd "BLOCKS" = '8' and "EXTENTS" = '1' and "INIEXTS" = '8' and "MINEXTS" = '1' an
d "MAXEXTS" = '2147483645' and "EXTSIZE" = '128' and "EXTPCT" = '0' and "USER#"
= '0' and "LISTS" = '0' and "GROUPS" = '0' and "BITMAPRANGES" = '2147483645' and
"CACHEHINT" = '0' and "SCANHINT" = '0' and "HWMINCR" = '77446' and "SPARE1" = '
4325633' and "SPARE2" IS NULL and ROWID = 'AAAAAIAABAAAMgSAAj';
前面的13.130这个名字就是由FILE#.BLOCK#构成的。
结束
到这里面我们基本演示朋友说到这个案例的现象，在这个现象背后其实有一个Oracle的基础知识就是create table as select 这种方式创建表时，Oracle是先创建临时段，临时段创建成功后，Oracle才会去做rename的操作和表定义的信息，这里可以通过去解析归档日志可以获得更多详细的信息。
——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
故障处理：Oracle表空间异常增长后又恢复正常的故障模拟与分析：等您坐沙发呢！