ORA-01114, but datafile size is 2 GB :(

[KAN]

Hi folks,

I created a table named FACT_OBJ. The table is stored in a tablespace named TS_FACT_OBJ. The tabelspace consists of eight datafiles; TS_FACT_OBJ_01 to TS_FACT_OBJ_08. The size of each datafile is 2097176 KB (2 GB + 64 KB).

I then populate the table FACT_OBJ using INSERT INTO ... (SELECT ...). The source tables, however, reside on another database, so I created a database link named DATACENT (note the FROM clause). The table populating script is as follows:

insert into FACT_OBJ
(
  select
  DAT_OBJ_PJK.CODE_OP_TYPE as OPTYPE_STD_OPTYPE,
  DAT_OBJ_PJK.CODE_PROVINCE ||
  DAT_OBJ_PJK.CODE_CITY ||
  DAT_OBJ_PJK.CODE_DISTRICT ||
  DAT_OBJ_PJK.CODE_SUBDISTRICT as AREA_STD_SUBDISTRICT_CODE,
  DAT_OBJ_PJK.MAP_STATUS_OP as MAPSTATOP_STD_MAPSTAT_CODE,
  DAT_OBJ_PJK.OP_TRANSACT_CODE as TRNTYPE_STD_TRNTYPE_CODECODE,
  DAT_OBJ_BM.LAND_TYPE as JPT_STD_JPT_CODE,
  SPPT.CODE_LAND_CLASS as LANDCLS_STD_LNDCLS_CODE,
  SPPT.CODE_BUILD_CLASS as BUILDCLS_STD_BLDCLS_CODE,
  DAT_OBJ_PJK.OP_ENTRY_DATE as TIME_STD_MON_CODE,
  1 as JML_OP,
  DAT_OBJ_PJK.LAND_SQR_TOT as LAND_SQR_OP,
  DAT_OBJ_PJK.BUILD_SQR_TOT as BUILD_SQR_OP,
  DAT_OBJ_PJK.LAND_VAL as LAND_VAL_OP,
  DAT_OBJ_PJK.BUILD_VAL,
  DAT_OBJ_ANGG.LAND_SQR_WGT as LAND_SQR_WGT_OP,
  DAT_OBJ_ANGG.BUILD_SQR_WGT as BUILD_SQR_WGT,
  DAT_OBJ_PJK.OP_STREET as OP_STREET,
  DAT_OBJ_PJK.OP_BLOCK as OP_BLOCK,
  DAT_OBJ_PJK.OP_RW as OP_RW,
  DAT_OBJ_PJK.OP_RT as OP_RT,
  REF_ADM_OFF_DISTRICT.CODE_DISTRICT_OFF||REF_ADM_OFF_DISTRICT.CODE_BRANCH_OFF as CODE_BRANCH_OFF,
  DAT_SBJ_PJK.ID_SBJ_PJK as ID_SBJ_PJK

  from
  DAT_OBJ_PJK@DATACENT,
  DAT_OBJ_BM@DATACENT,
  DAT_OBJ_ANGG@DATACENT,
  SPPT@DATACENT,
  REF_ADM_OFF_DISTRICT@DATACENT,
  DAT_SBJ_PJK@DATACENT

  where
  DAT_OBJ_PJK.CODE_PROVINCE=DAT_OBJ_BM.CODE_PROVINCE(+) AND
  DAT_OBJ_PJK.CODE_CITY=DAT_OBJ_BM.CODE_CITY(+) AND
  DAT_OBJ_PJK.CODE_DISTRICT=DAT_OBJ_BM.CODE_DISTRICT(+) AND
  DAT_OBJ_PJK.CODE_SUBDISTRICT=DAT_OBJ_BM.CODE_SUBDISTRICT(+) AND
  DAT_OBJ_PJK.CODE_BLOCK=DAT_OBJ_BM.CODE_BLOCK(+) AND
  DAT_OBJ_PJK.SEQ_NUM=DAT_OBJ_BM.SEQ_NUM(+) AND
  DAT_OBJ_PJK.CODE_OP_TYPE=DAT_OBJ_BM.CODE_OP_TYPE(+) AND
  DAT_OBJ_PJK.CODE_PROVINCE=DAT_OBJ_ANGG.CODE_PROVINCE(+) AND
  DAT_OBJ_PJK.CODE_CITY=DAT_OBJ_ANGG.CODE_CITY(+) AND
  DAT_OBJ_PJK.CODE_DISTRICT=DAT_OBJ_ANGG.CODE_DISTRICT(+) AND
  DAT_OBJ_PJK.CODE_SUBDISTRICT=DAT_OBJ_ANGG.CODE_SUBDISTRICT(+) AND
  DAT_OBJ_PJK.CODE_BLOCK=DAT_OBJ_ANGG.CODE_BLOCK(+) AND
  DAT_OBJ_PJK.SEQ_NUM=DAT_OBJ_ANGG.SEQ_NUM(+) AND
  DAT_OBJ_PJK.CODE_OP_TYPE=DAT_OBJ_ANGG.CODE_OP_TYPE(+) AND
  DAT_OBJ_PJK.CODE_PROVINCE=SPPT.CODE_PROVINCE(+) AND
  DAT_OBJ_PJK.CODE_CITY=SPPT.CODE_CITY(+) AND
  DAT_OBJ_PJK.CODE_DISTRICT=SPPT.CODE_DISTRICT(+) AND
  DAT_OBJ_PJK.CODE_SUBDISTRICT=SPPT.CODE_SUBDISTRICT(+) AND
  DAT_OBJ_PJK.CODE_BLOCK=SPPT.CODE_BLOCK(+) AND
  DAT_OBJ_PJK.SEQ_NUM=SPPT.SEQ_NUM(+) AND
  DAT_OBJ_PJK.CODE_OP_TYPE=SPPT.CODE_OP_TYPE(+) AND
  DAT_OBJ_PJK.CODE_PROVINCE=REF_ADM_OFF_DISTRICT.CODE_PROVINCE AND
  DAT_OBJ_PJK.CODE_CITY=REF_ADM_OFF_DISTRICT.CODE_CITY AND
  DAT_OBJ_PJK.CODE_DISTRICT=REF_ADM_OFF_DISTRICT.CODE_DISTRICT AND
  DAT_OBJ_PJK.ID_SBJ_PJK=DAT_SBJ_PJK.ID_SBJ_PJK AND
  DAT_OBJ_PJK.CODE_PROVINCE='30' AND
  TO_CHAR(DAT_OBJ_PJK.OP_ENTRY_DATE,'YYYY')='2004'
);

But when running the script, I got an ORA-01114 error message:

ERROR at line 60:
ORA-01114: IO error writing block to file 201 (block # 524140)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 524142)
ORA-01114: IO error writing block to file (block # )

Well, I was totally baffled. I mean, as far as I'm concerned, ORA-01114 and ORA-27069 usually happen when you have a very large datafile (4 GB or above). None of my datafiles exceeds 4 GB. In fact, the datafiles belong to the populated table (table FACT_OBJ ) are merely 2097176 KB each (2 GB + 64 KB).

What's wrong? Why the ORA-01114 happened? I'm using Oracle 9.2.0.2 64-bit, running on Windows 2003 Server and Intel Itanium 64-bit processor.

Oh, and here's the line 60. I'm not sure if it actually relates to the error, though; it is merely a join clause:

 60  DAT_OBJ_PJK.ID_SBJ_PJK=DAT_SBJ_PJK.ID_SBJ_PJK AND

[slimdave]

I don't really have an answer about the error you're getting, but I do notice that you're not using direct path inserts and therefore you're not using data segment compression ... you might want to consider that.

[tamilselvan]

You have sized the data file more than 2 GB. I suspect Oracle may not able to read beyond 2GB file size unless the system is configured with large file option.
See below my calculation:

SQL> select 2*1024*1024*1024 from dual;

2*1024*1024*1024
----------------
2147483648 ----> 2 GB

SQL> select 2097176 *1024 + (64*1024) from dual ;

2097176*1024+(64*1024)
----------------------
2147573760 --->2 GB + 64KB

My suggestion is resize the data file to 2048 MB or even lesser value.

Tamil

[KAN]

You have sized the data file more than 2 GB. I suspect Oracle may not able to read beyond 2GB file size unless the system is configured with large file option.

Thanks! I indeed configured the datafile to be 2097176 KB (2 GB + 64 KB). I never thought it would have caused the ORA-01114 error; I thought the limit is 4 GB instead of 2 GB.

By the way, such problem does not happen on 32-bit Windows. I used Oracle 9.2.0.1 with Windows 2000 and 32-bit Xeon processor, and I have no trouble with 4 GB datafiles even though I did not configure the system with large file option.

Does such problem only happen in 64-bit Windows?

[KAN]

Well I have reduced the datafile to 768 MB, but the problem persist. What's wrong? :(

[mike9]

Hi,

I just had a look at metalink. It seems to me that you should upgrade from 9.2.0.2 to, at least, 9.2.0.3 to have a certified OS/Oracle combination.

Certify - Additional Info Oracle Database - Enterprise Edition Version 9.2 64-bit On Microsoft Windows 2003 (Itanium)
Operating System: Microsoft Windows 2003 (Itanium) Version 2003
Oracle Database - Enterprise Edition Version 9.2 64-bit
N/A Version N/A
Status: Certified

Product Version Note:
None available for this product.

Certification Note:


Patch Set 9.2.0.3.0 or higher is required.
Existing patch sets:
9.2.0.4.0
9.2.0.5.0
9.2.0.6.0
9.2.0.7.0