Archive for the 'Database' Category


PostgreSQL (postgres) quick notes..

Mar 28, 2008 in PostgreSQL

  • psql -U postgres [-d database]
  • \list : List of databases.
  • \d : show tables.
  • \d “table” (d-quotes) : show columns in table “table”.
  • \o : output to file.
  • \f [string] : set field separator.
  • \h : help.
  • \q : quit.
  • \c database : change database.
  • \i script_name : run an SQL script.
  • Queries:

  • select * from “table”; (d-quotes for table name)
  • SELECT column_name FROM information_schema.columns
    WHERE table_name =”_table_” ORDER BY ordinal_position;

Oracle JDBC 9i/10g problem..

Feb 23, 2007 in Database, Oracle

JProfiler indicated a memory leak in the Oracle JDBC 9.0.x.3 package at TTCItem.

We replaced the 9i JDBC package with the 10g version and we started getting $B!HClassCastException$B!I on Timestamp to Date conversions. It turns out the Oracle JDBC 10g package has an option to treat this issue as a version 8 backwards compatibility by doing one of two things:

– Set system property props.put(oracle.jdbc.V8Compatible, true);


– Start Tomcat with cli option -Doracle.jdbc.V8Compatible=true

Oracle basic info ..

Mar 02, 2006 in Database, Oracle

sqlplus user@db/password

  • Execute a script from sqlplus
  • List schemas:
    SELECT username FROM all_users ORDER BY username;
  • List tables in a schema:
    SELECT object_name, created FROM user_objects WHERE object_type=’TABLE’;
  • Get number of rows
    SELECT COUNT(*) FROM table;
  • Show table schema
    DESC table;
  • Limit number of rows
    SELECT * FROM table WHERE rownum < N;
  • Get current datetime
    SELECT to_char(sysdate, ‘mm/dd/yyyy hh:mi:ss’) FROM dual;

See for
a procedure to list table names and number of rows.

for an introduction to sqlplus.

Objects and resources: