Using oracle DB to find on which day of the week you were born? :-)

To find out which day of the week a specific event was on, you can use the database, see example below:

SQL> alter session set nls_date_format='day dd-mon-yyyy';

Session altered.

SQL> select to_date('31-mar-1979','dd-mon-yyyy') from dual;

TO_DATE('31-MAR-1979'

---------------------

saturday  31-mar-1979

Crontab job to run every 5 minutes, for example

Just a small note, if you ever need to run a job in crontab every 5 minutes, let’s say, this is the way to do it:  (as opposed to writing 00,05,10,15….)

*/5 * * * * /u01/app/oracle/bin/ora_rm_arc MYDB 24 > /dev/null 2>&1

How to hint query based on a view?

Sometimes we need to add a hint on a query, which is based on a view.
For examples:

SQL>select name from all_employees_v where id > 101;

Since employees_v is a complex view, can we still add a hint to manipulate the optimizer, without modifying the view?

The answer is yes and is very nice detailed in the link below:

http://alexzeng.wordpress.com/2013/12/29/how-to-add-hint-for-sql-using-oracle-view/

Thank you Alex for sharing :-)

setsid Unix command to the rescue!

Assuming you have a main script, which is running a few other scripts in nohup in the background, and at the end, you want to run "tail -f" for the log file in a loop, until done.
If you press Ctrl-C, the main script will die, together with all the other scripts running in nohup, not exactly the desired result.

For example:

Main.sh:

nohup ./script1  > ${NOHUP_FILE1} 2>&1 &
nohup ./script2  > ${NOHUP_FILE2} 2>&1 &

RUN_IND=0
while [ $RUN_IND -eq 0 ]  ; do
ps -ef |grep Main |grep -v grep  > /dev/null
RUN_IND=$?
tail  -5   ${NOHUP_FILE1}
sleep 5
done

The solution will be to replace nohup by setsid, as below:

Main.sh:

setsid./script1  > ${NOHUP_FILE1} 2>&1 &
setsid./script2  > ${NOHUP_FILE2} 2>&1 &

RUN_IND=0
while [ $RUN_IND -eq 0 ]  ; do
ps -ef |grep Main |grep -v grep  > /dev/null
RUN_IND=$?
tail  -5   ${NOHUP_FILE1}
sleep 5
done

How to avoid printing sqlplus query output on the screen?

Sometimes, we have a shell script and inside we run a SQL query and we just want to spool the result in a file, without printing the output on the screen.
SET TERMOUT OFF will do the job, but only if it is inside a SQL script.

So this will work:

echo "set pages 0 feedback off  heads off echo off TERMOUT off" >> ${sql_v}
echo "spool Tmp/db.lst" >> ${sql_v}
echo "select distinct  db_name  from my_table order by  1;" >> ${sql_v}
echo "spool off " >> ${sql_v}
echo "exit; " >> ${sql_v}

sqlplus -s $MY_DB_CONNECT  @${sql_v}

But this will NOT work:

sqlplus -s $MY_DB_CONNECT << ENDofSql1
set pages 0 feedback off  heads off echo off TERM OFF
select distinct  db_name  from my_table order by  ENV_CODE;
exit
ENDofSql1

I thought of mentioning this, since it can be frustrating at times...

Annoying oracle error during software installation

It may happen that you install first oracle server/client in a specific directory and then you'd like to do a brand new installation, but you are getting the error below, even after you've completely removed the OH:

[INS-32025] The chosen installation conflicts with software already installed in the given Oracle home.

The solution is to tamper with the configuration file:

oraInventory/ContentsXML/inventory.xml

First create a backup of the above file and then remove the lines related to the previous installation.

Arithmetic inside Linux ksh script

Sometimes, you write a shell script and the need arises for a little arithemtic inside. There are a few ways to do this, below is the simplest way, in my opinion; this is a very simple example, to calculate the percentage done by a specific job:

#!/bin/ksh

total=27800
done=`grep 9k4dgqum1pkdh MYDB_ora_7519.trc |wc -l`


s=`echo "${done}*100/${total}" | bc`

echo "Completed : ${s}%"