[20250512]drop table的恢复3(包含lob类型字段).txt

来源：这里教程网时间：2026-03-03 21:57:03 作者：

[20250512]drop table的恢复3(包含lob类型字段).txt --//作为系列测试，增加drop table的恢复(包含lob类型字段)的情况，建立表包含lob字段，每个lob字段包含2个段，1个数据段，1个其 --//索引段。当drop table时，相关数据段全部删除(包括索引段也会一起删除，不过对于恢复，索引可以重建)，这样通过rowid方式恢 --//复需要建立的新表相关数据段指向原来的旧表的数据段，通过测试说明问题。 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 t5 (id number ,text clob); Table created. SCOTT@book01p> insert into t5 select rownum,to_char(rownum)||lpad('x',3964,'x') from dual connect by level <=1e4; 10000 rows created. SCOTT@book01p> commit ; Commit complete. --//记录相关数据段号： SCOTT@book01p> @ o2 t5 SCOTT@book01p> @pr ============================== O_OWNER : SCOTT O_OBJECT_NAME : T5 O_OBJECT_TYPE : TABLE SEG_PART_NAME : O_STATUS : VALID OID : 128559 D_OID : 128559 CREATED : 2025-05-12 15:27:44 LAST_DDL_TIME : 2025-05-12 15:27:44 PL/SQL procedure successfully completed. SCOTT@book01p> @lob scott.%128559% SCOTT@book01p> @pr ============================== OWNER : SCOTT TABLE_NAME : T5 COLUMN_NAME : TEXT SEGMENT_NAME : SYS_LOB0000128559C00002$$ TABLESPACE_NAME : USERS INDEX_NAME : SYS_IL0000128559C00002$$ CHUNK : 8192 PCTVERSION : RETENTION : FREEPOOLS : CACHE : NO LOGGING : YES ENCRYPT : NO COMPRESSION : NO DEDUPLICATION : NO IN_ROW : YES FORMAT : ENDIAN NEUTRAL PARTITIONED : NO SECUREFILE : YES SEGMENT_CREATED : YES RETENTION_TYPE : DEFAULT RETENTION_VALUE : VALUE_BASED : NO MAX_INLINE : 4000 PL/SQL procedure successfully completed. SCOTT@book01p> @ o2 SYS_LOB0000128559C00002$$ SCOTT@book01p> @ pr ============================== O_OWNER : SCOTT O_OBJECT_NAME : SYS_LOB0000128559C00002$$ O_OBJECT_TYPE : LOB SEG_PART_NAME : O_STATUS : VALID OID : 128560 D_OID : 128560 CREATED : 2025-05-12 15:27:44 LAST_DDL_TIME : 2025-05-12 15:27:44 PL/SQL procedure successfully completed. SCOTT@book01p> @ o2 SYS_IL0000128559C00002$$ SCOTT@book01p> @ pr ============================== O_OWNER : SCOTT O_OBJECT_NAME : SYS_IL0000128559C00002$$ O_OBJECT_TYPE : INDEX SEG_PART_NAME : O_STATUS : VALID OID : 128561 D_OID : 128561 CREATED : 2025-05-12 15:27:44 LAST_DDL_TIME : 2025-05-12 15:27:44 PL/SQL procedure successfully completed. --//T5表的数据段号128559。字段text的lob段号128560，其索引段号128561、 SCOTT@book01p> drop table t5 purge ; Table dropped. SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' "versions_operation='D' and VERSIONS_STARTTIME>='2025-05-12 15:32:10'" VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ROWID OBJ# DATAOBJ# NAME -------------------- -------------------- ----------------- --------------- ---------------- - ------------------ ---------- ---------- ------------------------------ 2025-05-12 15:32:12. 41543968 09001100A51F0000 D AAAAASAABAAAI6GAAD 128560 128560 SYS_LOB0000128559C00002$$ 2025-05-12 15:32:12. 41543968 09001100A51F0000 D AAAAASAABAAAI6GAAH 128559 128559 T5 2025-05-12 15:32:12. 41543968 09001100A51F0000 D AAAAASAABAAAI6GAAA 128561 128561 SYS_IL0000128559C00002$$ --//通过版本查询也能够确定删除表以及相关数据段号。 3.开始恢复： --//不能直接建立新表在原来的表空间，为了避免建立新表的覆盖问题.可以在原表空间增加一个数据文件： ALTER TABLESPACE USERS ADD DATAFILE '/u01/oradata/BOOK/book01p/users02.dbf' SIZE 100M AUTOEXTEND ON NEXT 4M MAXSIZE UNLIMITED; SYS@book01p> alter database datafile '/u01/oradata/BOOK/book01p/users01.dbf' offline drop; Database altered. --//注：因为我的测试环境没有打开归档，导致直接offline报错，加入drop没有问题，注意并非真正意思删除。 SYS@book01p> recover datafile 12; Media recovery complete. --//顺手先recover datafile 12;以后可以直接online。 --//然后再建立新表绕开这个覆盖问题。 --//建立的表要加入SEGMENT CREATION IMMEDIATE,不然oracle认为是空表,无法通过rowid方式读取。 SCOTT@book01p> create table t5_drop (id number ,text clob) SEGMENT CREATION IMMEDIATE ; Table created. SCOTT@book01p> column PARTITION_NAME noprint SCOTT@book01p> select * from dba_extents where segment_name='T5_DROP'; SCOTT@book01p> @ pr ============================== OWNER : SCOTT SEGMENT_NAME : T5_DROP PARTITION_NAME : SEGMENT_TYPE : TABLE TABLESPACE_NAME : USERS EXTENT_ID : 0 FILE_ID : 17 BLOCK_ID : 128 BYTES : 65536 BLOCKS : 8 RELATIVE_FNO : 17 PL/SQL procedure successfully completed. --//建立的新表使用的数据段不在数据文件号12上。 --//建立表空间TSP_AUDIT略。 --//ALTER USER SCOTT QUOTA UNLIMITED ON TSP_AUDIT; SCOTT@book01p> create table bak_t5 tablespace TSP_AUDIT as select * from scott.t5_drop where 0=1; Table created. --//扫描获得信息插入的表bak_t5放在另外的表空间,避免覆盖原来的数据文件信息。 --//修改相关段号： SYS@book01p> @ versions obj$ OBJ#,DATAOBJ#,name s '' '' "versions_operation='I' and VERSIONS_STARTTIME>='2025-05-12 15:32:10'" VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ROWID OBJ# DATAOBJ# NAME -------------------- -------------------- ----------------- --------------- ---------------- - ------------------ ---------- ---------- ------------------------------ 2025-05-12 15:39:44. 41545595 04000800821F0000 I AAAAASAABAAAI6GAAD 128563 128563 SYS_LOB0000128562C00002$$ 2025-05-12 15:39:44. 41545595 04000800821F0000 I AAAAASAABAAAI6GAAG 128562 128562 T5_DROP 2025-05-12 15:39:44. 41545595 04000800821F0000 I AAAAASAABAAAI6GAAA 128564 128564 SYS_IL0000128562C00002$$ 2025-05-12 15:42:45. 41548664 06000400471F0000 I AAAAASAABAAAI6GAAL 128566 128566 SYS_LOB0000128565C00002$$ 2025-05-12 15:42:45. 41548664 06000400471F0000 I AAAAASAABAAAI6GAAH 128565 128565 BAK_T5 2025-05-12 15:42:45. 41548664 06000400471F0000 I AAAAASAABAAAI6GAAK 128567 128567 SYS_IL0000128565C00002$$ 6 rows selected. --//建立新表以及lob以及索引段号分别是128562，128563，128564 --//修改指向旧表T5. update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128562) set DATAOBJ#=128559; update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128563) set DATAOBJ#=128560; update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128564) set DATAOBJ#=128561; commit ; SYS@book01p> alter system flush shared_pool; System altered. --//扫描数据文件确定数据段号。 $ . finddoid.sh 12 128 32640 128559 data_object_id 128559 max_row = 153 --//该脚本顺便确定数据块的最大行记录153条。 --//建立2个文件： $ paste scan_128559.txt scan_row_128559.txt | head -4 12,171 151 12,172 151 12,173 151 12,174 153 $ sed 's/^/insert into scanblock values (/;s/$/);/' scan_128559.txt | head -3 insert into scanblock values (12,171); insert into scanblock values (12,172); insert into scanblock values (12,173); $ sed 's/^/insert into scanblock values (/;s/$/);/' scan_128559.txt >| xy.txt SYS@book01p> create table scott.scanblock ( file_id number,block_id number ) tablespace TSP_AUDIT; Table created. --//注意建立的新表一定不能使用原来的表空间，避免覆盖。 --//执行xy.txt脚本，注意提交。 SYS@book01p> set timing on SYS@book01p> @ txt/truncT.txt SCOTT T5_DROP SCOTT BAK_T5 152 PL/SQL procedure successfully completed. Elapsed: 00:00:19.85 SYS@book01p> set timing off --//参数5=152，行号从0开始计数。 --//补充应该可以再改进减少扫描范围，另外写一篇blog。 SCOTT@book01p> select count(*) from bak_t5; COUNT(*) ---------- 10000 --//完全恢复。 4.收尾还原： update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128562) set DATAOBJ#=128562; update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128563) set DATAOBJ#=128563; update (select OBJ#,DATAOBJ#,OWNER#,NAME from obj$ where obj#=128564) set DATAOBJ#=128564; commit ; 5.附上相关执行脚本： $ cat finddoid.sh #! /bin/bash # argv1=file_number argv2=begin_block argvs3=end_block argv4=data_object_id file_number=$1 begin_block=$2 end_block=$3 data_object_id=$4 # scan begin_block to end_block,define search scope. /bin/rm scan_${data_object_id}.txt 2>/dev/null #seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \ #rlbbed | grep "^BBED" | egrep "$file_number,|ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan_${data_object_id}.txt seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo -n $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \ rlbbed | grep -B1 --no-group-separator "ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan_${data_object_id}.txt # scan begin_block to end_block,obtain max row. /bin/rm scan_row_${data_object_id}.txt 2>/dev/null cat scan_${data_object_id}.txt | xargs -IQ echo p dba Q kdbt[0].kdbtnrow | rlbbed | grep kdbtnrow | awk '{print $NF}' >| scan_row_${data_object_id}.txt max_row=$(sort -nr scan_row_${data_object_id}.txt | head -1) echo data_object_id $data_object_id max_row = $max_row $ cat txt/truncT.txt DECLARE v_fno NUMBER; v_s_bno NUMBER; v_e_bno NUMBER; v_rowid ROWID; v_owner VARCHAR2 (100) := '&&1'; v_table VARCHAR2 (100) := '&&2'; v_o_owner VARCHAR2 (100) := '&&3'; v_o_table VARCHAR2 (100) := '&&4'; v_dataobj NUMBER; v_sql VARCHAR2 (4000); v_tablespace VARCHAR2 (100); nrows NUMBER; BEGIN nrows := 0; SELECT data_object_id INTO v_dataobj FROM dba_objects WHERE owner = v_owner AND object_name = v_table; SELECT tablespace_name INTO v_tablespace FROM dba_tables WHERE owner = v_owner AND table_name = v_table; -- FOR i -- IN (SELECT relative_fno, block_id, blocks FROM dba_extents WHERE owner = v_owner AND segment_name = v_table AND extent_id = 0 -- UNION ALL -- SELECT relative_fno, block_id, blocks FROM dba_free_space WHERE tablespace_name = v_tablespace -- UNION ALL -- SELECT relative_fno, block_id, blocks -- FROM (SELECT relative_fno -- ,block_id -- ,blocks -- ,ROW_NUMBER () OVER (PARTITION BY owner, segment_name, partition_name ORDER BY extent_id DESC) -- rn -- FROM dba_extents -- WHERE tablespace_name = v_tablespace AND extent_id > 0) -- WHERE rn = 1) for i in (select file_id relative_fno,block_id, 1 blocks from scott.scanblock) LOOP v_fno := i.relative_fno; v_s_bno := i.block_id; v_e_bno := i.block_id + i.blocks - 1; --v_e_bno:=i.block_id+1-1; --//using scanblock method FOR j IN v_s_bno .. v_e_bno LOOP BEGIN FOR x IN 0 .. &&5 LOOP v_rowid := DBMS_ROWID.rowid_create ( 1 ,v_dataobj ,v_fno ,j ,x); v_sql := 'insert into ' || v_o_owner || '.' || v_o_table || ' select * from ' || v_owner || '.' || v_table || ' where rowid=:1'; EXECUTE IMMEDIATE v_sql USING v_rowid; IF SQL%ROWCOUNT = 1 THEN nrows := nrows + 1; END IF; IF (MOD (nrows, 10000) = 0) THEN COMMIT; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END; COMMIT; END LOOP; END LOOP; END; /

编辑推荐：

返回列表

[20250512]drop table的恢复3(包含lob类型字段).txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推