Kill all processes - the rough way

On certain occasions, it might be needed to kill several hunderds of processes on a linux machine. Of course you should NOT do this on a production site, but the use of xargs might be handy:

ps -ef | grep oracle | grep -v bash | awk '{ print $2}' | xargs kill -9

With the grep -v you exclude your own putty session.

Sqlplus connect on HP-UX

I had a problem connecting with a script on HP-UX. Somehow it felt like I had seen it earlier....

sqlplus myuser/mypassword@mydb

The HP-UX does not like the @ sign. You'll need to escape it:

sqlplus myuser/mypassword\@mydb

This solved my problem.

Correct NLS_LANG for exports

It's important to set the right NLS_LANG environment variable when doing exports/imports.
The following query makes it easy getting the syntax and values right.

set heading off
set feedback off
select 'export NLS_LANG=' || lan.value || '_' || ter.value || '.' || chr.value
from v$nls_parameters lan,
v$nls_parameters ter,
v$nls_parameters chr
where lan.parameter='NLS_LANGUAGE'
and ter.parameter='NLS_TERRITORY'
and chr.parameter='NLS_CHARACTERSET';
set heading on
set feedback on

This gives the line (e.g.)
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

which can easily be copied before the imp/exp is run.
For windows environments, you would need to replace 'export' by 'set' in the query.

Enjoy!

determine the Oracle version in a nutshell

On a host with multiple Oracle versions in multiple Oracle homes, it might be prove hard to quickly determine the Oracle version in a shell script. I came across the following method and like to share it.

First, grep the line from /etc/oratab which contains the ORACLE_SID at the beginning of that line:

grep -i ^$ORACLE_SID: /etc/oratab

Then cut everything behind the semicolon away:

cut -f2 -d:

this gives you the Oracle home path.

Now cut out everything behind the first dot:

cut -f1 -d.

Taking the basename, will remove the path and leave the Oracle version.
In one statement it will become:

VERSION=$( basename $(grep -i ^$ORACLE_SID: /etc/oratab | cut -f2 -d: | cut -f1 -d. ) )

I know that there are different ways. Suggestions welcome!