[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的段号。