ORA- errors...some direction please

[saira78]

List,

I have users executing an ksh script which calls an SQL*Plus script and they get this error. Since it is only a matter of reading a data file, can we give users permission to read this file, so they can execute the query in the ERP environment or does it pose a threat?

$ ls -l
total 6245888
-rw-rw---- 1 oracle oinstall 50868224 Apr 15 13:55 customer_data01.dbf
-rw-rw---- 1 oracle oinstall 2411728896 Apr 15 13:55 order_data01.dbf
-rw-r----- 1 oracle oinstall 78643712 Apr 15 13:48 redo_03a.log
-rw-rw---- 1 oracle oinstall 243937280 Apr 15 13:55 wms_data01.dbf
-rw-rw---- 1 oracle oinstall 412188672 Apr 15 13:55 wms_idx_static01.dbf

select a.order_date, c.cust_name, count(b.ord_id)
*
ERROR at line 1:
ORA-01115: IO error reading block from file 13 (block # 35)
ORA-01110: data file 13:
'/u05/oradata/THLI/order_data01.dbf'
ORA-27092: skgfofi: size of file exceeds file size limit of
the process
Additional information: 262143
Additional information: 588801

Then I have to address the other ora error regarding the size of file. If you have any insights into this, I'd be grateful.

In /etc/security/limits:

default:
fsize = 2097151
core = 2097151
cpu = -1
data = 262144
rss = 65536
stack = 65536
nofiles = 2000


Thanks!

 

[sem]

The problem is not on client-side. Your server process exceeded the file size limit for its launching shell. Try to add

ulimit -f unlimited

somewhere in startup script to override this setting.

Regards, Dima

 

[saira78]

/home/tecsys/scripts/test.sh[13]: ulimit: 0403-045 The specified value is outside the user's allowable range.

ulimit is set to unlimited following your suggestion. Actually, it was set to unlimited to begin with but I stated it explicitly in the script.

What am I missing?

Thanks,
Saira

 

[sem]

I can not understand the first line: did you try it from Oracle or from tecsys?

Regards, Dima

 

[saira78]

I'm running it from a different user account. Here are the permissions on the script:

-rwxr-xr-x 1 tecsys elite 901 Apr 16 09:42 test.sh
-rwxr-xr-x 1 tecsys elite 1471 Apr 16 09:37 test.sql

If I run it as tecsys (ERP user account), I do not get such an error. However, what confuses me is that both have their ulimits set to unlimited. I can't see the difference between the two. I tried moving the script out of the tecsys directory and into a common directory, but the results were the same.

 

[sem]

Do you start Oracle Server from this account? The problem is with shell starting ORACLE, not ERP application. So you should check it for user launching server (Oracle?).

Regards, Dima

 

[saira78]

Here is part of the script.

# test.sh
# Set the environment
ulimit -f unlimited
ORACLE_SID=SID1
ORACLE_HOME=/apps/oracle/products/8.1.7
ORACLE_BIN=$ORACLE_HOME/bin
PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID ORACLE_HOME ORACLE_BIN PATH

$ORACLE_BIN/sqlplus -s schema/password<<!

@/home/tecsys/scripts/test.sql

exit
!

 

[sem]

Just read my previous posts A BIT MORE CAREFULLY. The error was produced by SERVER. So you should place ulimit to SERVER startup script. Your client settings do not affect server process. Ask your DBA for details.

Regards, Dima

 

[saira78]

Thanks for your help. We seem to be interpreting my problem differently. I have enlisted the assistance of my senior DBA.