& in Oracle insert or update statements

If you have & or % in your Oracle insert or update statements - you probably would like to do the following:

SQL> set define off

SQL> [your insert or update query containing & and %]

SQL> set define on

Export from Oracle table to CSV file

Sometimes you need to export result of the query to CSV file. Here is a nice example of how to do this kind of operations.

SQL> SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF

SQL> spool outfile.csv

SQL> select '"'|| column_1 || '",' || column_2 || ',' || column_3 from mytable where [your where statement]

...

...

SQL> spool off

SQL> exit

In this case column_1 data will be surrounded by "" (you may need this if you have , character in field data).

Update: SET LINESIZE determines a maximum number of characters in the line. So if you expect output bigger than 500 lines you must set this to a bigger value.

Cent OS 5.1 + Oracle 10g

Apart of modifying /etc/redhat-release to "redhat-4", there are a couple of packages that you will need to install in order to make Oracle 10g work with Cent OS 5.1:

yum install \

compat-db-4.2.52-5.1 \

glibc-devel-2.5-18.el5_1.1 \

libX11-devel-1.0.3-8.0.1.el5 \

libXau-devel-1.0.1-3.1 \

libXdmcp-devel-1.0.1-2.1 \

libXmu-1.0.2-5 \

libXtst-devel-1.0.1-3.1 \

mesa-libGL-devel-6.5.1-7.5.el5 \

openmotif-2.3.0-0.3.el5 \

xorg-x11-proto-devel-7.1-9.el5.centos

Mac OS & Database development

Since switching to Mac I was looking for good DB modeling/editing tool that will support Oracle and MySQL databases. Finally I got one...

 

SQL4X Manager J from dbSuite

 

Seems to be a really nice tool.

Leopard terminal and Midnight Commander

If you use mc while connected to some linux server and you need to select some files - use Ctrl+T

Perl: CPAN or RPM packages?

Maybe this is just CentOS 5.1 related issue, but...

 

For those of you who will install MailScanner - just a small tip to avoid problems. Spend some time and install required perl modules from CPAN. This will save you some time on fixing SpamAssassin and some other perl modules.

Manually configure Enterprise Manager Console

Run emca -config dbcontrol db and follow the instructions.

Redo log size & performance

We got a performance issue with our Oracle 10g. Suddenly the performance was going down and we was getting a significant increase on wait times. After sniffing around and asking a friend we found that:

1. All our performance pick were related with time when our Oracle was writing the archive log.
2. There is a direct relationship between log_buffer, redo log file size and performance.

So, here is a tip. Your redo log file size must be multiple by 2 of your log_buffer. What I mean is that if your log_buffer is 5 MB your redo log file size can be 10 MB (2 x 5 MB), 20 MB (4 x 5 MB), 30 MB (6 x 5 MB) and so on...

Ctrl+click with two fingers

Enable Ctrl+click with two fingers click in System Preferences -> Keyboard & Mouse -> Trackpad -> Tap trackpad using two fingers for secondary click. Believe me, it's quite comfortable.

List of all Max OS X keyboard shortcuts

Will be useful for all Mac users. A kind of 'print-it-and-put-in-front-of-you-until-you-remember'.

Mac OS X keyboard shortcuts