Running statistics on Oracle tables from Java program.

Oracle database query optimizer relies heavily on statistics. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Normally, having correct and up-to-date statistics falls under DBA responsibilities (Oracle recommends automatic statistics gathering). However, there are cases when it might be necessary to update statistics manually, and moreover, at runtime directly from a Java program. For example, when data had been reloaded into the tables and now need to be accessed from a Java batch program while database instance is not configured for automatic statistics update. Below is an example of Java code that implements a method to gather statistics for a particular table. The code is based on the JdbcDaoSupport class from Spring framework but it is a simple convenience and author's preference - there is nothing that would prevent from porting this code to the standard plain JDBC implementation.

/*
 * Copyright 2012 VT Enterprise Software Consulting Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.vtesc.examples.db;

import java.sql.Types;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

/**
 * Demonstrates invocation of dbms_stats.gather_table_stats procedure
 * to gather an Oracle table statistics.
 * @author Vitali Tchalov
 */
public class JavaOracleStats extends JdbcDaoSupport {
 /**
  * Updates statistics for table in schema using
  * Oracle dbms_stats.gather_table_stats procedure.
  * 
  * @param schema
  * @param table
  */
 @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
 public void gatherTableStats(String schema, String table) {
  JdbcTemplate jdbcTemplate = new JdbcTemplate(getJdbcTemplate()
    .getDataSource());
  jdbcTemplate.setResultsMapCaseInsensitive(true);

  SimpleJdbcCall gatherStats = new SimpleJdbcCall(jdbcTemplate)
    .withProcedureName("dbms_stats.gather_table_stats")
    .withoutProcedureColumnMetaDataAccess()
    .useInParameterNames("ownname", "tabname")
    .declareParameters(new SqlParameter("ownname", Types.VARCHAR),
     new SqlParameter("tabname", Types.VARCHAR));
  SqlParameterSource in = new MapSqlParameterSource().addValue("ownname",
    schema).addValue("tabname", table);

  gatherStats.execute(in);
 }
}

This is a fully tested and usable code. But I plan to package this example along with a JUnit test, Spring context file and other artefacts and will publish when ready.

Frequently used SQL DDL statements

A collection of reference examples of SQL DDL (Data Definition Language) statements frequently used in application development to create and alter database tables, synonyms, constraints, indexes etc. 
Note: The statements have been tested on Oracle 11G database; most probably should work with other relational databases. Some of the datatypes used in the samples are Oracle-specific and would require some tweaking.

-- Create a table
CREATE TABLE TX_HISTORY (
  tx_id      NUMBER(10, 0) NOT NULL,
  stock_id   NUMBER(10, 0) NOT NULL,
  tx_date    DATE          NOT NULL,
  currency   VARCHAR2(5)   NOT NULL,
  price      NUMBER(9, 2)  NOT NULL,
  price_2    NUMBER
)
/

-- Create a table with a PK (Primary Key) on a single column
CREATE TABLE STOCK (
  stock_id      NUMBER(10, 0) NOT NULL,
  symbol        VARCHAR2(5)   NOT NULL,
  stk_exchange  VARCHAR2(5)   NOT NULL,
  currency      VARCHAR2(5)   NOT NULL,
--  
  CONSTRAINT stock_id_pk PRIMARY KEY (stock_id)
)
/

-- Add a PK (Primary Key) to already existing table
ALTER TABLE tx_history ADD CONSTRAINT tx_history_pk PRIMARY KEY (tx_id)
/

-- Create a table with a FK (Foreign Key) to another table
CREATE TABLE STOCK_HISTORY (
  rec_id        NUMBER(10, 0) NOT NULL,
  stock_id      NUMBER(10, 0) NOT NULL, -- FK to STOCK table
  trade_date    DATE          NOT NULL,
  open_price    NUMBER(9, 2)  NOT NULL,
  close_price   NUMBER(9, 2)  NOT NULL,
--  
  CONSTRAINT stock_history_stock_fk FOREIGN KEY (stock_id) REFERENCES stock (stock_id) ENABLE VALIDATE
)
/

