[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;
/