itech9

• 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;

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);

or

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

sqlplus user@db/password

• Execute a script from sqlplus
  @[/path/]script.sql
• 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 http://www.oracle.com/technology/oramag/code/tips2004/011104.html for
a procedure to list table names and number of rows.

See http://www-it.desy.de/systems/services/databases/oracle/sqlplus/sqlplus.html.en
for an introduction to sqlplus.

Objects and resources:
http://www-it.desy.de/systems/services/databases/oracle/at_desy/user_objects.html.en