Oracle Tips

Alter date format

Oracle's default date format is YYYY-MM-DD, if you want to change it, you could use the following:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; 

Non-ASCII characters

Pay attention to non-ascii characters when you insert data. Because each of them ocuupies two bytes. Consider the following

Grandfather‘s Hut

The string needs 18 bytes instead of 17 (pay attention to '‘'), so you must ensure your columns size is enough.



Find RAC sessions

select * from v$session;

or

select * from gv$session;

You can find the users connecting to each node if you query the v$session. You must be aware of the node you are connecting when issuing this query because the information is exclusive from the node you are connected to.

If you query the gv$session the information gathered is the same, but as you can see it reports information from all RAC participant users.

If you take a look at the dynamic views, you will find a lot of gv$ views, those are global views that can be seen from whichever instance you are connected to and it will report information about all the instances in a single query. As you can see the gv$ views include an INST_ID (instance ID) column, which declares the number of instance the information comes from.

Find All Uniques Indexes

select * from all_indexes where index_name='table_name$unique_index_name';

select * from all_ind_columns where index_name='table_name$unique_index_name'; 

Unique indexes are stored in the table all_indexes instead of all_constraints since they are not only constraints but also indexes.

Oracle - Find Time Difference (in Seconds) of Two Timestamp Columns

Suppose you have two timestamp columns in an Oracle table, from_timestamp and to_timestamp, and you want to find the difference in seconds of the two columns. 

You can achieve it by using the following SQL statement:

SELECT FROM_TIMESTAMP, TO_TIMESTAMP, 

  (

  to_number(TO_CHAR(FROM_TIMESTAMP, 'J') * 86400 + TO_CHAR(FROM_TIMESTAMP, 'SSSSS')) - 

  to_number(TO_CHAR(TO_TIMESTAMP, 'J') * 86400 + TO_CHAR(TO_TIMESTAMP, 'SSSSS'))

  ) 

  TIME_DIFF

FROM SOME_TABLE; 

FileSystemXmlApplicationContext JBoss | Spring Context - FileSystemXmlApplicationContext Reads Wrong Resource Location from JBoss Standalone.xml

FileSystemXmlApplicationContext Reads Wrong Resource Location from JBoss

I have a legacy application which uses the following style to load Spring context:

String rootDir = System.getProperty("rootDir");

StringBuffer path = new StringBuffer();

path.append(rootDir);
path.append("/spring-context.xml");

ApplicationContext ctx = new FileSystemXmlApplicationContext(path.toString()); 

The 'rootDir' system property is defined in JBoss's standalone.xml as follow:

<system-properties>
  <property name="rootDir" value="/opt/jboss-as-7.1.1.Final/modules/someproject/main/conf"/>
</system-properties> 

The spring-context.xml file is put under "/opt/jboss-as-7.1.1.Final/modules/someproject/main/conf".
However, when I started JBoss and got this application running, it gave me this exception:

org.springframework.beans.factory.BeanDefinitionStoreException: IOException parsing XML document from file [/root/opt/jboss-as-7.1.1.Final/modules/com/someproject/main/conf/spring-context.xml]; nested exception is java.io.FileNotFoundException: opt/jboss-as-7.1.1.Final/modules/com/someproject/main/conf/spring-context.xml (The system cannot find the path specified)

It was weird because from the log it tried to load Spring context from "
opt/jboss-as-7.1.1.Final/modules/someproject/main/conf" (there is no "/" before "opt").

What happened?

FileSystemXmlApplicationContext  Source Code

I tried to search on the Internet but got no answer. So I decided to read through source code based on the exception stack trace.

Finally I found the following code from FileSystemXmlApplicationContext.

protected Resource getResourceByPath(String path) {
  if (path != null && path.startsWith("/")) {
    path = path.substring(1);
  }
  return new FileSystemResource(path);
}

So it was Spring that removed the beginning slash!

Spring's Javadoc explains why:

<b>NOTE:</b> Plain paths will always be interpreted as relative
 * to the current VM working directory, even if they start with a slash.
 * (This is consistent with the semantics in a Servlet container.)
 * <b>Use an explicit "file:" prefix to enforce an absolute file path.</b>

OK. So the solution to this problem is:

JBoss System Properties Setting for FileSystemXmlApplicationContext to Work

1. Add an additional "/" to the beginning of the path, like this:

<system-properties>
  <property name="rootDir" value="//opt/jboss-as-7.1.1.Final/modules/someproject/main/conf"/>
</system-properties>

2. Prefix with "file:" to the path

<system-properties>
  <property name="rootDir" value="file:/opt/jboss-as-7.1.1.Final/modules/someproject/main/conf"/>
</system-properties>

I've tested solution 1 and it worked fine. I suppose solution 2 should also work.

JBoss AS 7.1.1 - HornetQ UnsatisfiedLinkError

You will see this exception when you are running HornetQ in JBoss 7 with debug level log enabled:

FINE [org.hornetq.core.asyncio.impl.AsynchronousFileImpl]  HornetQAIO -> error loading the native library

java.lang.UnsatisfiedLinkError: no HornetQAIO in java.library.path

          at java.lang.ClassLoader.loadLibrary(Unknown Source) 

This however is expected and the log level is trace. This message tries to inform you that the libAIO layer will look for either libHornetQAIO.so, libHornetQAIO32.so or libHornetQAIO64.so on your LD_LIBRARY_PATH. and if it can't use either of those it will fall back to NIO.

Maven - POM and Super POM

POM, or Project Object Model, is an XML file which stores necessary information, like version, dependent libraries and reports to be generated,  for Maven to build a project.

The XML primarily contains the following information:

<?xml version="1.0" encoding="UTF-8"?>
<project>
  <modelVersion>4.0.0</modelVersion> 

  <!-- The Basics -->
  <groupId>...</groupId>
  <artifactId>...</artifactId>
  <version>...</version>
  <packaging>...</packaging>
  <dependencies>...</dependencies>
  <parent>...</parent>
  <dependencyManagement>...</dependencyManagement>
  <modules>...</modules>
  <properties>...</properties> 

  <!-- Build Settings -->
  <build>...</build>
  <reporting>...</reporting>
  <!-- Project Meta Data -->
  <name>...</name>
  <description>...</description>
  <url>...</url>
  <inceptionYear>...</inceptionYear>
  <licenses>...</licenses>
  <organization>...</organization>
  <developers>...</developers>
  <contributors>...</contributors> 

  <!-- Environment -->
  <issueManagement>...</issueManagement>
  <ciManagement>...</ciManagement>
  <mailingLists>...</mailingLists>
  <scm>...</scm>
  <prerequisites>...</prerequisites>
  <repositories>...</repositories>
  <pluginRepositories>...</pluginRepositories>
  <distributionManagement>...</distributionManagement>
  <profiles>...</profiles>
</project>

The three fields - groupId, artifactId, and version, are the minimal basic fields that a POM definition must contain.

The Super POM is Maven's default POM. All POMs extend the Super POM unless explicitly set, meaning the configuration specified in the Super POM is inherited by the POMs you created for your projects.