[20250511]记录truncate table相关数据段号的变化.txt

[20250511]记录truncate table相关数据段号的变化.txt

--//恢复truncate table后恢复遇到的问题，记录分析看看truncate table后相关数据段号的变化.

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试环境建立：
SCOTT@book01p> create table t4 (id number ,text clob);
Table created.

SCOTT@book01p> insert into t4 select rownum,to_char(rownum)||lpad('x',3964,'x') from dual connect by level <=10;
10 rows created.

SCOTT@book01p> commit ;
Commit complete.

SCOTT@book01p> @ o2 t4
SCOTT@book01p> @ pr
==============================
O_OWNER                       : SCOTT
O_OBJECT_NAME                 : T4
O_OBJECT_TYPE                 : TABLE
SEG_PART_NAME                 :
O_STATUS                      : VALID
OID                           : 128062
D_OID                         : 128062
CREATED                       : 2025-05-11 16:21:06
LAST_DDL_TIME                 : 2025-05-11 16:21:06
PL/SQL procedure successfully completed.

SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' "OBJ#>=128062"
VERSIONS_STARTTIME   VERSIONS_ENDTIME     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V       OBJ#   DATAOBJ# NAME
-------------------- -------------------- ----------------- --------------- ---------------- - ---------- ---------- ------------------------------
2025-05-11 16:21:05.                               41458807                 0A001700811F0000 I     128062     128062 T4
2025-05-11 16:21:05.                               41458807                 0A001700811F0000 I     128064     128064 SYS_IL0000128062C00002$$
2025-05-11 16:21:05.                               41458807                 0A001700811F0000 I     128063     128063 SYS_LOB0000128062C00002$$
--//建表的同时建立lob段以及对应索引。

SCOTT@book01p> truncate table t4 ;
Table truncated.

SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' "OBJ#>=128062"
VERSIONS_STARTTIME   VERSIONS_ENDTIME     VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     V       OBJ#   DATAOBJ# NAME
-------------------- -------------------- ----------------- --------------- ---------------- - ---------- ---------- ------------------------------
2025-05-11 16:21:05. 2025-05-11 16:22:45.          41458807        41459150 0A001700811F0000 I     128063     128063 SYS_LOB0000128062C00002$$
2025-05-11 16:21:05. 2025-05-11 16:22:45.          41458807        41459150 0A001700811F0000 I     128062     128062 T4
2025-05-11 16:21:05.                               41458807                 0A001700811F0000 I     128064     128064 SYS_IL0000128062C00002$$
2025-05-11 16:22:45.                               41459150                 020015007E1F0000 U     128062     128066 T4
2025-05-11 16:22:45.                               41459150                 020015007E1F0000 U     128063     128065 SYS_LOB0000128062C00002$$

--//可以看出truncate table后，表的数据段号以及lob的段号发生变化，先修改lob段再修改表段，注意DATAOBJ#的变化，而lob的索引
--//段没有发生变化，难道索引段不回收吗？可以推断只要相关段没有覆盖，如果通过扫描rowid的方式恢复，需要修改表的数据段号以
--//及lob的段号。