SQLPlus (Shared Library) Not Found! (Linux, 10g2)

After installing Oracle database 10g2 on Linux, you may get the following error when trying to invoke sqlplus

$ sqlplus
sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Mmmm, that's strange, the install went perfectly. This error occurs under the following conditions

• The user invoking sqlplus is not the oracle user (or a member of the oinstall group)
• No Metalink patches have been applied yet
  

So let's do some investigation

$ su -
Password:
~ # locate libsqlplus.so
/u01/app/oracle/product/10.2.0/db_1/lib/libsqlplus.so
~ # ll /u01/app/oracle/product/10.2.0/db_1/lib/libsqlplus.so
-rw-r----- 1 oracle oinstall 1047293 Jun 22 2005 /u01/app/oracle/product/10.2.0/db_1/lib/libsqlplus.so

So clearly the shared object libsqlplus.so is there but note that it is only readable by the oracle user and members of oinstall group. This is a bug in the Linux release - it does seem to indicate some pretty poor quality control on the part of Oracle's release engineering team. I would have thought that this type of "correct permissions" error is part of the standard tests run immediately before a new version is OK'd for release.......Oracle, you do have such standard checks?

A quick scan through $ORACLE_HOME will show that some of the executables are not actually executable by everyone (permissions are rwxr-x---, when they should be rwxr-xr-x). If the installation is for training, experimenting etc and thus has no license, it can be easily fixed by simply doing

# chmod -R a+rX $ORACLE_HOME

The capital X gives everyone (all) executable permissions if, and only if, the owner has executable permissions.

If it is a licensed version then download patch 4516865 which will install a script called $ORACLE_HOME/install/changePerm.sh, which you obviously need to run. But this script has a bug too - it does not update $ORACLE_HOME/lib/libclntsh.so.10.1 - you can just chmod 755 this file manually. Not sure if there is another Metalink patch to do this "officially".

SQLPlus, RMAN etc. with Command History and Auto-Completion

Oracle's sqlplus command line interface to the database is fairly primitive and lacks two of the most often used and powerful features found in shells - a history of commands and auto-completion of keywords (upon hitting the tab key). These features can be added quite easily.

Command History
Hans Lub has written a readline wrapper called rlwrap that allows for editing of any keyboard input. RPM packages do not seem to be widely available so I built one myself - it is available here (version 0.30, built on 32 bit CentOS on 10-Sep-2008, requires readline version >= 4.2). To install, simply do

rpm -ivh rlwrap-0.30-1.i386.rpm

See the man page for details and usage examples.

Tab Auto-Completion
Johannes Gritsch has produced some extensions to rlwrap. These extensions consist of

• The list of Oracle keywords, names of all V$ views, complete data dictionary, DBMS_* and UTL_* packages, SQL functions etc.
• A shell script called sql+ that tells rlwrap what valid SQL word delimiters are (since rlwrap was written for the bash shell there are some differences e.g. $ and # are delimiters in bash but not in SQL).

Since the names of Oracle "variables" differ across versions there are extensions available for 9i, 10g and 11g. Download them here and install as follows (for 10g on CentOS/Redhat/Fedora):

wget http://www.linuxification.at/download/rlwrap-extensions-1.00.tar.gz
mkdir rlwrap-ext
tar xzf rlwrap-extensions-1.00.tar.gz -C rlwrap-ext
chown root:root -R rlwrap-ext
sed -e 's#/usr/local/share/rlwrap#/usr/share/rlwrap#' sql+ > sql+.tmp
mv sql+.tmp sql+
chmod 755 sql+
cp rlwrap-ext/sql+ /usr/local/bin/
cp rlwrap-ext/asmcmd /usr/share/rlwrap/
cp rlwrap-ext/rman /usr/share/rlwrap/
cp rlwrap-ext/sqlplus* /usr/share/rlwrap/

To use it, just do one of the following which then invokes rlwrap with the correct options for SQL and keyword list for tab auto-completion

sql+ username/password
sql+ # without arguments it assumes the SYS user connecting as SYSDBA

Nice and simple! Thanks Hans and Johannes - time permitting I'd like to put these two programs together into one RPM package......

Extracting a File from RPM Package

Ever wanted to see the contents of just one file inside a RPM package but didn't want to actually install the package? Then simply do the following which will extract all the files into the current directory

rpm2cpio package_name.rpm | cpio -idm

Can also a "v" option for a verbose progress listing.