ITPub博客

首页 > 数据库 > Oracle > create index ORA-00376 处理方法

create index ORA-00376 处理方法

原创 Oracle 作者:darren__chan 时间:2020-07-14 15:03:51 0 删除 编辑

临时段reuse引起的异常,小记!


SQL> create index cwdtest.testidx1 on cwdtest.tab_level_2(FID) tablespace testidx;

create index cwdtest.testidx1 on cwdtest.tab_level_2(FID) tablespace testidx

                                         *

ERROR at line 1:

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/testidx01.dbf'

 

 

alter system set db_block_checksum=false;

 

 

System altered.

 

select 'exec dbms_space_admin.segment_corrupt('''||tablespace_name||''','||relative_fno||','||HEADER_BLOCK||')'

  2      from dba_segments where SEGMENT_TYPE='TEMPORARY'and TABLESPACE_NAME = 'TESTIDX';

 

 

'EXECDBMS_SPACE_ADMIN.SEGMENT_CORRUPT('''||TABLESPACE_NAME||''','||RELATIVE_FNO|

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

exec dbms_space_admin.segment_corrupt('TESTIDX',3,130)

 

SQL> SQL> exec dbms_space_admin.segment_corrupt('TESTIDX',3,130);

 

PL/SQL procedure successfully completed.

 

select 'exec dbms_space_admin.segment_drop_corrupt('''||tablespace_name||''','||relative_fno||','||HEADER_BLOCK||')'

  2      from dba_segments where SEGMENT_TYPE='TEMPORARY'and TABLESPACE_NAME = 'TESTIDX';

 

'EXECDBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT('''||TABLESPACE_NAME||''','||RELATIVE

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

exec dbms_space_admin.segment_drop_corrupt('TESTIDX',3,130)

 

SQL> exec dbms_space_admin.segment_drop_corrupt('TESTIDX',3,130);

 

PL/SQL procedure successfully completed.

 

select owner, segment_name, tablespace_name, relative_fno, HEADER_BLOCK

from dba_segments

where SEGMENT_TYPE='TEMPORARY'

  4  and TABLESPACE_NAME = 'TESTIDX';

 

no rows selected

 

SQL> exec SYS.DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('TESTIDX');

BEGIN SYS.DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('TESTIDX'); END;

 

*

ERROR at line 1:

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/testidx01.dbf'

ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 120

ORA-06512: at line 1

 

 

 

SQL> SHOW PARAMETER db_block_checksum

 

NAME                                                   TYPE VALUE

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

db_block_checksum                          string              FALSE

SQL>  alter system set db_block_checksum=true;

 

System altered.

 

SELECT distinct(segment_name), owner, segment_type, partition_name FROM dba_extents

  2  WHERE  tablespace_name = 'TESTIDX';

 

no rows selected

 

 

SQL> create index cwdtest.testidx1 on cwdtest.tab_level_2(FID) tablespace testidx;

 

Index created.


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

请登录后发表评论 登录
全部评论
一思尚存,此志不懈

注册时间:2014-09-09

  • 博文量
    190
  • 访问量
    1180866