Skip to main content

Permitting a user on UNIX / Linux to use sqlplus

We encountered an interesting scenario worth mentioning.

  1. Oracle is installed on a Linux server newly & another ftp user wants to just use sqlplus also.
  2. Just setting the ORACLE_HOME, LD_LIBRARY_PATH, ORACLE_SID, PATH in the user's profile did not let him run the "sqlplus" command at all. The error shown was :
  3. $ sqlplus sqlplus: fatal: /app/oracle/product/10.2/lib/ Permission
  4. The files under "lib" directory under home didn't have permission for anybody other than "oracle" user.
  5. -rwxr-x--- 1 oracle oinstall 1320016 Jun 28 2005 /app/oracle/product/10.2/lib/
  6. When we permit the other users (other than oracle) to read/execute on the lib directory (chmod -R o+rx $ORACLE_HOME/lib), the error changed.
  7. $ sqlplus
    Error 6 initializing SQL*Plus
    Message file sp1.msb not found
    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
  8. Now, sqlplus is looking for sp1us.lang file under $ORACLE_HOME/sqlplus/mesg directory as per the information available on Oracle forums. Hence, the error. I allowed the others to "rx" here too.
  9. The error changed again :
  10. $ sqlplus
    SP2-1503: Unable to initialize Oracle call interface
    SP2-0152: ORACLE may not be functioning properly
  11. The actual procedure followed was to allow "rx" on all directories after "sqlplus" & then removing "rx" privilege one-by-one from each unimportant directory & testing sqlplus command at each step, from another session.
  12. I used a little script to change all the directory permissions interactively, only if I want to (by looking at the name & thinking as an Oracle DBA ;-) ) and ran this from inside ORACLE_HOME .
  13. $ for i in `ls $ORACLE_HOME`;
    echo "Dir or file : $i";
    echo "change ?";
    read ans;
    if [ $ans -eq '1' ];
    then chmod -R o-rx $i;
    else echo "Didn't change";
  14. The new information I discovered was on allowing "rx" to "oracore" directory in ORACLE_HOME, "sqlplus" started working. There's a mesg directory there too, which none of the errors pointed to :-( !

Conclusion :
  1. For any normal user (not a part of "oinstall" / "dba" groups ) to be able to run sqlplus and access an ORACLE database , read/execute permissions are required for these 4 directories :
    1. $ORACLE_HOME/bin
    2. $ORACLE_HOME/lib
    3. $ORACLE_HOME/oracore
    4. $ORACLE_HOME/sqlplus
  2. In addition, these 4 parameters should also be set in the user's environment (.profile) :
    3. ORACLE_SID,
    4. PATH

Popular posts from this blog

HWM (high watermark) of a tablespace ?

How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage.
HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. All segments are physically allocated by way of extents, which are contiguous blocks of data. Now, when we talk about contiguous blocks, 2 datafiles (even from same tablespace) are highly unlikely to have contiguguos data blocks. Hence, theoretically, it's possible to calculate high watermark within a datafile, not a tablespace directly, as the latter is only a logical unit. Although, we can resize a tablespace, however the resize value needs to be based on exact calculati…

Intereseting --- 249 days bug for Oracle on Linux servers

--contributed by Manjunath Ulthi, Senior DBA at Infosys Technologies limited

1 Introduction

Problem: OCI client spins when machine uptime >= 249 days in Linux machines. Once the uptime crosses 249 days the database client hangs and leads to high CPU utilization and server hung state. The user will not be able to connect to the databases This is oracle known bug 4612267.

Solution: This can be handled by applying the patch 4612267.

The patch has been applied in the servers server126, server041 & server120 and tested. After the patch has been applied the client connections were normal after 249 days and all the operations of databases/applications were normal and didn’t encounter any problems.

For further information refer the Metalink Notes

Note: 338461.1: SQL*Plus With Instant Client Hangs, When System Uptime Is More Than 248 Days

Note:4612267.8 : Bug 4612267 - OCI client spins when machine uptime >= 249 days

2 General Instructions

Procedure to apply the patch

1. Download the …