Does your Oracle client hang when connecting? Are you using Oracle 10.2.0.1? Do you get the following if you strace the process?

gettimeofday({1129717666, 622797}, NULL) = 0
access("/etc/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("./network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
access("./network/admin/sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
fcntl64(155815832, F_SETFD, FD_CLOEXEC) = -1 EBADF (Bad file descriptor)
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
times(NULL) = -1808543702
.
.
.

Has your client been up for more that 180 days? Well done; you've just come across the same bug that has bitten two of our customers in the last week. Back in the days of Oracle 8, there was a fairly imfamous bug in the Oracle client where new connections would fail if the client had been up for 248 days or more. This got fixed, and wasn't a problem with Oracle 9i at all. Now Oracle have managed to introduce a similar bug in 10.2.0.1, although in my experience the number of days appears to be shorter (180+).

Thankfully, this has been fixed in the 10.2.0.2 Instant Client. More information can be found on forums.oracle.com and www.redhat.com.

Ever wanted to know who was logged into your oracle server and where from? This SQL will show you the username connected, which machine they are connected from and what time they connected.

SELECT s.username, s.program, s.logon_time 
   FROM v$session s, v$process p, sys.v_$sess_io si 
   WHERE s.paddr = p.addr(+) AND si.sid(+) = s.sid 
   AND s.type='USER';

I've spent the weekend playing around with the new Oracle XE Debian packages in preparation of having to use them at work in the near future. I've written up my experiences of setting the server and connecting remote clients in my latest article.

Talking of work, we have a position for a junior support role open. If you live in or around Brighton, England and know a little bit about Linux, Debian, Tomcat, Java, PostgreSQL and Oracle and willing to learn more, have a look at the job description and get in contact.

Today, I discovered SQL::Translator, which seems to have some very interesting use cases. Basically, it is a perl module for translating a database schema from one of a number of formats and turning it into another format. Parsers include:

• Live querying of DB2, MySQL, DBI-PostgreSQL, SQLite and Sybase databases
• Access
• Excel
• SQL for DB2, MySQL, Oracle, PostgreSQL, SQLite and Sybase
• Storable
• XML
• YAML

Output formats include:

• Class::DBI
• SQL for MySQL, Oracle, PostgreSQL, SQLServer, SQLite and Sybase
• Storable, XML and YAML
• POD, Diagram, GraphViz and HTML

Several things spring to mind with this:

1. Defining your Schema in XML and using SQL::Translator to convert it into SQL for several databases and a set of classes for Class::DBI, which would make your application immediately target any of the supported databases.
2. Documenting an existing database for which you've lost existing documentation by pointing it at a running database instance and outputting HTML page and, thanks to the Diagram output module, visual representation of the structure.
3. Convert one database from product to another. Point it at a MySQL database and generate SQL for postgresql. If you generated some Class::DBI stuff you could possibly quickly write a script to copy data too.
4. Using the sqlt-diff script, compare you current SQL to what is running on the database and generate a SQL script to upgrade the database structure using ALTER TABLE etc. Presumably you'd need to convert any data yourself, but is still a time saver for large databases.

I'm sure other people could think of some interesting uses for this. Having looked at the Class::DBI stuff, I think it could do with some improvements. I can't see a way to set the class names, although I haven't spent that much time looking and it insists on having all the classes in one file. Also the XML and YAML formats generated are rather verbose and I haven't looked to see how much I could cut them down to use as the source definition. I suspect that I can make it a lot shorter and rely on sensible defaults.

My initial reason for wanting to use SQL::Translator is that Class::DBI::Pg has a large start up time and isn't really suitable for CGI use if you have a complex database. This might be mitigated by using mod_perl, but in the mean time I was hoping I could speed up startup by telling Class::DBI my column names, rather than it querying the database. SQL::Translator should allow me to save duplicating the database structure, whilst allowing me to support multiple backend databases. If I get this working, I'll write up a short HOWTO.