Ora-27069 Using Autoextend Bigfile Temporary Tablespace (Doc ID 444929.1)

Last updated on SEPTEMBER 12, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Information in this document applies to any platform.


Symptoms

When a temporary tablespace (BIGFILE TEMP TABLESPACE) created with TEMPFILES on a NFS MOUNT and has been set with AUTOEXTEND, parallel SQLLOADER operations fail with ORA-27069 when the tempfile needs to be extended for any sort operations.  The TEMP TABLESPACE will not have any problems if the files have been placed on the local file system.

 

 

Example:

1) Create a Temporary tablespace on a Local Disk and then run the load. 

SQL> create bigfile temporary tablespace tempsr tempfile
'/oracle/tmp/tempsr.dbf' size 10M autoextend on next 1M;

The load into the tables in this case runs successfully.
Temporary tablespace was enlarged up to 450MB.

2) Create a Temporary tablespace on an NFS Mount and then run the load. 

create bigfile temporary tablespace tempsr tempfile
'/oradata/clr/ftdb/tmp/tempsr.dbf' size 10M autoextend on next 1M.

Temporary tablespace was enlarged up to 280MB,

load fails with:

ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 35306
Additional information: 31
Additional information: 35329
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/oradata/clr/ftdb/tmp/tempsr.dbf'
ORA-01200: actual file size of 35328 is smaller than correct size of 35456 blocks

ALERT LOG FILE:

Sun Dec 3 13:54:07 2006
Errors in file /oracle/products/10.2/admin/ftdb/udump/ftdb_ora_17959.trc:
ORA-01114: IO error writing block to file 201 (block # 35306)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 35306
Additional information: 31
Additional information: 35329
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/oradata/clr/ftdb/tmp/tempsr.dbf'
ORA-01200: actual file size of 35328 is smaller than correct size of 35456 blocks

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms