[20250506]truncate table，drop table恢复使用脚本.txt

--//扫描行号根据具体的情况修改，一般8k不会大于736.没有必要设置太大，不然执行很慢的。
$ 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,block_id 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 .. 150
            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;
/

$ cat versions.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
------------------------------------------------------------------------------------------------------------
--
-- File name:   versions.sql
-- Purpose:     display table record of dml history
--
-- Author:      lfree
--
-- Usage:
--     @ versions <table_name> <col1,..,colN> <scn|s|t|time|timestamp> <scn1|time1> <scn2|time2> <filter>
--
-------------------------------------------------------------------------------------------------------------
set term off

column 2 new_value 2
column 3 new_value 3
column 4 new_value 4
column 5 new_value 5
column 6 new_value 6
column cols new_value v_cols
column st   new_value v_st
column s1   new_value v_s1
column s2   new_value v_s2
column filter  new_value v_filter

select null "2" , null "3" , null "4" , null "5" ,null "6" from dual where 1=2;

select decode('&2',NULL,'&1..*','*','&1..*','&2') cols
       ,decode(lower('&3'),null,'scn','s','scn','scn','scn','t','timestamp','time','timestamp','timestamp','timestamp','scn') st
       ,decode('&4',null,'minvalue','&4') s1
       ,decode('&5',null,'maxvalue','&5') s2
from dual ;

select decode('&6',null,'1=1','&6') "6" from dual ;

set term on

--set echo on verify on

SELECT versions_starttime
        ,versions_endtime
        ,versions_startscn
        ,versions_endscn
        ,versions_xid
        ,versions_operation
        ,&v_cols
       FROM &1 VERSIONS BETWEEN &v_st &v_s1 AND &v_s2
      where ( &6 )
      ORDER BY VERSIONS_STARTSCN nulls first;
      -- FROM &1 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
      -- FROM &1 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
      -- FROM &1 VERSIONS BETWEEN SCN &3 AND &4
      -- FROM &1 VERSIONS BETWEEN TIMESTAMP &&3 and &4

set echo off verify off


 $ cat as_of.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
------------------------------------------------------------------------------------------------------------
--
-- File name:   as_of.sql
-- Purpose:     display table record of dml history using as of scn|timestamp method.
--
-- Author:      lfree
--
-- Usage:
--     @ as_of  <table_name> <col1,..,colN> <scn|s|t|time|timestamp> <scn|timestamp>  <filter>
--
-------------------------------------------------------------------------------------------------------------
set term off

column 2 new_value 2
column 3 new_value 3
column 4 new_value 4
column 5 new_value 5
column 6 new_value 6
column cols new_value v_cols
column st   new_value v_st
column s1   new_value v_s1
column s2   new_value v_s2
column filter  new_value v_filter

select null "2" , null "3" , null "4" , null "5" ,null "6" from dual where 1=2;

select decode('&2',NULL,'&1..*','*','&1..*','&2') cols
       ,decode(lower('&3'),null,'scn','s','scn','scn','scn','t','timestamp','time','timestamp','timestamp','timestamp','scn') st
       ,decode('&4',null,'minvalue','&4') s1
       --,decode('&5',null,'maxvalue','&5') s2
from dual ;

select decode('&5',null,'1=1','&5') "5" from dual ;

set term on

--set echo on verify on

SELECT rowid
        ,&v_cols
       FROM &1 as of &v_st &v_s1
      where ( &5 );
set echo off verify off