Operating System - HP-UX
1748036 Members
5149 Online
108757 Solutions
New Discussion юеВ

Re: Which datafiles will be used ??

 
Chris Fung
Frequent Advisor

Which datafiles will be used ??

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,
8 REPLIES 8
Brian Crabtree
Honored Contributor

Re: Which datafiles will be used ??

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

Thanks,

Brian
Chris Fung
Frequent Advisor

Re: Which datafiles will be used ??

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,
Hari Kumar
Trusted Contributor

Re: Which datafiles will be used ??

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
Information is Wealth ; Knowledge is Power
twang
Honored Contributor

Re: Which datafiles will be used ??

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
Thierry Poels_1
Honored Contributor

Re: Which datafiles will be used ??

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.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Sanjiv Sharma_1
Honored Contributor

Re: Which datafiles will be used ??

Chris Fung
Frequent Advisor

Re: Which datafiles will be used ??

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,
Volker Borowski
Honored Contributor

Re: Which datafiles will be used ??

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