-- Add a FK (Foreign Key) to already existing table to reference another table
ALTER TABLE tx_history ADD CONSTRAINT tx_history_stock_fk FOREIGN KEY (stock_id) REFERENCES stock (stock_id) ENABLE VALIDATE
/
-- Drop (delete) a table
DROP TABLE TX_HISTORY
/

-- Add a new column to an existing table
ALTER TABLE TX_HISTORY ADD QTY NUMBER
/

-- Drop a column
ALTER TABLE TX_HISTORY DROP COLUMN PRICE_2
/

NoClassDefFoundError and ClassNotFoundException errors when running standalone Java programs

This post is intended as a collection of various cases related to Java CLASSPATH issues that cause NoClassDefFoundError and ClassNotFoundException errors that are often mysterious and sometimes quite difficult to troubleshoot. 

The addressed cases go beyond trivial causes of missing jar files or mistyped package names.

Case I - A standalone Java program is packaged into a jar file. The JAR includes a seemingly correct manifest specifying the main class and the program classpath as in the sample below:

MANIFEST.MF file:

Manifest-Version: 1.0
Main-Class: com.vtesc.batchjobs.daily.txprocessor.Launcher
Class-Path: . ./txprocessor.jar ./lib/spring.jar ./lib/commons-logging.jar ./lib/ojdbc6.jar d:/common/lib/dbaccess.jar

The txprocessor.jar file is correctly built by an Ant build script and has a totally correct structure and contents:

Everything seems to be perfectly fine but when run, it fails with classloader errors (Java version "1.6.0_29"):

D:\batchjobs\txprocessor>java -jar txprocessor.jar
Exception in thread "main" java.lang.NoClassDefFoundError: com/vtesc/batchjobs/daily/txprocessor/Launcher
Caused by: java.lang.ClassNotFoundException: com.vtesc.batchjobs.daily.txprocessor.Launcher
        at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
Could not find the main class: com.vtesc.batchjobs.daily.txprocessor.Launcher. Program will exit.

Cause:
The returned error displaying the "Could not find the main class" message can be actually quite misleading as it may make you to suspect some problem with the main class. However, in this case it has absolutely nothing to do with the main class. The cause of the error is actually in the Manifest file on the Class-Path: line:

d:/common/lib/dbaccess.jar

Hard-coding an absolute path to a library is a very bad practice in itself but specifying the path without indicating a protocol causes the total failure. By default, the Java platform uses the java.net.URLClassLoader class for loading program classes. As its name implies, the URLClassLoader uses another system class, java.net.URL for specifying the resource location. The URL class complies with a number of RFC documents and it requires the file:/ protocol to be specified for file system resources.

The problem can be corrected by prefixing the absolute path to the dbaccess.jar file with the file:/ protocol as shown below:

Manifest-Version: 1.0
Main-Class: com.vtesc.batchjobs.daily.txprocessor.Launcher
Class-Path: . ./txprocessor.jar ./lib/spring.jar ./lib/commons-logging.jar ./lib/ojdbc6.jar file:/d:/common/lib/dbaccess.jar

Yet, a better way would be not to use absolute paths. Case in point, using the Windows specific path here will confine this Java program to the Windows platform which goes against the Java platform-independence principle.
But we live in a world which is far from perfect and sometimes one finds himself forced to follow requirements even if they go against best practices and common sense.

Samples of control files for Oracle SQL Loader

This is intended as a quick reference to provide a set of templates for creating SQL Loader control files for most frequently used scenarios.

Sample 1:
Loading data from a TAB-delimited file: FIELDS TERMINATED BY X'09'
The sample also demonstrates the following techniques:

• skip lines: OPTIONS (SKIP=1)  instructs the SQL Loader to skip the first line (the number specifies the number of lines)
• auto-generate values for a primary key column: SEQUENCE(MAX, 1)
• load data by adding more rows without truncating the existing data: APPEND
• load into DATE type columns: "TO_DATE(:TX_DATE,'MM/DD/YYYY')"

