数据库BLOG类型字段坏块处理测试报告

最新推荐文章于 2022-05-08 10:36:44 发布
最新推荐文章于 2022-05-08 10:36:44 发布
阅读量199 收藏
点赞数
文章标签： 数据库

    xxxx数据库坏块测试报告

 

 

一、    测试背景

 

         xxxx数据库存在逻辑坏块，应用程序在执行SQL语句时报ORA-01578错误，导致SQL语句执行失败。搭建测试环境，测试几种坏块处理方法。

 

二、    测试目的

 

 

 

1、 测试dbms_repair包是否可以跳过坏块；

2、 测试10231事件是否可以跳过坏块；

3、 测试empty_blob()函数是否可以重置BLOG类型的字段置为空置，消除坏的记录。

 

 

三、    测试步骤

 

1、 在测试机器上安装oracle数据库；

2、 安装、配置NBU软件；

3、 RMAN异机恢复备份集；

4、 应用归档，启动数据库；

5、 测试dbms_repair包处理坏块；

6、 测试10231事件处理坏块；

7、 测试empty_blob()函数处理坏的记录。

 

 

四、    测试过程

      

方法一：

 

dbms_repair包处理坏块，以处理FMIS1600.XTDXDBGRIDFORMAT表为例，不能跳过坏块，测试过程如下：

 

1、创建REPAIR_TABLE2表，记录坏块信息

 

 begin

        dbms_repair.admin_tables (

        table_name => ’REPAIR_TABLE2’,

        table_type => dbms_repair.repair_table,

        action => dbms_repair.create_action,

        tablespace => ’SYSTEM’);

     end;

/

 

2、检测坏块,坏块信息存入REPAIR_TABLE2表

 

 declare

     rpr_count int;

     begin

         rpr_count := 0;

         dbms_repair.check_object (

         schema_name => ’ FMIS1600',

         object_name => ’ XTDXDBGRIDFORMAT',

         repair_table_name => ’REPAIR_TABLE’,

         corrupt_count => rpr_count);

         dbms_output.put_line(’repair count: ’ || to_char(rpr_count));

    end;

    /

 

3、查询坏块

 

set serveroutput on

select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description, repair_description from repair_table;

 

 

       

        

 

 

  

 

4、标记FMIS1600.XTDXDBGRIDFORMA表的坏块

 

declare

     fix_count int;

     begin

         fix_count := 0;

         dbms_repair.fix_corrupt_blocks (

         schema_name => ’ FMIS1600’,

         object_name => ‘XTDXDBGRIDFORMAT’,

         object_type => dbms_repair.table_object,

         repair_table_name => ’REPAIR_TABLE’,

         fix_count => fix_count);

         dbms_output.put_line(’fix count: ’ || to_char(fix_count));

      end;

     /

 

 

 

5、跳过所有被标记坏块的数据

 

  begin

         dbms_repair.skip_corrupt_blocks (

         schema_name => ’ FMIS1600’,

         object_name => ‘XTDXDBGRIDFORMAT’,S

         object_type => dbms_repair.table_object,

         flags => dbms_repair.skip_flag);

    end;

/

  

 

6、创建表FMIS1600.XTDXDBGRIDFORMAT_TMP失败

 

create table FMIS1600.XTDXDBGRIDFORMAT_TMP as select * from FMIS1600.XTDXDBGRIDFORMAT;

 

 

 

 

 

方法二：

 

10231事件处理坏块，以处理FMIS1600.XTDXDBGRIDFORMAT表为例，不能跳过坏块，测试过程如下：

 

1、在会话级开启10231事件

 

 alter system set events '10231 trace name context forever,level 10';

 

 

2、创建临时表FMIS1600.XTDXDBGRIDFORMAT_TMP失败

 

 create table FMIS1600.XTDXDBGRIDFORMAT_TMP as select * from FMIS1600.XTDXDBGRIDFORMAT;

 

 

 

 

方法三：

 

     empty_blob()函数处理损坏的记录，分别测试5张表，FMIS_LOB_ISOLATED、XTDXDBGRIDFORMAT、FMIS_LOB、XTYWDJDELETELOG、XTDXBLOB都可以成功处理损坏的记录。

 

使用empty_blob()函数处理XTDXDBGRIDFORMAT表中损坏的BLOG字段，将损坏的记字段值重置为空值，测试过程如下：

 

1、创建表corrupted_data

 

$ sqlplus /nolog

 

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 26 10:30:47 2012

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

SQL> connect /as sysdba;

Connected.

