[20250504]drop table的恢复思路.txt --//节前做了一次truncate table的恢复，理论也可以应用到drop table的情况，只要建立与原来数据结构一样的表，修改段号等于以前 --//drop table的段号，再扫描对应数据文件就可以恢复drop table的信息。 --//我看了一些的一些恢复操作笔记，当时思路很乱，好像drop table清除段头的信息，还只能通过扫描数据文件，先检查drop后段头的 --//情况。 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 t as select * from all_objects; Table created. SCOTT@book01p> @ o2 t SCOTT@book01p> @ pr ============================== O_OWNER : SCOTT O_OBJECT_NAME : T O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 125488 D_OID : 125488 CREATED : 2025-05-04 11:03:30 LAST_DDL_TIME : 2025-05-04 11:03:30 PL/SQL procedure successfully completed. SCOTT@book01p> @ seg2 t '' SCOTT@book01p> @pr ============================== SEG_MB : 12 SEG_OWNER : SCOTT SEG_SEGMENT_NAME : T SEG_PARTITION_NAME : SEG_SEGMENT_TYPE : TABLE SEG_TABLESPACE_NAME : USERS BLOCKS : 1536 HDRFIL : 12 HDRBLK : 170 PL/SQL procedure successfully completed. 3.查看drop table前后段头的情况。 SCOTT@book01p> alter system checkpoint; System altered. SCOTT@book01p> alter system dump datafile 12 block 170; System altered. --//检查转储文件，发现如下信息： Dump of buffer cache for pdb 3 tsn 5 rdba 0x30000aa at level 2 done. Block dump from disk: buffer tsn: 5 rdba: 0x030000aa (12/170) scn: 0x2311560 seq: 0x01 flg: 0x04 tail: 0x15602301 frmt: 0x02 chkval: 0x4589 type: 0x23=PAGETABLE SEGMENT HEADER Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007FA28C042000 to 0x00007FA28C044000 .... Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 27 #blocks: 1536 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x03000662 ext#: 26 blk#: 98 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1506 mapblk 0x00000000 offset: 26 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x03000662 ext#: 26 blk#: 98 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1506 mapblk 0x00000000 offset: 26 Level 1 BMB for High HWM block: 0x03000601 Level 1 BMB for Low HWM block: 0x03000601 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x030000a9 Last Level 1 BMB: 0x03000601 Last Level II BMB: 0x030000a9 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 27 obj#: 125488 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x030000a8 length: 8 0x030000b0 length: 8 0x030000b8 length: 8 0x030000c0 length: 8 0x030000c8 length: 8 0x030000d0 length: 8 0x030000d8 length: 8 0x030000e0 length: 8 0x030000e8 length: 8 0x030000f0 length: 8 0x030000f8 length: 8 0x03006000 length: 8 0x03006008 length: 8 0x03006010 length: 8 0x03006018 length: 8 0x03006020 length: 8 0x03000100 length: 128 0x03000180 length: 128 0x03000200 length: 128 0x03000280 length: 128 0x03000300 length: 128 0x03000380 length: 128 0x03000400 length: 128 0x03000480 length: 128 0x03000500 length: 128 0x03000580 length: 128 0x03000600 length: 128 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x030000a8 Data dba: 0x030000ab Extent 1 : L1 dba: 0x030000a8 Data dba: 0x030000b0 Extent 2 : L1 dba: 0x030000b8 Data dba: 0x030000b9 Extent 3 : L1 dba: 0x030000b8 Data dba: 0x030000c0 Extent 4 : L1 dba: 0x030000c8 Data dba: 0x030000c9 Extent 5 : L1 dba: 0x030000c8 Data dba: 0x030000d0 Extent 6 : L1 dba: 0x030000d8 Data dba: 0x030000d9 Extent 7 : L1 dba: 0x030000d8 Data dba: 0x030000e0 Extent 8 : L1 dba: 0x030000e8 Data dba: 0x030000e9 Extent 9 : L1 dba: 0x030000e8 Data dba: 0x030000f0 Extent 10 : L1 dba: 0x030000f8 Data dba: 0x030000f9 Extent 11 : L1 dba: 0x030000f8 Data dba: 0x03006000 Extent 12 : L1 dba: 0x03006008 Data dba: 0x03006009 Extent 13 : L1 dba: 0x03006008 Data dba: 0x03006010 Extent 14 : L1 dba: 0x03006018 Data dba: 0x03006019 Extent 15 : L1 dba: 0x03006018 Data dba: 0x03006020 Extent 16 : L1 dba: 0x03000100 Data dba: 0x03000102 Extent 17 : L1 dba: 0x03000180 Data dba: 0x03000182 Extent 18 : L1 dba: 0x03000200 Data dba: 0x03000202 Extent 19 : L1 dba: 0x03000280 Data dba: 0x03000282 Extent 20 : L1 dba: 0x03000300 Data dba: 0x03000302 Extent 21 : L1 dba: 0x03000380 Data dba: 0x03000382 Extent 22 : L1 dba: 0x03000400 Data dba: 0x03000402 Extent 23 : L1 dba: 0x03000480 Data dba: 0x03000482 Extent 24 : L1 dba: 0x03000500 Data dba: 0x03000502 Extent 25 : L1 dba: 0x03000580 Data dba: 0x03000582 Extent 26 : L1 dba: 0x03000600 Data dba: 0x03000602 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x030000a9 End dump data blocks tsn: 5 file#: 12 minblk 170 maxblk 170 --//030000a9= set dba 12,169 = alter system dump datafile 12 block 169 = 50331817 SCOTT@book01p> drop table t purge ; Table dropped. SCOTT@book01p> @ ti New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4757_0001.trc SCOTT@book01p> alter system checkpoint; System altered. SCOTT@book01p> alter system dump datafile 12 block 170; System altered. --//检查转储文件，发现如下信息： Dump of buffer cache for pdb 3 tsn 5 rdba 0x30000aa at level 2 done. Block dump from disk: buffer tsn: 5 rdba: 0x030000aa (12/170) scn: 0x23119f5 seq: 0x1b flg: 0x04 tail: 0x19f5231b frmt: 0x02 chkval: 0x5957 type: 0x23=PAGETABLE SEGMENT HEADER Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007FA28C042000 to 0x00007FA28C044000 7FA28C042000 0000A223 030000AA 023119F5 041B0000 [#.........1.....] 7FA28C042010 00005957 00000000 00000000 00000000 [WY..............] 7FA28C042020 00000000 00000001 00000008 00000A9C [................] 7FA28C042030 00000001 00000062 00000080 03000662 [....b.......b...] 7FA28C042040 00000000 00000001 00000000 000005E2 [................] 7FA28C042050 0021000A 00001E5F 00000001 0000001A [..!._...........] 7FA28C042060 00000062 00000080 03000662 00000000 [b.......b.......] 7FA28C042070 0000001A 00000000 000005E2 03000601 [................] 7FA28C042080 03000601 00000000 00000000 00000000 [................] 7FA28C042090 00000000 00000000 00000000 00000000 [................] Repeat 3 times 7FA28C0420D0 00000001 00002000 00000000 00001434 [..... ......4...] 7FA28C0420E0 00000000 030000A9 00000001 03000601 [................] 7FA28C0420F0 030000A9 00000000 00000000 00000000 [................] 7FA28C042100 00000000 00000000 00000001 00000000 [................] 7FA28C042110 0001EA30 12000000 030000A8 00000008 [0...............] 7FA28C042120 00000000 00000000 00000000 00000000 [................] Repeat 152 times 7FA28C042AB0 030000A8 030000AB 00000000 00000000 [................] 7FA28C042AC0 00000000 00000000 00000000 00000000 [................] Repeat 151 times 7FA28C043440 00000000 00000000 030000A9 00000000 [................] 7FA28C043450 00000000 00000000 00000000 00000000 [................] Repeat 185 times 7FA28C043FF0 00000000 00000000 00000000 19F5231B [.............#..] Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x03000662 ext#: 1 blk#: 98 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1506 mapblk 0x00000000 offset: 1 Disk Lock:: Locked by xid: 0x000a.021.00001e5f -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x03000662 ext#: 26 blk#: 98 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1506 mapblk 0x00000000 offset: 26 Level 1 BMB for High HWM block: 0x03000601 Level 1 BMB for Low HWM block: 0x03000601 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x030000a9 Last Level 1 BMB: 0x03000601 Last Level II BMB: 0x030000a9 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 1 obj#: 125488 flag: 0x12000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x030000a8 length: 8 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x030000a8 Data dba: 0x030000ab -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x030000a9 End dump data blocks tsn: 5 file#: 12 minblk 170 maxblk 170 --//对比可以发现drop后，段头清除许多信息，Extent Map以及Auxillary Map仅仅剩下1个项目，要恢复里面的信息难道相对较大。 --//只能通过扫描数据文件收集需要恢复的记录,另外写一篇blog恢复drop table的数据。
[20250504]drop table的恢复思路.txt
来源：这里教程网
时间：2026-03-03 21:52:35
作者：
编辑推荐：
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 内部讲话曝光！刘强东声称京东外卖利润率不高过 5%，是假道德还是真商战？
内部讲话曝光！刘强东声称京东外卖利润率不高过 5%，是假道德还是真商战？
26-03-03 - 惊天骗局曝光：“水果第一股”爆雷，多名高管被抓，还有员工打卡等待发工资
惊天骗局曝光：“水果第一股”爆雷，多名高管被抓，还有员工打卡等待发工资
26-03-03 - [20250418]绑定变量太多的限制.txt
![[20250418]绑定变量太多的限制.txt](/d/file/efpub/2026/03-03/small85d6e1154f1b9627b9d78570d310ec4c1772545943.jpg "[20250418]绑定变量太多的限制.txt")
[20250418]绑定变量太多的限制.txt
26-03-03 - rac防火墙未禁用服务器重启导致二节点启动异常
rac防火墙未禁用服务器重启导致二节点启动异常
26-03-03 - 数据库管理-第317期 Oracle 12.2打补丁又出问题了（20250421）
- 大学生就业新方向：山姆代购，月入过万
大学生就业新方向：山姆代购，月入过万
26-03-03 - 业务高峰期ddl带崩数据库
业务高峰期ddl带崩数据库
26-03-03 - 利润暴跌30%，高管年薪狂涨84倍：谁在掏空贝壳找房？
利润暴跌30%，高管年薪狂涨84倍：谁在掏空贝壳找房？
26-03-03 - schedule job 新增删除操作简单研究
schedule job 新增删除操作简单研究
26-03-03 - Oracle 11g RAC ASM磁盘组剔盘、加盘实施过程
Oracle 11g RAC ASM磁盘组剔盘、加盘实施过程
26-03-03
