topic Which datafiles will be used ?? in Operating System - HP-UX

Which datafiles will be used ??

Chris Fung — Mon, 12 Apr 2004 20:39:05 GMT

Hi there,

Suppose I have 2 data files in a tablespace and both of them are empty. Just wondering what criteria Oracle uses to write data to these data files ?

As I have encounter a case for temporary tablespace - one data file is 4GB and the other is 2GB. One user account with 2GB file size ulimit is unable to execute the query when the temporary tablespace is empty.

Any idea ??

Cheers,

Chris,

Re: Which datafiles will be used ??

Brian Crabtree — Mon, 12 Apr 2004 22:02:11 GMT

What error are they getting? How are they connected to the database (local or remote)?

Thanks,

Brian

Re: Which datafiles will be used ??

Chris Fung — Mon, 12 Apr 2004 22:31:27 GMT

Hi Brian,

The error message is:

ERROR at line 1:
ORA-01114: IO error writing block to file 4 (block # 420098)
ORA-27092: skgfofi: size of file exceeds file size limit of the process
Additional information: 262143
Additional information: 512001
ORA-01114: IO error writing block to file 4 (block # 420098)
ORA-27092: skgfofi: size of file exceeds file size limit of the process
Additional information: 262143
Additional information: 512001

And the sql is run by cronjob. The user account is OS authenticated.

Cheers,

Chris,

Re: Which datafiles will be used ??

Hari Kumar — Mon, 12 Apr 2004 23:57:24 GMT

Hi Chris

Set the ulimit file limit for the user to unlimited or at least more than the
maximum datafile size.

There r two kinds of Limits Hard and Soft Limit.
Increasing the limit for a resource usually requires special privileges. Some systems allow you to lower resource limits and later increase them. These are called soft lim-
its. Once a hard limit is set the resource can not be increased.

Which oracle version ur using and what is OS.

With Regards
Hari Kumar

Re: Which datafiles will be used ??

twang — Tue, 13 Apr 2004 02:15:14 GMT

Hi Chris,
if hpux, to change the process resource limits:
to display the soft limit for stack size:
$ ulimit -s
to set the soft limit to 'unlimited':
$ ulimit -s unlimited
to display the hard limit for stack size:
$ ulimit -Hs 8192
to increase the hard limit:
$ ulimit -Hs 8193

regards tommy

Re: Which datafiles will be used ??

Thierry Poels_1 — Tue, 13 Apr 2004 02:48:39 GMT

Hi,

users do not write to Oracle datafiles. This is handled by the Oracle database owner, only this user needs to have access to this files, enough quotas, etc.

regards,
Thierry.

Re: Which datafiles will be used ??

Sanjiv Sharma_1 — Tue, 13 Apr 2004 04:21:37 GMT

Hi Chris,

Please check this link:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=227044
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=14213

hth.

Re: Which datafiles will be used ??

Chris Fung — Thu, 15 Apr 2004 01:14:17 GMT

Hi there,

Thanks for all your solutions. However, I would like to know how Oracle determines which datafile to use when handling queries / or other insert, update, delete operations.

Any related documents / resource can help ?

Many thanks,

Chris,

Re: Which datafiles will be used ??

Volker Borowski — Fri, 16 Apr 2004 17:08:46 GMT

Hi Chris,

this might be outdated and there are diffrences now between Dictionary and local managed tablespaces ....
http://asktom.oracle.com/pls/ask/f?p=4950:8:832400930516336758::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2091240981476,

There are pretty good whitepapers available at http://otn.oracle.com

Hope this helps
Volker