ERROR

ORA-01157: cannot identify/lock data file 206 – see DBWR trace file
ORA-01110: data file 206: ‘+DATA’

SOLUTION

If you are using XML in your database and you are trying to update any XML data, then above error may come.

Step 1: Add below line in your parameter file and bounce the database if not there.

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)’

Step 2: Unlock the XDB user if locked.

SQL> alter user XDB account unlock;

Step 3:  If you have created a duplicate database or open standby database read/write mode, then source TEMP tablespaces will no more valid. drop your TEMP tablespace and create new TEMP tablespace and make it default.

SQL> DROP TABLESPACE TEMP  INCLUDING CONTENTS AND DATAFILES;

SQL> create temporary tablespace TEMP02 tempfile ‘/u02/apps/ORCL/temp_02.dbf’ size 1024M autoextend on; 

SQL> alter database default temporary tablespace TEMP02;

Bounce the database. Now sure the above error will not come.

How to Find and repair Corrupt block in database

Step 1: Below query will show if there is any corrupted block

SELECT * 
FROM   v$database_block_corruption — will show if any corruped block 

Step 2: Below query can give you Detail information about corrupted block:

set head ON; 
set pagesize 2000 
set linesize 250 
SELECT * 
FROM   v$database_block_corruption; 

SELECT e.owner, 
       e.segment_type, 
       e.segment_name, 
       e.partition_name, 
       c.file#, 
       Greatest(e.block_id, c.block#) 
       corr_start_block#, 
       Least(e.block_id + e.blocks – 1, c.block# + c.blocks – 1) corr_end_block# 
       , 
       Least(e.block_id + e.blocks – 1, c.block# + c.blocks – 1) – 
       Greatest(e.block_id, c.block#) + 1 
       blocks_corrupted, 
       NULL                                                      description 
FROM   dba_extents e, 
       v$database_block_corruption c 
WHERE  e.file_id = c.file# 
       AND e.block_id <= c.block# + c.blocks – 1 
       AND e.block_id + e.blocks – 1 >= c.block# 
UNION 
SELECT s.owner, 
       s.segment_type, 
       s.segment_name, 
       s.partition_name, 
       c.file#, 
       header_block     corr_start_block#, 
       header_block     corr_end_block#, 
       1                blocks_corrupted, 
       ‘Segment Header’ description 
FROM   dba_segments s, 
       v$database_block_corruption c 
WHERE  s.header_file = c.file# 
       AND s.header_block BETWEEN c.block# AND c.block# + c.blocks – 1 
UNION 
SELECT NULL                                                      owner, 
       NULL                                                      segment_type, 
       NULL                                                      segment_name, 
       NULL                                                      partition_name, 
       c.file#, 
       Greatest(f.block_id, c.block#) 
       corr_start_block#, 
       Least(f.block_id + f.blocks – 1, c.block# + c.blocks – 1) corr_end_block# 
       , 
       Least(f.block_id + f.blocks – 1, c.block# + c.blocks – 1) – 
       Greatest(f.block_id, c.block#) + 1 
       blocks_corrupted, 
       ‘Free Block’                                              description 
FROM   dba_free_space f, 
       v$database_block_corruption c 
WHERE  f.file_id = c.file# 
       AND f.block_id <= c.block# + c.blocks – 1 
       AND f.block_id + f.blocks – 1 >= c.block# 
ORDER  BY file#, 
          corr_start_block#; 

Step 4:  Collect file ids

SELECT DISTINCT file_id 
FROM   dba_extents; 

Step 5: Collect details 

SELECT file_id, 
       segment_name, 
       segment_type, 
       owner, 
       tablespace_name, 
       block_id, 
       blocks 
FROM   sys.dba_extents 
WHERE  ( file_id BETWEEN 2 AND 19 ) 
       AND 468598 BETWEEN block_id AND block_id + blocks – 1;

Step 6:  Repair 

a) Collect all data to temporary table and collect all DDL script and grants.

b) drop the table and re-create it with DDL script. (Disable refence key before drop, enable after create table)

c) Insert all records to the table

Note: This entire activity should not be taken in prod databases without Oracle support’s recommendation.

ERROR

Solution : ORA-01442: column to be modified to NOT NULL is already NOT NULL

sql> alter table HR.employees modify UID not null;

ORA-01442: column to be modified to NOT NULL is already NOT NULL

SOLUTION

ALTER TABLE table_name 
  ADD CONSTRAINT constraint_name CHECK(column_name IS NOT NULL); 

OR

ALTER TABLE hr.employees 
  ADD CONSTRAINT nn_uid_hr_addrsdtls CHECK(UID IS NOT NULL);