Tuesday, July 31, 2007

Monitor the SQL statement being executed in Oracle

There are several way to monitor the sql statement being executed in Oracle database.
We can do it through a client side monitoring tool, or some jdbc debugging drivers.
There is one thing we can do in the server side, and the following statement will do the trick:

select SQL_TEXT, SQL_ID, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, ELAPSED_TIME, MODULE
from v$sql where MODULE like '%JDBC Connect Client%'
order by FIRST_LOAD_TIME desc


More information about v$sql is available at v$sql reference .

For the implementation part of an Oracle client, we can use d, t, and ts. The specification about them is available at Time and Date literal .

Saturday, July 14, 2007

Character Sets and Collations in MySQL

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

The MySQL server can support multiple character sets. To list the available character sets, use the SHOW CHARACTER SET statement.
mysql> SHOW CHARACTER SET;
Any given character set always has at least one collation. It may have several collations. To list the collations for a character set, use the SHOW COLLATION statement.
mysql> SHOW COLLATION LIKE 'latin1%';

Every database has a database character set and a database collation. The CREATE DATABASE and ALTER DATABASE statements have optional clauses for specifying the database character set and collation:
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]


ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]


Example:
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

The database character set and collation are used as default values if the table character set and collation are not specified in CREATE TABLE statements. They have no other purpose.

In order to check teh character set and collation for the databases, tables and columns, you can go to the information_schema database, and check the table called SCHEMATA.
mysql> SELECT * FROM SCHEMATA;

Synonyms in Oracle

What is synonym in Oracle? What's it used for?
Here's a good article about it: Synonyms in Oracle .

Installing and testing MySQL Server 5.0.45

Instructions for installations are available in Install MySQL on a RH machine .
Basic commands include:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz tar xvf -

shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &


If mysqld is currently running, you can find out what path settings it is using by executing this command:
shell> mysqladmin variables
Or:
shell> mysqladmin -h host_name variables

Another good instruction on installing MySQL Server is available here.