-- ============================================================
-- stocktx.ctl

-- SQLLDR Control File for data loading to STOCKTX table.
--
-- The input file has the TAB-separated format with a single line per table row.
--
-- To run (sample):
--   sqlldr batchjob/<pwd>@oradb control=stocktx.ctl errors=1000 log=logs/stocktx_load.log

OPTIONS (SKIP=1) load data
  infile 'data/stocktx_data.tab'
  badfile 'logs/stocktx_data.bad'
  discardfile 'logs/stocktx_data.discard'
  APPEND
  into table portfolio.stocktx
  FIELDS TERMINATED BY X'09'
  trailing nullcols
(
    TX_ID        SEQUENCE(MAX, 1),
    EXCHANGE,
    SYMBOL,
    QTY,
    PRICE,

    TX_DATE      "TO_DATE(:TX_DATE,'MM/DD/YYYY')"

)

Sample 2:
Loading data from a multicharacter-delimited file (^|^) file: FIELDS TERMINATED BY '^|^'
The sample also demonstrates the following techniques:

• delete existing data prior to the load: TRUNCATE
• specify the data file name on the command line: data=addr.dsv
• specify log files pathnames via command line parameters (as opposite to hard-coding in the control file).
• evaluate the source values and transform on a condition, e.g if the Postal Code is "N/A", replace it with NULL: ZIP_CODE "decode(:ZIP_CODE, 'N/A', '', :ZIP_CODE)"

• apply the current time/date to a DATE column: SYSDATE 

• CONSTANT - allows to set a column to a constant value; this value is hard-coded into the control script file, not from the input data file. The value is treated as a character string; SQLLDR will attempt to convert it to the column type if required.

-- ============================================================
-- addr.ctl
-- SQLLDR Control File for data loading to ADDR table.
--
-- The input file the '^|^' sequence of characters to separate values.
--
-- To run (sample):
-- sqlldr batchjob/@oradb control=addr.ctl errors=100 log=logs/addr_load.log bad=logs/addr.bad discard=logs/addr.discard data=addr.dsv
--
-- Note: the logs directory needs to be created prior to running the script.

OPTIONS (SKIP=0)

load data
  TRUNCATE
  into table ADDR
  FIELDS TERMINATED BY '^|^'
  trailing nullcols
(
    ADDR_ID SEQUENCE(MAX, 1),
    CITY,
    STREET_NUM,
    STREET_NAME,
    STREET_DIR,
    ZIP_CODE    "decode(:ZIP_CODE, 'N/A', '', :ZIP_CODE)",
    MODIFIED_ON SYSDATE,
    BATCH_ID    CONSTANT 1
)

This is a table structure for the sample above:

CREATE TABLE ADDR (
  addr_id      NUMBER(10)     NOT NULL,
  city         VARCHAR2(40)   NOT NULL,
  street_num   NUMBER         NOT NULL,
  street_name  VARCHAR2(40)   NOT NULL,
  street_dir   VARCHAR2(4)    NOT NULL,
  zip_code     VARCHAR2(7),
  modified_on  DATE           NOT NULL,
  batch_id     NUMBER,
--  
  CONSTRAINT addr_id_pk PRIMARY KEY (addr_id)
)
/

