ORA-01114: IO error writing block to file 202 (block # 4445)

Getting error while creating the and index

ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
ORA-01114: IO error writing block to file 202 (block # 4445)
ORA-27063: number of bytes read/written is incorrect
Additional information: 245760
Additional information: 253952
Process ID: 15076
Session ID: 492 Serial number: 1471

The most probable reason for this error under my circumstances is that, create index command use tamp tablespace and one of the datafile of temp tablespace is not able to extend itself because the disk have no space left. 

In case your TEMP tablespace have two tempfile temp01.dbf and temp02.dbf, first file is on the disk with less space than the maxsize of the file and second file on the disk with more space then the maxsize.

Now you are creating an index and it is using temp01.dbf to as tempspace then once the file have filled the db disk then it will give above error, but it will not start using temp02.dbf.

Incase you query have started using temp02.dbf, because this file can grow and it have enough space on dbdisk and once this file is completely used then it will start using temp01.dbf until it have filled the db disk and hit the error.

ORA-00060: Deadlock detected

Deadlock explained

Deadlock is a state in database when no further action can be done and sessions are in a state that they can not be committed, below is the example of how database deadlock occurs:

SQL> select c from t;
C
--
X
Y

Description of ORA-00060 is:

$ oerr ORA 00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause:  Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
//          involved. Retry if necessary.
$ 

This is just an example of how deadlock can occur. It can occur using any other DML operation as well. Deadlock is not an oracle database issue but this is a feature so that sessions do not struct in limbo.

In the above example I am not committing any where. Some explanation for the above scenario:

• SNO 1, session 13 I am updating a table T and not committing.
• SNO 2, session 401 updating another row in the same table with something else.
• Both of the above commands will finish and there will be no issue.
• SNO 3, session 401 trying to update what was updated in SNO 1, the transaction in this session will not finish but wait for session 13 to commit the transaction. Which means session 401 will not receive SQL prompt to run anything else. This means session 401 is waiting for session 13 and there is a lock by session 13 on the row.
• SNO 4, session 13 update the row which was updated by session 2 in SNO 3. Which means another waiting event is created and now session 13 is waiting for session 401 to commit. But as said in previous point session 401 can not commit because it can not get the SQL prompt until session 13 from commits transaction in SNO 1 and now session 13 can not commit transaction in SNO 1 because it is not getting the SQL prompt. This situation of waiting for each other to commit is called deadlock.
• SNO 5, an error occurred in session 401 for the transaction which caused the database lock and eventually became deadlock. At this point a trace file is created in alert log which have more information.

*** 2011-08-20 09:33:48.253
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006000d-0000a3d0        27     401     X             26      13           X
TX-0001000c-00007adc        26      13     X             27     401           X

session 401: DID 0001-001B-00000005     session 13: DID 0001-001A-00000005
session 13: DID 0001-001A-00000005      session 401: DID 0001-001B-00000005

Rows waited on:
  Session 401: obj - rowid = 00011F19 - AAAR8ZAAFAADZkeAAA
  (dictionary objn - 73497, file - 5, block - 891166, slot - 0)
  Session 13: obj - rowid = 00011F19 - AAAR8ZAAFAADZkeAAB
  (dictionary objn - 73497, file - 5, block - 891166, slot - 1)

----- Information for the OTHER waiting sessions -----
Session 13:
  sid: 13 ser: 11 audsid: 511371 user: 85/HDHILLON flags: 0x45
  pid: 26 O/S info: user: oracle, term: UNKNOWN, ospid: 4106
    image: oracle@hdhillon-dt2 (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/2, ospid: 4057
    machine: hdhillon-dt2 program: sqlplus@hdhillon-dt2 (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update t set c='E' where c='Y'

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=13f3ds0x0bq54) -----
update t set c='D' where c='X'
===================================================