sql loader and tab separated multibyte fields

when you use following part in your control file:

fields terminated by X'0009' optionally enclosed by X'0022'

you probably notice that the multi tab characters got swelled to one tab character, which causes that null value fields are gone, and the next non-null fields got shifted in that way, that there are no null values between 2 non-null value fields in db.

The solution to this issue is removing the "optionally enclosed ..." statement.

10g hangs because of Archiver is unable to archive a redo log because the output device

the most probable option is that there is no place for your archive logs.
If you use flash_recovery_area, increase the size there:

$ sqlplus /nolog

SQL> connect sys as sysdba;
Enter password: 
Connected.
SQL> select * from v$recovery_file_dest;

if SPACE_USED is the same or close to SPACE_LIMIT it's definitely this reason (no place to store archive logs)

increase the size of flash_recovery area:

SQL> alter system set db_recovery_file_dest_size = 50g scope=BOTH;

this will set the size of the flash_recovery_area to 50g immediately, and after restart as well (setting the parameter to SPFILE)

If your database is not opened, try to shut it down first.

SQL> shutdown immediate

if that takes long time then

SQL> shutdown abort
SQL> startup nomount

do the stuff written above

SQL> alter database open;

sqlplus enabling arrow keys for command history

It is very annoying when you in sqlplus under Linux cannot use ARROW keys to choose between executed commands.
there is a workaround for that, it's name is rlwrap:

sudo apt-get install rlwrap

then you can execute:

rlwrap sqlplus /nolog

or create an alias on sqlplus to be executed as rlwrap sqlplus.

thanks,
chris

Oracle to_date function dates from 20th century

there is a very useful function in Oracle DB to_date. But when you run:

select to_date('78/06/01','YY/MM/DD') from dual;

you might be expecting a date '1-Jun-1978' (might differ depending on your local nls settings), but you get surprisingly: '1-Jun-2078'.
Helpful is parameter RR instead of YY.
RR returns years from 21st century when given value is in range 0-49
and returns years from 20th century when given value is in range 50-99

For most cases you probably want:

select to_date('78/06/01','RR/MM/DD') from dual;

thanks
chris

Oracle - table changes how to notice

Couple of days ago I had a real problem how to provide always the most recent report with the products hierarchy. The first thought was to write a kind of a hash function to get just a hash stamp of the whole table and compare against the new one. The problem arose when it came to implementing the idea.
There is no built way to get a hash value of a whole table.

Suddenly I thought: "OK, let's check if the number of rows is different, then it would mean the content of the table changed".

I walked that way.
Replacing the table I always create first the new one with some different name and if everything goes fine, I replace the name to the right one at the end.

I have a production table called prods_hier and the new one in the background called prods_hier_bak

the following lines of code do the comparing:

changed:=false
select count(*) into no_of_rows_new from prods_hier;
select count(*) into no_of_rows_bak from prods_hier_bak;
if no_of_rows_new <> no_of_rows_bak then
    changed := true;
else -- the same no_of_rows
    select count(*) into no_of_changed_rows from
    ( select * from prods_hier
      minus
      select * from prods_hier_bak
    );
    if no_of_changed_rows <> 0 then
        changed := true;
    end if;
end if;