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