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

[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