Running out of space in /tmp while using the "vi" editor

The "vi" editor is using the /tmp directory to place its buffer and whenever there is shortage of space there, vi is failing.
The solution below worked for me as a charm:

>cd my_directory
>vi

 Inside vi:

:set directory=my_new_temp
:e file_name

where my_new_temp is a directory with enough disk space free and file_name is the name of the file to edit.

Is supplemenal logging enabled for my table?

The answer comes in by querying the view below:

SQL>select * from dba_log_groups;

As easy as this :-)

dbua is failing during 12c upgrade: "you do not have enough tablespace free space or disk space to complete the upgrade."

During a 12c upgrade from 11.2, using dbua, I've received the error message above, even that all the tablespace had enough disk space and no space shortage in any file system either.
 The dbua trace file mentioned that the error was related to UNDOTBS tablespace.

The solution was to make the UNDOTBS datafile exensible, and the dbua went on :-)

SQL>alter database datafile '/mydb/ora_data02/undo_MYDB_01.dbf' autoextend on;

The issue here was that the dbua was failing, even that all the logs indicated that the space was OK.

dbms_stats.import_table_stats is NOT importing the statistics ;-(

It happens quite often that copying statistics from one database to another, using the dbms_stats various procedures ( create_table_stats, export_table_stats, import_table_stats) is a bit challenging.

You run:

SQL> exec dbms_stats.import_table_stats(user,tabname=>'my_table',stattab=>'my_table_STATS');

PL/SQL procedure successfully completed.

The prompt comes back immediately and checking for example num_ros or last_abalyzed from user_tables confirms that nothing was done.

There are a few possible causes to this: the source and target table have to have the same number of partitions, the same partition names and of course, the table owner has to be the same, or , like in my case, has to be adjusted.
The column called "C5" hold the DB user name.

SQL> update my_table_STATS set c5='new_owner' where c5='old_owner';

2412 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.import_table_stats(user,tabname=>'my_table',stattab=>'my_table_STATS');

PL/SQL procedure successfully completed.

SQL> select num_rows from tabs where table_name='MY_TABLE';

  NUM_ROWS
----------
 416503400

 So now the import of stats was successful.

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}%"