ORA-01114: IO error writing block to file name block

Environment: Oracle Database 12.1.0.1.0 OS: AIX 6.1 The following error was reported in the alert log of one of my databases when a user tried to run a select statement. ORA-01114: IO error writing block to file (block # ) Fri May 15 10:22:00 2015 Errors in file /ORACLE_HOME/app/oracle/diag/rdbms/xxxxxx/XXXXXX/trace/XXXXXX_m001_43188288.trc: ORA-01114: IO error writing block to file (block # ) "oerr ora 01114" shows the following: 01114, 00000, "IO error writing block to file %s (block # %s)" // *Cause: The device on which the file resides is probably offline. If the // file is a temporary file, then it is also possible that the device // has run out of space. This could happen because disk space of // temporary files is not necessarily allocated at file creation time. // *Action: Restore access to the device or remove unnecessary files to free // up space. I looked at the file systems and found one which is 100% used. Checking further revealed that the file system held data files and the database temp file. The datafiles were configured with AUTOEXTENSIBLE OFF and checks on tablespace usage revealed that all tablespaces were below 75% usage threshold. However, since the user was trying to run a select statement which will involve sorting, it became obvious that this has to do with the temporary tablespace. In my case I had another filesystem that had enough free space so I decided to migrate my default temporary tablespace to a new location. Oracle 12c has a new feature where you can migrate datafiles online, however this beautiful feature does not work for temp files! STEPS TO CREATE A NEW TEMPORARY TABLESPACE. ============================================== STEP 1. Create a new temporary tablespace (e.g. TEMP2). SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u03/XXXXXX/oradata/XXXXXX/temp/temp01.dbf' size 30g autoextend off; STEP 2. Change default temporary tablespace (assume it was TEMP)to the newly created one (TEMP2). SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; STEP 3. Check to see that no user sessions are using the original temporary tablespace. If there are any, identify and kill the sessions ONLY if it will not impact production activities. i. Get session number from the V$SORT_USAGE view : SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; ii. Get corresponding session ID from the V$SESSION view: This can be done using SESSION_NUM or SESSION_ADDR from the results gotten from the above query. SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM; SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; iii. Kill identified Session(s) with IMMEDIATE option: SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE; STEP 4. Drop original temporary tablespace (TEMP in this case) SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; STEP 5. Recreate a new/larger temporary tablespace (named TEMP) SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u03/XXXXXX/oradata/XXXXXX/temp/temp001.dbf' size 30g autoextend off; STEP 6. Move default temporary tablespace back to TEMP. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; STEP 7. Drop TEMP2 temporary tablespace. NB: Dropping TEMP2 tablespace may hand due to usage by a session, so you must check and kill any sessions currently using the temporary tablespace we are about to drop like was done in step 3 above. SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; USERNAME SESSION_NUM SESSION_ADDR ------------------------------ ----------- ---------------- SYS 17639 070001004FE710B0 SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=17639; SID SERIAL# STATUS ---------- ---------- -------- 367 17639 ACTIVE SQL> alter system kill session '367,17639' immediate; System altered. SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; no rows selected SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES; CONCLUSION: The steps stated above helped in resolving the issue and gave joy to the customer!