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

[20250512]drop table的恢复3(包含lob类型字段)(补充).txt --//尝试drop table的恢复，发现脚本还可以做一些改进，既然通过bbed扫描的方式知道每块的行记录，这样可以减少扫描范围。 --//参考链接[20250512]drop table的恢复3(包含lob类型字段).txt 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> drop table scanblock purge ; Table dropped. SCOTT@book01p> create table scanblock ( file_id number,block_id number,rowsnum number   )  tablespace TSP_AUDIT; Table created. --//建立新表增加字段rowsnum。 --//扫描数据文件确定数据段号。 $ . finddoid.sh 12 128 32640 128559 data_object_id 128559 max_row = 153 $ paste -d',' scan_128559.txt scan_row_128559.txt | head -4 12,171,151 12,172,151 12,173,151 12,174,153  $ paste -d',' scan_128559.txt scan_row_128559.txt | tail -10 12,24598,151 12,24599,151 12,24600,0 12,24601,0 12,24602,0 12,24603,0 12,24604,151 12,24605,32 12,24606,0 12,24607,0 --//有一些数据块根本不需要扫描。 $ paste -d',' scan_128559.txt scan_row_128559.txt | sed 's/^/insert into scanblock values (/;s/$/);/' | head -4 insert into scanblock values (12,171,151); insert into scanblock values (12,172,151); insert into scanblock values (12,173,151); insert into scanblock values (12,174,153); $ paste -d',' scan_128559.txt scan_row_128559.txt | sed 's/^/insert into scanblock values (/;s/$/);/' >| xz.txt --//执行xz.txt,并且提交。 SCOTT@book01p> truncate table bak_t5 ; Table truncated. SYS@book01p> set timing on SYS@book01p> @ txt/truncTz.txt SCOTT T5_DROP SCOTT BAK_T5 PL/SQL procedure successfully completed. Elapsed: 00:00:14.90 SYS@book01p> set timing off --//这样扫描比原来快了5秒。 SCOTT@book01p> select count(*) from bak_t5;   COUNT(*) ----------      10000 3.附上改进的脚本： 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)    for i in (select file_id relative_fno,block_id, 1 blocks,rowsnum-1 rowsnum from scott.scanblock)    LOOP       v_fno := i.relative_fno;       v_s_bno := i.block_id;       v_e_bno := i.block_id + i.blocks - 1;       FOR j IN v_s_bno .. v_e_bno       LOOP          BEGIN --          FOR x IN 0 .. &&5             FOR x IN 0 .. i.rowsnum             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; /