Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

ORA-01114: IO error writing block to file %s (block # %s)

501173
501173 Member Posts: 95
Hi,

I tried to perform a complex select and a complex insert statement on Oracle9i Enterprise Edition Release 9.2.0.4.0 on my linux. The following error messages were displayed:

select statement: ORA-01114: IO error writing block to file %s (block # %s)
insert statement: ORA-00603: ORACLE server session terminated by fatal error

I did researches on the internet and found one of the causes (database's temp file space has filled up etc.), but I am not sure what to do.

I think that I found the temp file in the database directory: temp01.dbf (11.039.416.320). Is it the temp file mentioned? How can I truncate it? or what should I do with this file? What does this temp file do?

Another file is undotbs01.dbf (2.422.218.752). This is a big file. Should I fix this file?

Could someone please help me to solve this problem?
Thanks
Tagged:
«1

Comments

  • 249425
    249425 Member Posts: 1,482
    Please post the full error from the statment and from alert.log

    Best Regards
    Krystian Zieja / mob
  • 501173
    501173 Member Posts: 95
    Thanks for your response.
    There were no other messages.

    Can you tell me where is the alert.log located?
  • Daljit
    Daljit Member Posts: 2,186
    login to oracle using sqlplus and issue:

    show parameter background_dump

    The alert.log will be under this location.

    Daljit Singh
  • 501173
    501173 Member Posts: 95
    after entering the command in sqlplus: show parameter background_dump
    I got the message:
    table or view not exist
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    You need to connect as system or sys user.

    Nicolas.
  • 501173
    501173 Member Posts: 95
    Error message in the alert.log:

    ORA-27072: skgfdisp: I/O error
    Linux Error: 28: No space left on device
    Additional information: 1347364
    ORA-01114: IO error writing block to file 201 (block # 1347365)
  • Daljit
    Daljit Member Posts: 2,186
    Linux Error: 28: No space left on device
    This is self explanatory, check the available disk space using df command on OS.

    Daljit Singh
  • 501173
    501173 Member Posts: 95
    Filesystem 1k-blocks Used Available Use% Mounted on
    /dev/sda2 4127108 200124 3717336 6% /
    /dev/sda1 46636 14166 30062 33% /boot
    none 513884 0 513884 0% /dev/shm
    /dev/sda3 11052880 5697800 4793612 55% /usr
    /dev/sda6 256667 37742 205673 16% /var
    /dev/sdb1 35001508 33223516 0 100% /u01
    /dev/sdc1 35001508 137340 33086176 1% /u02
    /dev/sdd1 35001508 19748484 13475032 60% /u03
    /dev/sde1 35001508 32844 33190672 1% /u04

    -------------------------------------------------------------------------
    In the /u01 are temp01.dbf (11.039.416.320) and undotbs01.dbf (2.422.218.752) located. Can I delete or truncate the temp01 file to make space on the device or not?
  • 501173
    501173 Member Posts: 95
    Can someone tell me what does the temp01.dbf do?
    Can I truncate it to make space?
  • 370767
    370767 Member Posts: 74
    no you cannot truncate it. it appears to me that your complex sql requires more temp space (via the temp01.dbf file) than your disk allows. add an additional datafile (on another mountpoint) to your temp tablespace or tune your sql to use less temp space (ie do less sorts most likely).

    temp01.dbf is probably used by the temp tablespace. the temp tablespace is where disk sorts occur (among other things)
  • 501173
    501173 Member Posts: 95
    Hi,

    Can you tell me how to add an additional datafile to my temp tablespace?

    Thanks
  • Daljit
    Daljit Member Posts: 2,186
    Try:

    Alter tablespace temp add tempfile 'path of file' size 100m;

    Daljit Singh
  • 501173
    501173 Member Posts: 95
    Hi,

    I found an article regarding dropping the tempfile

    http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

    (Drop Tempfile Command Method - (Oracle9i and higher) section)

    What do you think? Should I delete the temp file (temp01.dbf) and then create it on /u01/oradata/servername/ again?
  • 501173
    501173 Member Posts: 95
    I added an additional datafile temp02.dbf into another mountpoint /u02.
    I could make some simple sql statement.
    But the same error occured when I made a complex select statement.

    Error message in the alert.log:

    ORA-01114: IO error writing block to file 201 (block # 1347351)
    ORA-27072: skgfdisp: I/O error
    Linux Error: 25: Inappropriate ioctl for device
    Additional information: 1347350
  • Daljit
    Daljit Member Posts: 2,186
    Is your new mountpoint also get full?? What is the total size of you temp tablespace now??
    But the same error occured when I made a complex select statement.
    What kind of complex statement it is?? I think you really want to tune it first.

    Daljit Singh
  • 501173
    501173 Member Posts: 95
    No, it is only 1% used.

    Filesystem 1k-blocks Used Available Use% Mounted on
    /dev/sdc1 35001508 137340 33086176 1% /u02

    What does "Inappropriate ioctl for device" mean?
  • 176447
    176447 Member Posts: 389
    Temp file temp tablespace size should reset when you bounce the database. Of course you should have window to bounce it. In this case, any way your datafile is not able to write. You should bounce database.

    It is good idea to keep the temp file in seperate file system expandable within the max size of the file system.

    Ashok
  • 501173
    501173 Member Posts: 95
    Sorry, I am not sure what you meant "you should bounce database".
    And how can I do it?
  • Daljit
    Daljit Member Posts: 2,186
    Temp file temp tablespace size should reset when you bounce the database
    Even after bouncing the server, oracle will not shrink the space of any tempfile instead alread allocated temp segment get free up and thats why oracle can reuse them instead of allocating more space. But at OS level the file size will remain same.

    Daljit Singh
  • Daljit
    Daljit Member Posts: 2,186
    Can you tell us what exactly are you doing? Are you running some shell script or directly running the query using sqlplus? where are you getting this error means in sqlplus window?? Can you post the contents of alert log?

    Daljit Singh
  • 501173
    501173 Member Posts: 95
    I tried to update my database.
    I am running the query using sqlplus.
    Error messages were displayed in the sqlplus window.
    The following messages were found in the alert.log:

    ---------------------------------
    Wed Sep 6 11:02:24 2006
    ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/annotatr/temp02.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 1000K MAXSIZE UNLIMITED
    Wed Sep 6 11:02:24 2006
    Completed: ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/a
    Wed Sep 6 11:07:09 2006
    ALTER DATABASE TEMPFILE '/u02/oradata/annotatr/temp02.dbf'
    AUTOEXTEND ON
    NEXT 1000K
    MAXSIZE 10240M
    Wed Sep 6 11:07:09 2006
    Completed: ALTER DATABASE TEMPFILE '/u02/oradata/annotatr/tem
    Wed Sep 6 12:31:59 2006
    Errors in file /usr/oracle/app/oracle/admin/annotatr/udump/anno_ora_22922.trc:
    ORA-01114: IO error writing block to file 201 (block # 1347365)
    ORA-27072: skgfdisp: I/O error
    Linux Error: 28: No space left on device
    Additional information: 1347364
    ORA-01114: IO error writing block to file 201 (block # 1347365)
    ORA-27072: skgfdisp: I/O error
    Linux Error: 28: No space left on device
    Additional information: 1347364
    ORA-01114: IO error writing block to file 201 (block # 1347351)
    ORA-27072: skgfdisp: I/O error
    Linux Error: 25: Inappropriate ioctl for device
    Additional information: 1347350
    Wed Sep 6 12:58:23 2006
    Errors in file /usr/oracle/app/oracle/admin/annotatr/udump/anno_ora_22998.trc:
    ORA-01114: IO error writing block to file 201 (block # 1347365)
    ORA-27072: skgfdisp: I/O error
    Linux Error: 28: No space left on device
    Additional information: 1347364
    ORA-01114: IO error writing block to file 201 (block # 1347365)
    ORA-27072: skgfdisp: I/O error
    Linux Error: 28: No space left on device
    Additional information: 1347364
    ORA-01114: IO error writing block to file 201 (block # 1347351)
    ORA-27072: skgfdisp: I/O error
    Linux Error: 25: Inappropriate ioctl for device
    Additional information: 1347350
    Wed Sep 6 15:37:02 2006
    Thread 1 advanced to log sequence 2749
    Current log# 3 seq# 2749 mem# 0: /u03/oradata/annotatr/redo03.log
    --------------------------
  • 501173
    501173 Member Posts: 95
    Hi,

    a friend suggested me to reallocate the temp01.dbf datafile to another device

    ALTER DATABASE RENAME
    FILE '/u01/oradata/annotatr/temp01.dbf'
    TO '/u02/oradata/annotatr/temp01.dbf';

    But I got errors:

    ERROR at line 1:
    ORA-01511: error in renaming log/data files
    ORA-01516: nonexistent log file, datafile, or tempfile
    "/u01/oradata/annotatr/temp01.dbf"

    Could someone tell me why the temp01.dbf could not be found? This file is located in /u01/oradata/annotatr.

    Thanks
  • 164995
    164995 Member Posts: 984
    Please post:-
    $ls -ltr /u01/oradata/annotatr/temp01.dbf

    Is this the only temp tablespace in the database. How are you trying to rename the temp tablespace, online or offline.

    Fast way.
    shutdown immediate;
    startup mount;
    alter database rename file '' to '';
    alter database open;

    Other way:
    create temp tablespace new .....
    make this the default tablespace for the database;
    drop the old temp tablespace
  • 501173
    501173 Member Posts: 95
    Hi,

    Thanks.

    $ls -ltr /u01/oradata/annotatr/temp01.dbf

    => -rw-r--r-- 1 oracle dba 11039416320 Sep 6 16:41 /u01/oradata/annotatr/temp01.dbf

    How can I check whether it is the only temp tablespace in the database?
    I tried to rename the temp tablespace online.

    I will try the fast way method.
    Should I enter the command:
    alter database rename file '/u01/oradata/annotatr/temp01.dbf' to '/u02/oradata/annotatr/temp01.dbf'
  • 501173
    501173 Member Posts: 95
    I did some queries:

    SQL> select tablespace_name from dba_data_files;

    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    UNDOTBS1
    CWMLITE
    DRSYS
    EXAMPLE
    INDX
    ODM
    TOOLS
    USERS
    XDB
    ANNO

    TABLESPACE_NAME
    ------------------------------
    ANNO
    HIV
    MITO
    GROUP4

    15 rows selected.

    SQL> select tablespace_name from dba_data_files where file_name = '/u01/oradata/annotatr/temp01.dbf';

    no rows selected
This discussion has been closed.