Use vi to replace some pattern, but only for specific lines

Sometimes is very handful to use the Unix/Linux utility "vi" to replace a specific pattern, but there is a special syntax if we only want to do it for a specific range of rows, see exmaple below:

Use find and replace on line ranges (match by line numbers)

You can also make changes on range of lines i.e. replace first occurrence of foo with bar on lines 5 through 20 only, enter:
:5,20s/foo/bar/

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