SQL> create table corrupted_data (corrupted_rowid rowid);

 

Table created.

 

2、查找损坏的记录rowid，放到corrupted_data表

 

SQL> set concat off

SQL> declare

  2  error_1578 exception;

  3  pragma exception_init(error_1578,-1578);

  4  n number;

  5  begin

  6  for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

  7  begin

  8  n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

  9  exception

 10  when error_1578 then

 11  insert into corrupted_data values (cursor_lob.r);

 12  commit;

 13  end;

 14  end loop;

 15  end;

 16  /

Enter value for lob_column:  FORMATINFO

Enter value for table_owner: FMIS1600

Enter value for table_with_lob: XTDXDBGRIDFORMAT

old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

new   6: for cursor_lob in (select rowid r,  FORMATINFO from FMIS1600.XTDXDBGRIDFORMAT) loop

old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

new   8: n:=dbms_lob.instr(cursor_lob. FORMATINFO,hextoraw('889911')) ;

PL/SQL procedure successfully completed.

 

3、检查坏的记录，并将坏BLOG字段的列设置为空值

 

SQL> SQL> SQL> SQL> select * from corrupted_data;

 

CORRUPTED_ROWID

------------------

AAAYQFAAIAAAat7AA9

AAAYQFAAIAAAat7ABG

AAAYQFAANAAAuGUAAH

AAAYQFAANAAAuGUAAK

AAAYQFAANAAAuGpAAL

AAAYQFAANAAAuGpAAM

AAAYQFAANAAAuGpAA2

 

7 rows selected.

 

SQL>

 

 

 

 

 

SQL> update &table_owner.&table_with_lob

  2          set &lob_column = empty_blob()

  3       where rowid in (select corrupted_rowid from corrupted_data);

Enter value for table_with_lob: XTDXDBGRIDFORMAT

old   1: update &table_owner.&table_with_lob

new   1: update FMIS1600.XTDXDBGRIDFORMAT

old   2:         set &lob_column = empty_blob()

new   2:         set  FORMATINFO = empty_blob()

 

7 rows updated.

 

4、创建临时表FMIS1600.XTDXDBGRIDFORMAT_TMP没有报错

 

SQL> create table FMIS1600.XTDXDBGRIDFORMAT_TMP as select * from FMIS1600.XTDXDBGRIDFORMAT;

 

Table created.

 

 

 

 

使用empty_blob()函数处理FMIS_LOB_ISOLATED表中损坏的BLOG字段，将损坏的记字段值重置为空值，测试过程如下：

 

1、创建corrupted_data1

 

SQL> create table corrupted_data1 (corrupted_rowid rowid);

 

Table created.

 

2、查找损坏的记录，放到corrupted_data1表

 

SQL> set concat off

SQL> declare

  2  error_1578 exception;

  3  pragma exception_init(error_1578,-1578);

  4  n number;

  5  begin

  6  for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

  7  begin

  8  n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

  9  exception

 10  when error_1578 then

 11  insert into corrupted_data1 values (cursor_lob.r);

 12  commit;

 13  end;

 14  end loop;

 15  end;

 16  /

Enter value for table_with_lob: FMIS_LOB_ISOLATED

old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

new   6: for cursor_lob in (select rowid r,  LOB_CONTENT from FMIS1600.FMIS_LOB_ISOLATED) loop

old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

new   8: n:=dbms_lob.instr(cursor_lob. LOB_CONTENT,hextoraw('889911')) ;

 

PL/SQL procedure successfully completed.

 

 

3、检查坏的记录，并将坏的列设置为空值

 

SQL> select * from  corrupted_data1;

 

CORRUPTED_ROWID

------------------

AAAD7RAAgAAA2SOAB0

AAAD7RAAgAAA2SOAB3

AAAD7RAAgAAA2SOAB4

AAAD7RAAgAAA2SOAB5

AAAD7RAAgAAA2SOAB6

AAAD7RAAgAAA2SOAB7

AAAD7RAAgAAA2SOAB8

AAAD7RAAgAAA2SOAB9

AAAD7RAAgAAA2SOAB+

AAAD7RAAgAAA2SOAB/

AAAD7RAAgAAA2SOACA

 

CORRUPTED_ROWID

------------------

AAAD7RAAgAAA2SOACB

AAAD7RAAgAAA2SOACC

AAAD7RAAgAAA2SOACD

AAAD7RAAgAAA2SOACE

AAAD7RAAgAAA2SOACF

AAAD7RAAgAAA2SOACG

AAAD7RAAgAAA2SOACH