Sample 3:
SQLLDR control file to load data from a comma-separated file (,) where field values may be enclosed in double-quotes (").


Control file explanation:

• FIELDS TERMINATED BY ',' - specifies that the fields are delimited by comma.
• OPTIONALLY ENCLOSED BY '"' - specifies that field values may (or may not) be enclosed in double quotes, for example: "2269 Lake Shore Blvd. W., Toronto, ON"
  This is often required if the data can contain characters that are used for field separation, the comma character in this particular example.
• SEQUENCE(COUNT, 1) - this is usually used to generate unique values for the table primary key. The COUNT parameter specifies that the 1st value to be the number of records in the table before the load plus 1.
• --  INTO TABLE ADDR.STREET_VALUES_BAK - a commented out line; it is handy during the development phase when the control file can be re-used to load data into different tables.
• "UPPER(:ALT_STREET_NAME)" - convert the values to the upper case.
• CREATE_DATE "TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')" - this is useful to load only the date part of the current time into DATE type columns.
  SYSDATE returns the current time which is formatted into a string containing Year, Month, and Day which in turn converted back to DATE type.
• BATCH_ID EXPRESSION "(SELECT MAX(BATCH_ID) FROM ADDR.BATCH_ID WHERE TABLE_NAME = 'STREET_ALTERNATIVE')" - useful to insert a constant value for each data load, for a example a Batch ID or Run ID.
  A separate table is required, of course, to maintain load data IDs. A new record can be inserted into this table from a shell script prior to invoking the SQLLDR script. See a DDL and INSERT statement below.

-- =============================================================
-- street_alternative.ctl
-- Loading alternative street names.
-- SQLLDR Control File for data loading into ADDR.STREETS_ALTERNATIVE table.
--
-- The input file is |-separated in double quotes.
-- sqlldr vit1@devdb1 control=street_alternative.ctl errors=1 log=logs/street_alternative.log bad=logs/street_alternative.bad discard=logs/street_alternative.discard data=street_alternative.txt
--

OPTIONS (SKIP=1)

LOAD DATA
  APPEND
  INTO TABLE ADDR.STREET_ALTERNATIVE
--  INTO TABLE ADDR.STREET_VALUES_BAK
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS (
  REC_ID            SEQUENCE(COUNT, 1),
  STREET_ID,
  ALT_STREET_NAME   "UPPER(:ALT_STREET_NAME)",
  CREATE_DATE       "TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD')",
  BATCH_ID          EXPRESSION "(SELECT MAX(BATCH_ID) FROM ADDR.BATCH_ID WHERE TABLE_NAME = 'STREET_ALTERNATIVE')"
)

DDL for the tables used in the sample #3 above:

CREATE TABLE ADDR.STREET_ALTERNATIVE (
  rec_id          NUMBER       NOT NULL,
  street_id       NUMBER       NOT NULL,
  alt_street_name VARCHAR2(40) NOT NULL,
  create_date     DATE         NOT NULL,
  batch_id        NUMBER       NOT NULL,
--  
  CONSTRAINT rec_id_pk PRIMARY KEY (rec_id)
)
/

CREATE TABLE ADDR.BATCH_ID (
  rec_id          NUMBER        NOT NULL,
  table_name      VARCHAR2(255) NOT NULL,
  batch_id        NUMBER        NOT NULL,
  create_date     DATE          NOT NULL,
 --  
  CONSTRAINT batch_id_pk PRIMARY KEY (rec_id)
)
/

-- A statement to insert a record into the BATCH_ID table for
-- the next value to be used in a data load:
insert into ADDR.BATCH_ID values (1, 'STREET_ALTERNATIVE', 
  (select nvl(max(batch_id), 0) from ADDR.BATCH_ID
    where table_name = 'STREET_ALTERNATIVE') + 1, sysdate)
/

Oracle Database Management for Application Developers

A set of how-to's and tips for managing an Oracle 10g/11g database targeted for non-DBA folks such as Application Architects and Java Developers.

Problem: The Enterprise Management (EM) webapp console can no longer be started after a password change.

This tip is specific to running Oracle on Windows. Normally the installer will register a Windows service for the EM console. Assuming the default settings for the instance and host were kept, the service name is likely to be OracleDBConsoleorcl.
Since it is a full-blown web application running under OC4J application server, it's quite heavy on the hardware; so, as a post-installation step it is common to change the service default Startup Type from Automatic to Manual and launch it only when there is a need.
If all of a sudden the service begins to fail to start, chances are that the message and error code reported by the service will be very generic and most likely useless for troubleshooting.
So, the first thing to do is to locate the EM deployment directory which contains log files. Normally (the tip assumes an installation on a single host, not cluster), it will be under the <DBHOME> directory; for example, a typical location:

....\product\11.2.0\dbhome_1\localhost_orcl\sysman\log

One possible reason could be the expired password for the SYSMAN user. The SYSMAN is the default super user account created by the installer to set up and administer the Enterprise Manager. It is also the database account that owns the objects stored in the Oracle Management Repository.

Seeing the following exceptions in the emoms.log file is a good indication that something is wrong with the SYSMAN account:

Caused by: java.sql.SQLException: ORA-01017: invalid username/password; logon denied
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:73)
...

