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.