AAAD7RAAgAAA2SOACI

AAAD7RAAgAAA2SOACJ

AAAD7RAAgAAA2SOACK

AAAD7RAAgAAA2SOACL

 

CORRUPTED_ROWID

------------------

AAAD7RAAgAAA2SPAAA

AAAD7RAAgAAA2SPAAB

AAAD7RAAgAAA2SPAAC

AAAD7RAAgAAA2SPAAD

AAAD7RAAgAAA2SPAAE

AAAD7RAAgAAA2SPAAF

AAAD7RAAgAAA2SPAAG

AAAD7RAAgAAA2SPAAH

AAAD7RAAgAAA2SPAAI

AAAD7RAAgAAA2SPAAJ

AAAD7RAAgAAA2SPAAK

 

CORRUPTED_ROWID

------------------

AAAD7RAAgAAA2SPAAL

 

34 rows selected.

 

SQL>

 

 

 

 

SQL> update &table_owner.&table_with_lob

  2          set &lob_column = empty_blob()

  3       where rowid in (select corrupted_rowid from corrupted_data1);

Enter value for table_with_lob: FMIS_LOB_ISOLATED

old   1: update &table_owner.&table_with_lob

new   1: update FMIS1600.FMIS_LOB_ISOLATED

old   2:         set &lob_column = empty_blob()

new   2:         set  LOB_CONTENT = empty_blob()

 

34 rows updated.

 

SQL> commit;

 

 

4、  创建临时表FMIS1600.FMIS_LOB_ISOLATED_TMP

 

SQL> create table FMIS1600.FMIS_LOB_ISOLATED_TMP as select  *  from FMIS1600.FMIS_LOB_ISOLATED;

 

 

Table created.

 

 

 

 

使用empty_blob()函数处理FMIS_LOB表中损坏的BLOG字段，将损坏的记字段值重置为空值，测试过程如下：

 

1、创建corrupted_data2

 

SQL> create table corrupted_data2 (corrupted_rowid rowid);

 

Table created.

 

2、查找损坏的记录，放到corrupted_data2表

 

SQL> set concat off

SQL> declare

  2  error_1578 exception;

  3  pragma exception_init(error_1578,-1578);

  4  n number;

  5  begin

  6  for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

  7  begin

  8  n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

  9  exception

 10  when error_1578 then

 11  insert into corrupted_data2 values (cursor_lob.r);

 12  commit;

 13  end;

 14  end loop;

 15  end;

 16  /

Enter value for table_with_lob: FMIS_LOB

old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

new   6: for cursor_lob in (select rowid r, LOB_CONTENT from FMIS1600.FMIS_LOB) loop

old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

new   8: n:=dbms_lob.instr(cursor_lob.LOB_CONTENT,hextoraw('889911')) ;

 

PL/SQL procedure successfully completed.

 

SQL>

SQL>

 

3、检查坏的记录，并将坏的列设置为空值

 

SQL> select * from corrupted_data2;

 

CORRUPTED_ROWID

------------------

AAAD7OAAhAAAIK2ABd

AAAD7OAAhAAAIK2ABe

AAAD7OAAhAAAIK2ABf

AAAD7OAAhAAAIK2ABg

AAAD7OAAsAAAgnnABB

AAAD7OAAsAAAgnnABC

AAAD7OAAsAAAgnnABD

AAAD7OAAsAAAgnnABE

AAAD7OAAsAAAgnnABF

AAAD7OAAsAAAgnnABG

AAAD7OAAsAAAgnnABH

 

CORRUPTED_ROWID

------------------

AAAD7OAAsAAAgnnABI

AAAD7OAAsAAAgnnABJ

AAAD7OAAsAAAgnnABK

 

14 rows selected.

 

SQL>

 

 

 

SQL>  update FMIS1600.FMIS_LOB

  2          set  LOB_CONTENT  = empty_blob()

  3       where rowid in (select corrupted_rowid from corrupted_data2);

 

14 rows updated.

 

SQL> commit;

 

Commit complete.

 

 

 

 

4、创建临时表FMIS1600.FMIS_LOB_TMP成功

 

SQL> create table FMIS1600.FMIS_LOB_TMP as select * from FMIS1600.FMIS_LOB;

Table created.

 

 

 

使用empty_blob()函数处理XTYWDJDELETELOG表中损坏的BLOG字段，将损坏的记字段值重置为空值，测试过程如下：

 

1、创建corrupted_data3

 

SQL> create table corrupted_data3 (corrupted_rowid rowid);

 