java.sql.SQLException: ORA-28000: the account is locked
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:73)

 

In one particular case (that inspired this post), it was a forced password change after logging in to EM. The passwords had expired for a number of system accounts, SYSMAN included, and I was forced to change them. The submitted request led the EM console app to crash and it was impossible to start it again. (In retrospect, a better course of action probably was to shutdown the EM and instead change the passwords from sqlplus but it's a topic for a different post).
This is what helped to resolve the situation:

• Locate the emoms.properties file for the EM. It should be at a path similar to this: C:\app\<user_account>\product\11.2.0\dbhome_1\localhost_orcl\sysman\config
• Make a backup copy of the file and then edit these 2 properties: 
  oracle.sysman.eml.mntr.emdRepPwd=<new_password>
  oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
• Use sqlplus command line tool or any other client to connect with a system account (e.g. SYS) and run the following statement to change the password and unlock the SYSMAN account:
  alter user SYSMAN identified by <new_password> account unlock;
• Shutdown the database server service (usually named OracleServiceORCL), the listener (OracleOraDb11g_home1TNSListener) and whatever other related Oracle services (OracleJobSchedulerORCL, OracleMTSRecoveryService, etc.) and for a good measure reboot the Windows. 
• Start back the OracleServiceORCL and OracleOraDb11g_home1TNSListener services. After that, the EM service, OracleDBConsoleorcl, should start successfully.
  Keep in mind that the password used for the SYSMAN account is not necessarily the same used to login into EM; normally you login to EM with the SYS account as SYSDBA and have to use the password set for the SYS user. The SYSMAN account is a system account not to be used by human users.

Check the emoms.properties configuration file - the previously edited emdRepPwd property should be now overridden with an encrypted value for the password and the emdRepPwdEncrypted should be reset back to TRUE by the EM.
In case this tip has not helped to resolve the problem, check again the log files, particularly OracleDBConsoleorclsrvc.log, emdb.nohup and emoms.log, for clues.
Also, a message log at C:\app\<user_account>\diag\rdbms\orcl\orcl\alert\log.xml contains alerts that reference errors and detailed log files.
And finally, refer to Maintaining and Troubleshooting the Repository in Oracle Enterprise Manager Advanced Configuration 10g documentation for help.

Check out the following tip to disable the password expiry to prevent these kind of troubles in the future.

Tip: Change password expiration policy

For a locally installed developer instance of Oracle database, the security is often of no concern. On the contrary, it can be quite a hussle when the database is used only by a single developer or a small team.
It therefore makes sense to disable password expiry for system accounts to prevent troubles such as one described above. Normally, most of the system accounts are locked by default in simple installations such as a development instance, but SYS, SYSTEM, SYSMAN, MGMT_VIEW have to be unlocked and active  even in a minimal configuration.
In Oracle, the password expiry policy is defined on the profile level.
By default, the Oracle installer creates these system accounts under the DEFAULT profile. If these assumptions are applicable, then executing the statement below will disable password expiration:

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Of course, this will impact every user account (a.k.a. schema) that is based on the DEFAULT profile.

The same can be achieved via the EM - log in under SYS user as SYSDBA and navigate to the Sever > Profiles > DEFAULT page. Click Edit and then select the Password tab. In the Password section put UNLIMITED (or click on the lookup icon) into the "Expire in (days)" input field.