Using NEW_VALUE to Record Start/End Time in Oracle SQL Script

In a SQL script, I need to record the start and end time of a block which performs some DMLs, then I will query the records being DML'ed during the peroid.

COLUMN CHAR_SYSDATE NEW_VALUE START_DATETIME
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CHAR_SYSDATE FROM DUAL;

......
...The block
......

COLUMN CHAR_SYSDATE NEW_VALUE END_DATETIME
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CHAR_SYSDATE FROM DUAL;
 
SELECT ... FROM ...
 WHERE audit_creation_date BETWEEN TO_DATE('&START_DATETIME','YYYYMMDDHH24MISS') AND TO_DATE('&END_DATETIME','YYYYMMDDHH24MISS');

Indexes on Foreign Keys

Indexes on foreign keys are not always required. But I recommend to create the indexes on foreign keys when: 1) There are deletions on the parent table and 2) the child table is large.

It has happened to me a few times when deleting from the parent table, it was kind of hanging even there were no associated records in the child table. And when looking at the execution plan, it wouldn't show anything about the foreign key validation to the child table. After adding the indexes the deletions finished instantly.

Oracle Tablespace Fragmentation and Resize

http://forums.oracle.com/forums/thread.jspa?threadID=695610

Datapump doesn't support Unix PIPE

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#sthref48

The following complained dumpfile already exists
-----------------------
mknod ${dump_file} p

compress < ${dump_file} &

expdp \'/ as sysdba\' dumpfile=${dump_file} directory=db_exp schemas=$1 logfile=${dump_file}.log

No mysteries of Oracle Locally managed tablespace (LMT) and Automatic segment space management (ASSM)

Initially I called this post "Mysteries of ...", but I decided to change it to "No mysteries of...". Really there are no mysteries there with understanding.

The syntax to create a tablespace,
CREATE TABLESPACE xxx DATAFILE ...
EXTENT MANAGEMENT LOCAL [AUTOALLOCATE|UNIFORM SIZE nn]
[SEGMENT SPACE MANAGEMENT AUTO|MANUAL]
/

With LMT:
1. The NEXT storage parameter becomes obsolete.

2. The default extent allocation is AUTOALLOCATE, meaning Oracle will automatically determine the size of the extents. In my database here, for an object without INITIAL specified, the default size of an extent and the INITIAL is 640k. For objects with INITIAL from 100M~700M, the average extent size is 8M; For an object with INITIAL set to 8G, the average extent size is 62M – by looking at BYTES/EXTENTS in DBA_SEGMENTS.

3. When specifying UNIFORM SIZE nn, all the extent size is nn

4. Some experts including Tom Kyte recommended to use AUTOALLOCATE over UNIFORM SIZE: Just use system allocated extent sizes, segments will start small and grow in extent size as needed.

With ASSM:
1. It automates freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists.

2. Storage parameters including PCTUSED, FREELISTS, and FREELIST GROUPS are out. Columns DBA_TABLES(PCT_USED,FREELISTS, FREELIST_GROUPS) and DBA_SEGMENTS(FREELISTS, FREELIST_GROUPS) are NULL.

Testing DataStage ODBC driver connectivity

Excerpted from IBM DataStage document

You can test the whether your ODBC drivers can successfully connect to your data sources.

Symptoms
If a job fails to connect to a data source using an ODBC connection, test the connection outside the job to see if the ODBC connection is the source of the problem.

Environment
The procedure applies to ODBC connections in a UNIX environment.

Diagnosing the problem
To test the connectivity of your ODBC connections:

Change directory to $DSHOME and set up the IBM® InfoSphere™ DataStage® environment by running dsenv:
. ./dsenv

Start the engine shell:
cd $DSHOME
bin/dssh

In the engine shell, log to the project:
LOGTO project_name

Get a list of available DSNs by typing:
DS_CONNECT

Test the required connection by typing:
DS_CONNECT DSN

Where DSN specifies the connection that you want to test.

Enter the user name and password to connect to the required data source.

After you have connected to the data source, enter .Q to close the connection.

A Bash Script Template

#!/bin/sh

#---------------------------------------
# Script Description/Usage:
# To perform start/stop/restart of ......
#---------------------------------------
# Modification History:
# Jan 01, 2011 : A.Nie : initial creation
#---------------------------------------
#

RETVAL=0

# Check input arguments
if [ $# -ne 1 ] ; then
  echo "Usage: `basename $0` {start|stop|restart}"
  RETVAL=1
  exit $RETVAL
fi

# Check running user
if [ `id -un` != "root" ] ; then
  echo "Must run the script as root, exiting..."
  RETVAL=2
  exit $RETVAL
fi

# Define variables
VAR1=VARVALUE1
......

# Start function
start() {
          ......
}

# Stop function
stop() {
          ......
}

# Restart function
restart() {
             stop
             start
}

# Main
# While LOOP
while [ condition ] ; do
  ......
done

# For LOOP
for VAR in ...... ; do
  ......
done

case "$1" in
start)
        start
        ;;
stop)
        stop
        ;;
restart)
        restart
        ;;
*)
        echo "Usage: $0 {start|stop|restart}"
        RETVAL=3
        ;;
esac

exit $RETVAL