Table created.

 

2、查找损坏的记录，放到corrupted_data3表

 

SQL> declare

  2  error_1578 exception;

  3  pragma exception_init(error_1578,-1578);

  4  n number;

  5  begin

  6  for cursor_lob in (select rowid r, YWDJ from FMIS1600.XTYWDJDELETELOG )

  7  loop

  8  begin

  9  n:=dbms_lob.instr(cursor_lob.YWDJ,hextoraw('889911')) ;

 10  exception

 11  when error_1578 then

 12  insert into corrupted_data3  values (cursor_lob.r);

 13  commit;

 14  end;

 15  end loop;

 16  end;

 17  /

 

PL/SQL procedure successfully completed.

 

3、检查坏的记录，并将坏的列设置为空值

 

SQL> select * from corrupted_data3;

 

CORRUPTED_ROWID

------------------

AAAYU1AA2AAALQeABU

 

SQL>

 

 

 

SQL>  update FMIS1600.XTYWDJDELETELOG

  2          set YWDJ  = empty_blob()

  3       where rowid in (select corrupted_rowid from corrupted_data3);

 

1 row updated.

 

SQL> commit;

 

Commit complete.

 

 

 

4、创建临时表FMIS1600.XTYWDJDELETELOG_TMP成功

 

SQL> create table  FMIS1600.XTYWDJDELETELOG_TMP as select * from FMIS1600.XTYWDJDELETELOG;

 

Table created.

 

 

 

 

 

使用empty_blob()函数处理XTDXBLOB表中损坏的BLOG字段，将损坏的记字段值重置为空值，测试过程如下：

 

1、创建corrupted_data5

 

SQL> create table corrupted_data5 (corrupted_rowid rowid);

 

Table created.

 

 

2、查找损坏的记录，放到corrupted_data5表

SQL> set concat off

SQL> declare

  2  error_1578 exception;

  3  pragma exception_init(error_1578,-1578);

  4  n number;

  5  begin

  6  for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

  7  begin

  8  n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

  9  exception

 10  when error_1578 then

 11  insert into corrupted_data5  values (cursor_lob.r);

 12  commit;

 13  end;

 14  end loop;

 15  end;

 16  /

Enter value for lob_column: CONTENT

Enter value for table_owner: FMIS1600

Enter value for table_with_lob: XTDXBLOB

old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop

new   6: for cursor_lob in (select rowid r, CONTENT from FMIS1600.XTDXBLOB) loop

old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;

new   8: n:=dbms_lob.instr(cursor_lob.CONTENT,hextoraw('889911')) ;

 

PL/SQL procedure successfully completed.

 

SQL>

 

 

3、检查坏的记录，并将坏的列设置为空值

 

SQL> select * from corrupted_data5;

 

CORRUPTED_ROWID

------------------

AAZvjAAAOAAAcrXAAl

 

SQL>

 

 

 

SQL> update  FMIS1600.XTDXBLOB

  2          set   CONTENT  = empty_blob()

  3       where rowid in (select corrupted_rowid from corrupted_data5);

 

1 row updated.

 

SQL> commit;

 

Commit complete.

 

4、创建临时表FMIS1600.XTDXBLOB_TMP成功

 

SQL> create table FMIS1600.XTDXBLOB_TMP as select * from FMIS1600.XTDXBLOB;

 

Table created.

 

SQL>

SQL>

 

 

 

 

 

 

 

五、    测试总结

 

 

通过以上测试，dbms_repair包和10231事件并不能跳过坏块，empty_blob()函数可以重置BLOG类型的字段置为空置，消除坏的记录。

 

 

 

 

 

 

附录一、 empty_blob()函数处理5张表损坏记录操作步骤和命令

 

1、创建表corrupted_data      （1分钟）

 

create table corrupted_data (corrupted_rowid rowid);

 

