create index ORA-00376 處理方法

臨時段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.