Liquibase in Action

Download the product from http://sourceforge.net/projects/liquibase/files/Liquibase%20Core/liquibase-3.1.1-bin.zip/download

Unzip it on your local drive

Type "liquibase" , you will get a pretty help. We learn that we have several areas:
Standard Commands (update, rollback..)
Diff commands
Maintenance Commands

We learn that some parameters are required (changeLogFile, username and pw, DB url) and other optional (drive, logfile...).

All the default parameters can be stored in liquibase.properties. More info here

vi liquibase.properties

driver: oracle.jdbc.OracleDriver
classpath: /opt/oracle/fmw11_1_1_5/wlserver_10.3/server/lib/ojdbc6.jar
url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myrachost.acme.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=d01osb_app.acme.com)))
username: pl1_soainfra
password: pl1_acme

mkdir data
I try first an export:
java -jar liquibase.jar --changeLogFile="./data/myexportfile.xml" --diffTypes="data" generateChangeLog
you must provide an extension (.xml) in the filename, otherwise you get "Liquibase generateChangeLog Failed: No serializer associated with the filename or extension '/data/myexportfile'"

A sample entry generated is:

    <changeSet author="soa (generated)" id="1395989865665-193">
        <insert tableName="BPM_MEASUREMENT_ACTION_SEQ">
            <column name="SEQ_NAME" value="ACTION_SEQ"/>
            <column name="SEQ_COUNT" valueNumeric="0"/>
        </insert>
    </changeSet>

The changelog file is a huge file not very easy to read. If I do 2 subsequent changelogs, the differences are many even if schema/data are the same... not very intuitive.

Now I try:
java -jar liquibase.jar updateSQL
Errors:
--changeLogFile is required

I try again:
java -jar liquibase.jar --changeLogFile=./data/myexportfile.xml updateSQL


-- Changeset ./data/myexportfile.xml::1395989865665-193::soa (generated)
INSERT INTO PL1_SOAINFRA.BPM_MEASUREMENT_ACTION_SEQ (SEQ_NAME, SEQ_COUNT) VALUES ('ACTION_SEQ', 0);


INSERT INTO PL1_SOAINFRA.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, LIQUIBASE) VALUES ('1395989865665-193', 'soa (generated)', './data/myexportfile.xml', SYSTIMESTAMP, 193, '7:f8ab8f75bd7c0bafd9cd43fd119e4340', 'insert', '', 'EXECUTED', '3.1.1');
so basically for each row in the DB (in this case it's a definition of a sequence, not a row) it produces the SQL and the reference to the changeset element.

WebLogic dbping

The good old weblogic dbping utility seems to require the weblogic.jdbc.oracle.OracleDriver class with the ORACLEB option, and I have no clue where to find it (not in /opt/oracle/fmw11_1_1_5/oracle_common/modules/oracle.jdbc_11.1.1/ojdbc6dms.jar) , so... I wrote my little tool:

package com.acme.osb.db;

import java.sql.DriverManager;
import java.sql.SQLException;

public class DBPing {
 public static void main(String[] args) throws ClassNotFoundException, SQLException {
  Class.forName("oracle.jdbc.driver.OracleDriver");
  System.out.println("length " + args.length);
  String user = args[0];
  String password = args[1];
  String url = args[2];
  String now = new java.util.Date().toString();
  System.out.println(now + " user= " + user + " password=" + password + " url=" + url);
  java.sql.Connection conn = DriverManager.getConnection(url, user, password);
  System.out.println("ok");
 }
}

run this
java com.acme.osb.db.DBPing myuser mypassword "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=myport))(CONNECT_DATA=(SERVICE_NAME=myservicename)))"

java.sql.SQLException: ORA-01653: unable to extend table BLA by 8 in tablespace BLU

This query works like a breeze to find out the FAT tables:

http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle


once you discover the FAT tables, run a
truncate table BLA;

you might have to disable FK constraints (remember to reenable again)

Oracle DB package versus procedure

What is the difference between a package and a procedure? When should I use packages?

Here a series of great examples:
http://www.java2s.com/Tutorial/Oracle/0540__Function-Procedure-Packages/Packages.htm


Here the official documentation
http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_pc.htm


Here a chapter on the advantages of packages
http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/09_packs.htm

Oracle: drop table if exists

something quite annoying in Oracle is that if you do

drop table MYTABLE;

and the table doesn't exist, you get an error.

A workaround can be:

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE MYTABLE';
EXCEPTION WHEN OTHERS THEN NULL;
END;

Oracle Drop Database

this should do the job:

sqlplus "sys as sysdba"
shutdown abort;
startup mount exclusive restrict;
drop database;
exit

Got minus one from a read call

weblogic.common.resourcepool.ResourceDeadException: 0:weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: Got minus one from a read call

here
they suggest:

I also had this kind of error. I suppose that the root cause was the leak of available sessions on the oracle side. My glassfish server had 400-connection jdbc pool size and Oracle 10g had following parameters:
processes = 420
sessions = 467
transactions = 514
Every request to the DB failed with "IO Exception: Got minus one from a read call".
Increasing Oracle values (processes = 450; sessions = 500; transactions = 550) fixed the problem.

The DB is the foundation

Call me archaic or protozoic, but I still believe that the design of any application starts from the DB.

I like to get the DB structure first, then do:

for all columns:

select * FROM USER_TAB_COLUMNS;

for all tables:

select unique(table_name) FROM USER_TAB_COLUMNS;

you can stick the result in an Excel and generate from it plenty of useful stuff...