2、查找损坏的记录，放到corrupted_data表 (20分钟）

 

 set concat off                                                                           

 declare                                                                                  

 error_1578 exception;                                                                     

 pragma exception_init(error_1578,-1578);                                                 

 n number;                                                                                

 begin                                                                                     

 for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop 

 begin                                                                                    

 n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;                          

 exception                                                                                

 when error_1578 then                                                                     

 insert into corrupted_data values (cursor_lob.r);                                        

 commit;                                                                                  

 end;                                                                                      

 end loop;                                                                                

 end;                                                                                     

 /   

                                                                                     

Enter value for lob_column:  FORMATINFO                                                   

Enter value for table_owner: FMIS1600                                                     

Enter value for table_with_lob: XTDXDBGRIDFORMAT

                                         

3、检查坏的记录，并将坏的列设置为空值（1分钟）

 

select * from corrupted_data;

 

update &table_owner.&table_with_lob                          

        set &lob_column = empty_blob()                        

 where rowid in (select corrupted_rowid from corrupted_data); 

 

Enter value for table_with_lob: XTDXDBGRIDFORMAT              

old   1: update &table_owner.&table_with_lob                  

new   1: update FMIS1600.XTDXDBGRIDFORMAT                     

old   2:         set &lob_column = empty_blob()               

new   2:         set  FORMATINFO = empty_blob()  

 

commit;            

 

4、创建临时表（20分钟） 

                                                                                                        

create table FMIS1600.XTDXDBGRIDFORMAT_TMP as select * from FMIS1600.XTDXDBGRIDFORMAT;

 

5、检查临时表和原表，确认临时表创建成功后才可以删除原表（10分钟）

 

   select count(*)　from FMIS1600.XTDXDBGRIDFORMAT_TMP;

   select count(*)  from FMIS1600.XTDXDBGRIDFORMAT;

 

6、删除原表并重命名临时表为原表名（2分钟）

 

drop table FMIS1600.XTDXDBGRIDFORMAT;

alter table FMIS1600.XTDXDBGRIDFORMAT_TMP rename to XTDXDBGRIDFORMAT;

 

7、重建主建、索引、外建

 

 

 

 

-------------FMIS1600.FMIS_LOB_ISOLATED---------

 

 

1、创建corrupted_data1  1分钟

 

 create table corrupted_data1 (corrupted_rowid rowid);

 

2、查找损坏的记录，放放到corrupted_data1表 (10分钟）

 

 set concat off                                                                                   

 declare                                                                                          

 error_1578 exception;                                                                            

 pragma exception_init(error_1578,-1578);                                                         

 n number;                                                                                         

 begin                                                                                            

 for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop         

 begin                                                                                             

 n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;                                  

 exception                                                                                         

 when error_1578 then                                                                             

 insert into corrupted_data1 values (cursor_lob.r);                                               

 commit;                                                                                           

 end;                                                                                             

 end loop;                                                                                        

 end;                                                                                             

 /     

                                                                                          

Enter value for table_with_lob: FMIS_LOB_ISOLATED                                                  

old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop 

new   6: for cursor_lob in (select rowid r,  LOB_CONTENT from FMIS1600.FMIS_LOB_ISOLATED) loop    

old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;                          

new   8: n:=dbms_lob.instr(cursor_lob. LOB_CONTENT,hextoraw('889911')) ;                          

                                                                                                   

PL/SQL procedure successfully completed.                                                          

                                                                                                  

3、检查坏的记录，并将坏的列设置为空值（1分钟）

 

   select * from  corrupted_data1;

 

   update &table_owner.&table_with_lob                                  

   set &lob_column = empty_blob()                                

   where rowid in (select corrupted_rowid from corrupted_data1);

 

Enter value for table_with_lob: FMIS_LOB_ISOLATED                     

old   1: update &table_owner.&table_with_lob                          

new   1: update FMIS1600.FMIS_LOB_ISOLATED                            

old   2:         set &lob_column = empty_blob()                       

new   2:         set  LOB_CONTENT = empty_blob()                      

                                                                      

34 rows updated.                                                      

                                                                       

   commit;                                                              

 

 

4、创建临时表（20分钟） 

 

 create table FMIS1600.FMIS_LOB_ISOLATED_TMP as select  *  from FMIS1600.FMIS_LOB_ISOLATED;

 

 

5、检查临时表和原表，确认临时表创建成功后才可以删除原表（10分钟）

 

   select count(*) from FMIS1600.FMIS_LOB_ISOLATED_TMP;

   select count(*) from FMIS1600.FMIS_LOB_ISOLATED;

 

 

6、删除原表并重命名临时表为原表名（2分钟）

 

   drop table FMIS1600.FMIS_LOB_ISOLATED;

   alter table FMIS1600.FMIS_LOB_ISOLATED_TMP rename to FMIS_LOB_ISOLATED;

 

 

 

7、重建主建、索引、外建

 

 

------------------FMIS1600.FMIS_LOB----------

 

1、创建corrupted_data2  1分钟

 

  create table corrupted_data2 (corrupted_rowid rowid);

 

2、查找损坏的记录，放到corrupted_data2表 (10分钟）

  

 

set concat off                                                                                    

declare                                                                                           

error_1578 exception;                                                                              

pragma exception_init(error_1578,-1578);                                                          

n number;                                                                                         

begin                                                                                             

for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop          

begin                                                                                             

n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;                                   

exception                                                                                          

when error_1578 then                                                                              

insert into corrupted_data2 values (cursor_lob.r);                                                

commit;                                                                                            

end;                                                                                              

end loop;                                                                                          

end;                                                                                              

/ 

 

  

                                                                                            

Enter value for table_with_lob: FMIS_LOB                                                          

old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop 

new   6: for cursor_lob in (select rowid r, LOB_CONTENT from FMIS1600.FMIS_LOB) loop              

old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;                          

new   8: n:=dbms_lob.instr(cursor_lob.LOB_CONTENT,hextoraw('889911')) ;                           

                                                                                                   

PL/SQL procedure successfully completed.                                                          

                                                                                                   

3、检查坏的记录，并将坏的列设置为空值（1分钟）

 

 select * from corrupted_data2;

 

update FMIS1600.FMIS_LOB                                      

set  LOB_CONTENT  = empty_blob()                              

where rowid in (select corrupted_rowid from corrupted_data2);

 

commit; 

 

4、创建临时表（20分钟）

 

  create table FMIS1600.FMIS_LOB_TMP as select * from FMIS1600.FMIS_LOB;

 

5、检查临时表和原表，确认临时表创建成功后才可以删除原表（10分钟）

 

   select count(*) from FMIS1600.FMIS_LOB_TMP;

   select count(*) from FMIS1600.FMIS_LOB;

 

 

6、删除原表并重命名临时表为原表名（2分钟）

 

   drop table FMIS1600.FMIS_LOB;

   alter table FMIS1600.FMIS_LOB_TMP  rename to FMIS_LOB;

 

 

7、重建主建、索引、外建

 

 

 

------------------FMIS1600.XTYWDJDELETELOG--------------

 

1、创建corrupted_data3  1分钟

 

   create table corrupted_data3 (corrupted_rowid rowid);

 

2、查找损坏的记录，放到corrupted_data3表 (10分钟）

 

  set concat off 

  declare                                                                   

  error_1578 exception;                                                     

  pragma exception_init(error_1578,-1578);                                   

  n number;                                                                 

  begin                                                                     

  for cursor_lob in (select rowid r, YWDJ from FMIS1600.XTYWDJDELETELOG )   

  loop                                                                      

  begin                                                                     

  n:=dbms_lob.instr(cursor_lob.YWDJ,hextoraw('889911')) ;                   

  exception                                                                 

  when error_1578 then                                                      

  insert into corrupted_data3  values (cursor_lob.r);                       

  commit;                                                                    

  end;                                                                      

  end loop;                                                                 

  end;                                                                       

  /  

                                                                      

                                                                            

  PL/SQL procedure successfully completed.                                    

 

3、检查坏的记录，并将坏的列设置为空值（1分钟）

 

 select * from corrupted_data3;

 

  update FMIS1600.XTYWDJDELETELOG                               

  set YWDJ  = empty_blob()                                      

  where rowid in (select corrupted_rowid from corrupted_data3); 

 

  commit;

 

4、创建临时表（20分钟）

 

  create table  FMIS1600.XTYWDJDELETELOG_TMP as select * from FMIS1600.XTYWDJDELETELOG;

 

5、检查临时表和原表，确认临时表创建成功后才可以删除原表（10分钟）

 

   select count(*) from FMIS1600.XTYWDJDELETELOG_TMP;

   select count(*) from FMIS1600.XTYWDJDELETELOG;

 

6、删除原表并重命名临时表为原表名（2分钟）

 

 drop table FMIS1600.XTYWDJDELETELOG;

 alter table FMIS1600.XTYWDJDELETELOG_TMP rename to XTYWDJDELETELOG;

 

7、重建主建、索引、外建

 

 

 

---------------FMIS1600.XTDXBLOB---------

 

1、创建corrupted_data5  1分钟

  

   create table corrupted_data5 (corrupted_rowid rowid);

 

2、查找损坏的记录，放到corrupted_data5表 (10分钟）

 

  set concat off                                                                                        

  declare                                                                                               

  error_1578 exception;                                                                                 

  pragma exception_init(error_1578,-1578);                                                               

  n number;                                                                                             

  begin                                                                                                  

  for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop              

  begin                                                                                                 

  n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;                                       

  exception                                                                                             

  when error_1578 then                                                                                   

  insert into corrupted_data5  values (cursor_lob.r);                                                   

  commit;                                                                                               

  end;                                                                                                   

  end loop;                                                                                             

  end;                                                                                                   

  /    

                                                                                                

 Enter value for lob_column: CONTENT                                                                     

 Enter value for table_owner: FMIS1600                                                                  

 Enter value for table_with_lob: XTDXBLOB                                                               

 old   6: for cursor_lob in (select rowid r, &&lob_column from &&table_owner.&table_with_lob) loop      

 new   6: for cursor_lob in (select rowid r, CONTENT from FMIS1600.XTDXBLOB) loop                       

 old   8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;                               

 new   8: n:=dbms_lob.instr(cursor_lob.CONTENT,hextoraw('889911')) ;                                    

 

3、检查坏的记录，并将坏的列设置为空值（1分钟）

 

 select * from corrupted_data5;

 

update  FMIS1600.XTDXBLOB                                     

set   CONTENT  = empty_blob()                                 

where rowid in (select corrupted_rowid from corrupted_data5); 

 

commit;

 

4、创建临时表（20分钟）

 

   create table FMIS1600.XTDXBLOB_TMP as select * from FMIS1600.XTDXBLOB;

 

5、检查临时表和原表，确认临时表创建成功后才可以删除原表（10分钟）

 

   select count(*) from FMIS1600.XTDXBLOB_TMP;

   select count(*) from FMIS1600.XTDXBLOB;

 

6、删除原表并重命名临时表为原表名（2分钟）

 

  drop table FMIS1600.XTDXBLOB;

  alter table FMIS1600.XTDXBLOB_TMP rename to XTDXBLOB;

 

7、重建主建、索引、外建

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27571661/viewspace-751939/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27571661/viewspace-751939/

ckhwddy577380
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
对象序列化写入到blog大值字段
04-19
对象序列化实例:包含object 写入的数据库blog字段、 和从blog字段读取对象
关于一次blog性能测试的过程和疑问
最新发布
weixin_55282974的博客
09-08 286
找了搭建的一个博客网站，拿来试着测了测，想看一下性能能达到什么目标；
【Oracle】迁移含有BLOG类型的字段的表
ajsyipsc40270的博客
10-12 186
日常数据库迁移过程中，发现dmp文件中的表，如果表含有blog类型的字段，无法进行迁移。以下是我迁移含有blog类型字段表的经验分享‘使用PLSQL Developer进行导出接着选择需要导出的表点击【Export】即可导出到a.sql文件中。导出后，再打开Command Window输入@后回车选择a.sql即可完成导入。 转载于:https://www.cnblogs.com/Ol...
oracle必须读取一个,读取oracle中blog字段
weixin_33225892的博客
04-09 402
读取oracle中blog字段Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();String URL=dbmes.url;Connection con = DriverManager.getConnection(URL,dbmes.usrname,dbmes.pwd);try{// 准备语句执行对象String bh=re...
springboot处理blog字段
weixin_44846436的博客
05-08 815
springboot处理blog字段 欢迎关注博主公众号「Java大师」, 专注于分享Java领域干货文章https://www.javaman.cn/ 1、数据库表结构 其中content为longblob字段，代表存入的内容 CREATE TABLE `t_post` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `channel_id` int(11) DEFAULT NULL, `created` datetime DEFAULT NULL
易语言数据库操作之“取字段类型”命令介绍
08-19
总的来说，“取字段类型”命令是易语言数据库操作中的一个重要工具，它使得开发者能够方便地获取和处理数据库中的数据类型信息。结合其他数据库操作命令，开发者可以构建出高效且灵活的数据管理程序。希望这篇文章能...
mysql数据库全部字段类型
03-02
BIGINT 大整数，带符号的范围是-...BIT 位字段类型，范围为从1到64，默认为1 BLOB 最大长度为65,535(216–1)字节的BLOB列 BOOL 是TINYINT(1)的同义词。zero值被视为假。非zero值视为真 CHAR 固定长度字符串
详解Oracle修改字段类型方法总结
12-16
在Oracle数据库管理中，修改表结构是常见的任务之一，其中包括改变字段的数据类型。本文主要针对Oracle修改字段类型的方法进行详细讲解，特别是在面对不同情况时如何有效地进行操作。 首先，我们来看一个具体的例子...
Java实体类字段生成工具类-将数据库表列字段转为Java实体类驼峰字段
05-29
1、在Java开发中，常常需要将数据库表列字段换成Java实体类字段。但是手动实现这个转换过程比较慢，且容易出错，影响开发效率。为了解决这个问题，开发了这个Java实体类字段生成工具类。 2、该工具类可以将数据库表...
数据库自增字段是字符型的简单处理
01-21
因为在数据库端实现自增，不能在软件端处理了。在不考虑 　几个函数的解释： 　Right(str，len)-字段返回右边的len个字符的字符串str 　MAX()-字段中大的值 　IsNull() 如为空为空 　思路： 　首先这是个...
Spring+Hibernate中处理Oracle的BLOG和CLOG字段
曹世超的专栏
03-19 2329
对于BLOG和CLOB字段，大部分数据库提供了较为透明的支持，但Oracle比较特殊一些，在用Spring和Hibernate操作时，需要作一些特殊的处理，以CLOB为例，基本有下面几个步骤：１、在Spring的配置文件中加入以下几个Bean的定义 ２、在sessionFactory这个Bean中加入一个属性的引用３、在需要clob的JavaBean对应的hbm.xml中，改对
转载：CLOB，BLOG大字段加载
he_zhengxing的博客
07-26 201
Oracle中的varchar2对象最多只能存4000个字节，对于要进行大批量字符对象的存储来说显然是远远不够的，这时就需要用到CLOB。 Spring的HibernateTemplate的既有方法完全兼容对CLOB对象的操作，仅仅需要进行简单的配置： 1.保留实体中需要保存为CLOB对象的属性为String，而在hbm映射文件中将其修改为org.springframework.orm...
下载图片(数据库BLOG字段)
Shaniya
04-21 1693
package com.lc.dcwork.edoc.handSignPict.cmd; import java.awt.image.BufferedImage; import java.io.ByteArrayInputStream; import java.io.IOException; import java.io.InputStream; import javax.imageio.Im
django-blog（四）自定义公共字段
dyeee的专栏
11-02 226
vim blogproject/settings.py   SITE_NAME='测试标题' SITE_DESC='测试测试测试按时打卡机安检时刻'   [root@localhost blogproject]# cat blog/views.py from django.shortcuts import render import logging from django.con...
BCB操作BLOG字段的方法
Johnny的专栏
03-27 1201
/** * 把图像保存到SQL Server数据库中 */  OpenDialog1->FileName="";    if (OpenDialog1->Execute()){    Edit1->Text=  OpenDialog1->FileName;    Image1->Picture->LoadFromFile(Edit1->Text);}if (!Edit1->Text.IsEmpty
如何读写Oracle数据库Blog字段(Java)
爱生活·爱Java
03-01 1575
数据库表结构CREATE TABLE LM_DATA_KEY(  KEY  BLOB,  ID   NUMBER) 1. 如何写入KEY字段 ://Remove old record with id=1PreparedStatement pstmt = conn.prepareStatement("delete from lm_data_key where id=1");pstmt
字段处理
weixin_44542048的博客
06-07 182
一、如果页面有下拉框而且数据是固定的少数的几个值的时候就不需要再去创建子表，然后再连表查询的操作步骤了。 像这种下拉框，我们在数据库设计的时候不需要建子表再用外键去连接，因为在这个项目里面就学生、教师、管理这三个身份，可以直接在页面下拉框进行绑定，给value设定固定的值，当下拉框被选中的时候就会返回绑定的值，再判断返回的值是哪个，将对应的身份填到数据库里面。 在表格添加自定义列{ title...
oracle 清理lob set (lob字段)='n',Oracle数据库出现ORA-19566 LOB坏块的处理记录
weixin_39842744的博客
04-05 179
1.故障现象：在晚上的生产库自动备份时，备份失败，出现以下错误提示：RMAN-03009: failure of backup command on c1 channel at 06/11/2020 03:31:02ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/orcl/datafile/data12.dbfcontinu...
oracle 验证 lob 坏块,Oracle数据库出现ORA-19566 LOB坏块的处理记录
weixin_36269334的博客
04-12 459
1.故障现象：在晚上的生产库自动备份时，备份失败，出现以下错误提示：RMAN-03009: fAIlure of backup command on c1 channel at 06/11/2020 03:31:02ORA-19566: exceeded limIT of 0 corrupt blocks for file +DATA/orcl/datafile/datA12.dbfcontinu...
Oracle数据库字段类型全面解析
"Oracle数据库字段类型的详细解析，涵盖了字符串类型、数字类型、日期时间类型、大型对象类型、RAW和LONGRAW类型以及ROWID和UROWID类型。" Oracle数据库是世界上最广泛使用的数据库管理系统之一，其丰富的数据类型...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值