Useful shortcuts for vi editor

Wednesday 16 November 2016

ORA-01578 solution (if object is INDEX)

Error
ORA-01578: ORACLE data block corrupted (file # 100, block # 50000)

Solution
Find the object that was corrupted:
SQL> 
SELECT SEGMENT_TYPE, OWNER || '.' || SEGMENT_NAME
  FROM DBA_EXTENTS
 WHERE FILE_ID = 100 AND 50000 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

Then re-create index or indexes:
SQL> drop index alper_index_pk;

SQL> 
CREATE UNIQUE INDEX HR.ALPER_INDEX_PK ON HR.EMPLOYEES
(EMPLOYEE_ID)
LOGGING
TABLESPACE INDEX_TS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;