Oracle Unknown Error ORA-01114: IO error writing block to file 201, when I Run My Select Command

when I Run My "Select * from tab1,tab2, ...where ... " This Error Occures:

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

I suppose that this error Ocured because of my huge selection and join of tables, in my select command, i have 14 tables in my join, and when I decreese the number of tables that joined together, this error disapear.....
LVL 2
abbas_najafizadehAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

techjiCommented:
No your datafile simply grew larger than 4GB or so. This is a bug.

oracle version ?
OS ?
techjiCommented:
your joins use temporary segments in temp tablespace.
how big your temporary tablespace ? does it have many tempfiles or one tempfile ? what is the current size of the tempfiles ? Does it have autoextend on beyond the approved OS limit ?
abbas_najafizadehAuthor Commented:
oracle version 9.20
os: windows 2003 advanced server

My DB have 1 Temprory tablespace, the size is: 3GB , IS Autoextend

My SYSTEM TableSpace is 10Gb and 5.7 Gb used in it ....
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

techjiCommented:
is it 32-bit OS or 64-bit OS ? there is filesize limit on 32-bit OS.

Can you take out autoextend on tempfiles or cap it at 2GB and add more tempfiles to the temporary tablespace.
techjiCommented:
ORA-01114: IO error writing block to file 201 (block # 524041)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file

is an indication that when you ran the sql, your temp tablespace expanded beyond the OS range of the file, and it couldnt create additional temporary segment.

So if you can get an additional tempfile added to your tempfile you will be fine. But first cap the autoextend at or below what it is currently.
abbas_najafizadehAuthor Commented:
How can I get in windows, kind of CPU (32bit or 64bit) ?
abbas_najafizadehAuthor Commented:
just Now I Added 3 Temporary Tablespaces , one 500Mb autoextend, and two 1500Mb autoextend.....

But the Problem persists...
techjiCommented:
not the tablespaces, you need to add tempfiles to one tablespace and make it bigger than 3GB.

You have multiple tempfiles per temp tablespace.
abbas_najafizadehAuthor Commented:
now I deleted All My temp tablespaces but One.....

then, I added  2 Tempfiles to temp tablespace "TEMP", two 2000 Mb autoextend....

now, I have one temp tablespace with 3 tempFiles, the size is minimum 4Gb now....
but the problem persists...
techjiCommented:
interesting. The only files affected during a query are tempfiles.

What are the current sizes of all the tempfiles you got in your temp tablespace now ?

also can you check the default temporay tablespace for the user you can running this sql from -

select username, temporary_tablespace from dba_users;
techjiCommented:
My SYSTEM TableSpace is 10Gb and 5.7 Gb used in it ....

this is very bad. You shouldnt put anything other than data dictionary in SYSTEM tablespace. It appears some of your users default to SYSTEM tablespace for storing their tables. I suspect the default temporary tablespace is set to SYSTEM as well.
abbas_najafizadehAuthor Commented:
I have Just One Tablesspace "TEMP" used for all My users...

This Temp Tablespace has these tempfiles:
1- the first original tempfile: 4Gb
2- my added tempfile: 2Gb
3- my added tempfile: 2Gb


techjiCommented:
To find out which operating system your computer is running, first click Start, and then click Run. In the box that appears, type winver, and then click OK. The window that appears will show you which version of Windows your computer is using.
techjiCommented:
see if you can shrink down the first tempfile from 4GB down to 2GB. The bug I referred to was on windows, caused when running select statement and the file size reached 4GB.
abbas_najafizadehAuthor Commented:
Microsoft windows server 2003 Enterprise edition
ver 5.2
abbas_najafizadehAuthor Commented:
how can i down it to 2Gb...
techjiCommented:
You can do it OEM. Were you using OEM.

if not you can use this -

ALTER DATABASE TEMPFILE  '{filepath}/temp01.dbf'  RESIZE 2000M;
techjiCommented:
in OEM just change the size and then apply.
abbas_najafizadehAuthor Commented:
The following error has occurred:

ORA-03297: file contains used data beyond requested RESIZE value
techjiCommented:
try different sizes 3000, 2500 until you are able to shrink to down as much as possible.
sathyagiriCommented:
Instead drop and recreate your temp file.

ALTER DATABASE DROP TEMPFILE <tfile>

Recreate with new size.

abbas_najafizadehAuthor Commented:
rejected: 3500mb
rejected: 3600mb
rejected: 3800mb
rejected: 3900mb
rejected: 4000mb
rejected: 4050mb

accepted: 4100 Mb

it's fully have data.....
techjiCommented:
Ok. It is busy right now. There may be other session using it or you just have to wait for oracle process SMON  to cleanup.
My recommendation at this point -

create a new temporary tablespace. Give it 4GB. If you need more you can add more. But be sure not to create a tempfile larger than 2GB. Add more tempfiles if needed.
Assign the new tablespace to the user you are running the sql query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abbas_najafizadehAuthor Commented:
I added another temp tablespace "TEMP3" 2000Gb unlimited , I alter my user temporary tablespace to "TEMP3".....


and added 3 tempfiles  to it, with each size 2000GB...

I Run my Select command, and I'm now waiting for reponse from oracle....!
techjiCommented:
one final comment -

"TEMP3" 2000Gb unlimited <-- is bad. You may run into same situation again. Always cap the autoextend.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.