根据rowid读取表数据，存放到新表中(常用于数据损坏提取)

以下测试当test表数据根据rowid读取表数据，存放到新表中。
常用于表记录存在损坏的情况下如何提取正常的数据。
思路步骤：

1. 提取源表建表、索引等DDL语句

SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 300000
SET FEEDBACK OFF
SET HEADING OFF
SET SERVEROUTPUT ON SIZE 1000000
COLUMN TXT FORMAT a3000 WORD_WRAPPED
select dbms_metadata.get_ddl('TABLE','TEST','JYC') TXT from dual;
select dbms_metadata.get_ddl('INDEX','PK_TEST','JYC') TXT from dual;

2. 创建备份表

create table test_bak as select * from test where 1<>1;

3. rename原表

alter table TEST rename to TEST_BAK;

参考：https://www.modb.pro/db/658363

4. 重建主键索引相关

ALTER TABLE "JYC"."TEST" DROP PRIMARY KEY;
DROP INDEX "JYC"."PK_TEST";   

CREATE UNIQUE INDEX "JYC"."PK_TEST" ON "JYC"."TEST" ("ID");

alter table "JYC"."TEST" add constraint PK_TEST primary key (ID) using index;

5.根据rowid读取表数据，存放到新表中

操作记录如下：

[oracle@ora01 sqlplus jyc/jyc@jycdb

SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 7月 13 16:09:16 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

上次成功登录时间: 星期五 7月  07 2023 13:23:21 +08:00

连接到: 
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from test;

        ID NAME
---------- --------------------
         3 xx
         5 kkd
         4 ok
         6 sdf
         1 jie
         2 zxm

已选择 6 行。

SQL> create table test_bak as select * from test where 1<>1;

表已创建。

SQL> select * from test;

        ID NAME
---------- --------------------
         3 xx
         5 kkd
         4 ok
         6 sdf
         1 jie
         2 zxm

已选择 6 行。

SQL> select * from test_bak;

未选定行

SQL> create table bad_rows (owner varchar2(20),myrowid varchar2(50),error_code varchar2(200));

表已创建。

SQL> set serveroutput on
SQL> DECLARE
    TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
    CURSOR c1 IS  select  rowid from "JYC"."TEST" t ;
    r RowIDTab;
    rows  NATURAL := 1000;
    bad_rows number := 0 ;
    errors number;
    error_code number;
    myrowid rowid;
   BEGIN
    OPEN c1;
    LOOP
      FETCH  c1 BULK COLLECT INTO r LIMIT rows;
      EXIT WHEN r.count=0;
      BEGIN
       FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
        insert into "JYC"."TEST_BAK"
        select /*+ ROWID(A) */ *
        from "JYC"."TEST" A where rowid = r(i);
      EXCEPTION
      when OTHERS then
       BEGIN
        errors := SQL%BULK_EXCEPTIONS.COUNT;
        FOR err1 IN 1..errors LOOP
            error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
            myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
            bad_rows := bad_rows + 1;
            insert into JYC.bad_rows values('TEST',myrowid, error_code);
        END LOOP;
        END;
      END;
     commit;
    END LOOP;
    commit;
    CLOSE c1;
    dbms_output.put_line('Total Bad Rows: '||bad_rows);
   END;
   /
Total Bad Rows: 0

PL/SQL 过程已成功完成。

SQL> select * from test_bak;

        ID NAME
---------- --------------------
         1 jie
         2 zxm
         3 xx
         4 ok
         5 kkd
         6 sdf

已选择 6 行。

SQL> select * from bad_rows;

未选定行


SQL> select rowid from test;          

ROWID
------------------
AAAR0hAAMAAAACHAAA
AAAR0hAAMAAAACHAAB
AAAR0hAAMAAAACFAAA
AAAR0hAAMAAAACGAAB
AAAR0hAAMAAAACGAAA
AAAR0hAAMAAAACGAAC

已选择 6 行。

SQL> select rowid from test_bak;                   

ROWID
------------------
AAAR6KAAMAAAADuAAA
AAAR6KAAMAAAADuAAB
AAAR6KAAMAAAADuAAC
AAAR6KAAMAAAADuAAD
AAAR6KAAMAAAADuAAE
AAAR6KAAMAAAADuAAF

已选择 6 行。

6.minus方式对比备份表和源表，插回差异值

insert into jyc.test
(select * from jyc.test_bak where id in
(select id from jyc.test_bak
minus
select id from jyc.test);
commit;

7.对于lob字段检查是否存在空值

select count(*) from test where dbms_lob.GETLENGTH(lobname)=0;

相关参考：https://blog.csdn.net/sinat_36757755/article/details/128985053
https://sqlora.blog.csdn.net/article/details/118029262
http://blog.itpub.net/29578568/viewspace-2152697/