itech9

From http://databases.about.com/od/specificproducts/a/acid.htm

• Atomicity states that database modifications must follow an ‘all or nothing’ rule.
• Consistency states that only valid data will be written to the database.
• Isolation requires that multiple transactions occurring at the same time not impact each other’s execution.
• Durability ensures that any transaction committed to the database will not be lost.

Example of range partitioning:

CREATE TABLE sales (year NUMBER(4),
                    product VARCHAR2(10),
                    amt NUMBER(10,2))
   PARTITION BY RANGE (year)
   PARTITION p1 VALUES LESS THAN (1992) TABLESPACE u1,
   PARTITION p2 VALUES LESS THAN (1993) TABLESPACE u2,
   PARTITION p3 VALUES LESS THAN (1994) TABLESPACE u3,
   PARTITION p4 VALUES LESS THAN (1995) TABLESPACE u4,
   PARTITION p5 VALUES LESS THAN (MAXVALUE) TABLESPACE u5;

http://www.oracle-dba-online.com/sql/oracle_table_partition.htm

• – Run ‘SQL Server Configuration Manager’
  — click on
  ‘… Network Configuration’
  ‘Protocols’
  TCP/IP,
  Properties
  ‘IP Addresses’ tab.
• – SQL service info:

  net start “SQL Server (SQLEXPRESS)”

  net start “SQL Server Browser”

• – sqlcmd info:

  http://msdn.microsoft.com/en-us/library/ms162773.aspx

  sqlcmd -S tcp:localhost,2248 -l 60

  sqlcmd -S tcp:localhost,2248 -i script.sql

On Linux:

1. $ [/sbin/]service postgresql initdb

2. $ [/sbin/]service postgresql start

3. The “initdb” procedure will create user “postgres”.

4. Change /var/lib/pgsql/data/pg_hba.conf

from this:

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident sameuser
# IPv6 local connections:
host    all         all         ::1/128               ident sameuser

to this:

# "local" is for Unix domain socket connections only
#local   all         all                               ident sameuser
local   all         all   trust
# IPv4 local connections:
#host    all         all         127.0.0.1/32          ident sameuser
host    all         all         127.0.0.1/32  trust
# IPv6 local connections:
#host    all         all         ::1/128               ident sameuser
host    all         all         ::1/128  trust

5. Restart postgres

$ [/sbin/]service postgresql restart

6. Test access:

psql -U postgres (no password)

• 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