select YES, NO, NULL from a column

select YES, NO, NULL from a column:

decode(decode(:SCAN,'%','%',nvl(detail_schema.SCAN,'NULL')),:SCAN,1)=1

select null values

select null values:

Today, I had faced strange problem. I have a table with a coloumn which can have YES, NO or NULL. We want to give a choice to user from webinterface that user can either select 'YES' or 'NO' or NULL.

I can put where clause easily for YES or NO values. But I had hard time, how to select only null values. Please don't say use IS NULL. I want ony one where condition to select either "YES" or "NO" or NULL. We can't use IS NULL there.


Here is the solution finally I found:

decode(scan,:scan,1)=1

Unix TIPS - Running one scripts at a time

Unix TIPS - Running one scripts at a time


A lockfile can be used when a particular program might be run more than once at the same time and you need to be sure that only one instance of the program can do something (modify some file, access a printer, etc.). To really do this right, the program needs to both test for the lockfile and create it (if it doesn't exist) in one atomic operation. If the test-and-set operation isn't atomic — for instance, if a program tests for the lock file in one command and then creates the lock file in the next command — there's a chance that another user's program could do its test at the precise moment between the first program's (non-atomic) test and set operations. The technique in this article lets you make a lockfile atomically from a shell script.

   touch /tmp/xxx

ln -s /tmp/xxx /tmp/lockfile 2>/dev/null
ret=$?
rm /tmp/xxx
if [ $ret -ne 0 ];then
echo lockfile already exists
exit 1
else
echo success
fi

Race conditions:
It's worth pointing out that there is a slight race condition in the above lock example between the time we test for the lockfile and the time we create it. A possible solution to this is to use IO redirection and bash's noclobber mode, which won't redirect to an existing file. We can use something similar to:


if ( set -o noclobber; echo "$$" > "$lockfile") 2> /dev/null;
then
trap 'rm -f "$lockfile"; exit $?' INT TERM EXIT

critical-section

rm -f "$lockfile"
trap - INT TERM EXIT
else
echo "Failed to acquire lockfile: $lockfile."
echo "Held by $(cat $lockfile)"
fi

Another solution: Thanks Power Tools book...

LOCKFILE="/tmp/lockfile"
ACTION_WAIT="0"
MAX_ACTION_WAIT="60"
trap 'rm -f "$LOCKFILE"; exit $?' INT TERM EXIT
until (umask 222; echo "$$" > "$LOCKFILE") 2>/dev/null
do
                if [ "$ACTION_WAIT" -ge "$MAX_ACTION_WAIT" ]; then
                        echo "expiring the lock"
                        rm -f "$LOCKFILE"
                        return 1
                else
                        echo "Waiting for current ACTION to finish"
                        ACTION_WAIT=$(($ACTION_WAIT + 1))
                fi
                sleep 1
done

Unix TIPS - Send mail from Unix with file attachments

Unix TIPS - Send mail from Unix with file attachments

#!/bin/ksh
# --------------------------------------------------------------------
# Script: unix_mail_withattachments.ksh
# Aurthor: Ravin Maharaj
# Purpose: Use sendmail to e-mail messages from Unix with
# file attachements
# --------------------------------------------------------------------
SUBJ="Send mail from Unix with file attachments"
TO=someone@domain_name
CC=someoneelse_1@domain_name,someoneelse_2@domain_name
(
cat << !
To : ${TO}
Subject : ${SUBJ}
Cc : ${CC}
!
cat << !
HOPE THIS WORKS
This sample E-mail message demonstrates how one can attachfiles when sending messages with the Unix sendmail utility.
!
uuencode ${file_1} ${file_1}
uuencode ${file_2} ${file_2}
uuencode ${file_3} ${file_3}
!
) sendmail -v ${TO} ${CC}

ERRORSTACK- Oracle Debugging Event

Errorstack is used as debugging event for following reasons
To extract Failing SQL statement

To get Oracle Function call (to be used by Oracle) for interpreting cause for the error (e.g ORA 4030, ORA- 4031). It is also used in case of database hang scenarios. Refer to my earlier article on database hang

It also contains execution plan in some cases which can be used to diagnose ORA - 1652 errors (Excessive temp usage)

Errorstack can be set at System level and also at Session level.
e.g To set errorstack for ORA - 1652 ,Syntax will be

alter system set events ‘1652 trace name errorstack level 3′;
If suppose, you want to set it at session level, then

alter session set events ‘1652 trace name errorstack level 3′;

You can also use oradebug to take errorstack for particular process (say ospid 1234) as below
sqlplus “/ as sysdba”
oradebug setospid 1234
oradebug unlimit
oradebug dump errorstack 3

Setting an Oracle trace event
Another set of parameters that may be useful are events, so I’ll discuss events a bit in this subsection. Setting an event means to tell Oracle to generate information in form of a trace file in the context of the event. The trace file is usually located in a directory specified by the initialization parameter USER_DUMP_DEST. By examining the resulting trace file, detailed information about the event traced can be deduced. The general format for an event is:
EVENT = ""
There are two types of events: session-events and process-events. Process-events are initialized in the parameter file; session-events are initialized with the ALTER SESSION... or ALTER SYSTEM command. When checking for posted events, the Oracle Server first checks for session-events then for process-events.

Oracle trace Event Classes
There are four traceable event classes:
Class 1: “Dump something.” Traces are generated upon so-called unconditioned immediate, events. This is the case when Oracle data has to be dumped; for example , the headers of all redolog files or the contents of the controlfile. These events can not be set in the init.ora, but must be set using the ALTER SESSION or the DBMS_SESSION.SET_EV() procedure.

Class 2: “Trap on Error.” Setting this class of (error-) events causes Oracle to generate an errorstack every time the event occurs.

Class 3: “Change execution path.” Setting such an event will cause Oracle to change the execution path for some specific Oracle internal code segment. For example, setting event "10269" prevents SMON from doing free-space coalescing.

Class 4: “Trace something.” Events from this class are set to obtain traces that are used for, for example, SQL tuning. A common event is "10046", which will cause Oracle to trace the SQL access path on each SQL-statement.
The "set events" trace command settings
The SET EVENTS command in an init.ora file have generally been placed there at the command of Oracle support to perform specific functions. Usually, these alerts turn on more advanced levels of tracing and error detection than are commonly available. Source 2.6 lists some of the more common events.
The syntax to specify multiple events in the init.ora is:
EVENT=":: : "

You can also split the events on multiple lines by using the continuation backslash character (\) at the end of each event and continue the next event on the next line. For example:
EVENT=":\ :\ : \ "
For Example:
EVENT="\ 10210 trace name context forever, level 10:\ 10211 trace name context forever, level 10:\ 10231 trace name context forever, level 10:\ 10232 trace name context forever, level 10"After setting the events in the initialization file, you need to stop and restart the instance. Be sure to check the alert.log and verify that the events are in effect. You can specify almost all EVENT settings at the session level using the ALTER SESSION command or a call to the DBMS_SYSYTEM.SET_EV( ) procedure; doing so does not require an instance bounce for the EVENT to take effect.
The alert.log should show the events that are in effect; for example:
event = 10210 trace name context forever, level 10:10211 trace name context for ever, level 10:10231 trace name context forever, level 10:10232 trace name context forever, level 10

Example Uses of the EVENT Initialization Parameter
To enable block header and trailer checking to detect corrupt blocks:
event="10210 trace name context forever, level 10" -- for tablesevent="10211 trace name context forever, level 10" -- for indexesevent="10210 trace name context forever, level 2" -- data block checkingevent="10211 trace name context forever, level 2" -- index block checkingevent="10235 trace name context forever, level 1" -- memory heap checkingevent="10049 trace name context forever, level 2" -- memory protect cursors
And to go with these, the undocumented parameter setting:
_db_block_cache_protect=TRUE
which will prevent corruption from getting to your disks (at the cost of a database crash).
For tracing of a MAX_CURSORS exceeded error:
event="1000 trace name ERRORSTACK level 3"

To get an error stack related to a SQLNET ORA-03120 error:
event="3120 trace name error stack"
To work around a space leak problem:
event="10262 trace name context forever, level x"
where x is the size of space leak to ignore.
To trace memory shortages:
event="10235 trace name context forever, level 4"
event="600 trace name heapdump, level 4"

To take a shared pool heapdump to track Ora-04031 as the error occurs, set the following event in your init.ora file:
event = "4031 trace name heapdump forever, level 2"
For ORA-04030 errors: Take a dump by setting this event in your INIT file and analyze the trace file. This will clearly pinpoint the problem.
event="4030 trace name errorstack level 3"
The following undocumented SQL statements can be used to obtain information about internal database structures:
* To dump the control file:
alter session set events 'immediate trace name CONTROLF level 10'
* To dump the file headers:
alter session set events 'immediate trace name FILE_HDRS level 10'
* To dump redo log headers:
alter session set events 'immediate trace name REDOHDR level 10'
* To dump the system state:
alter session set events 'immediate trace name SYSTEMSTATE level 10'
* To dump the optimizer statistics whenever a SQL statement is parsed:
alter session set events '10053 trace name context forever'
* To prevent db block corruptions:
event = "10210 trace name context forever, level 10"
event = "10211 trace name context forever, level 10"
event = "10231 trace name context forever, level 10"
* To enable the maximum level of SQL performance monitoring:
event = "10046 trace name context forever, level 12"
* To enable a memory-protect cursor:
event = "10049 trace name context forever, level 2"
* To perform data-block checks:
event = "10210 trace name context forever, level 2"
* To perform index-block checks:
event = "10211 trace name context forever, level 2"
* To perform memory-heap checks:
event = "10235 trace name context forever, level 1"
* To allow 300 bytes memory leak for each connection:
event = "10262 trace name context forever, level 300"
You should be noticing a pattern here for tracing events related to error codes: the first argument in the EVENT is the error code followed by the action you want to take upon receiving the code.
Events at the Session Level
Events are also used as the SESSION level using the ALTER SESSION command or calls to the DBMS_SYSTEM.SET_EV() procedure. The general format for the ALTER SESSION command is:
ALTER SESSION SET EVENTS 'ev_number ev_text level x';
where:
Ev_number is the event number.
Ev_text is any required text (usually "trace name context forever").
x is the required level setting corresponding to the desire action, file, or other required data.
For example, to provide more detailed SQL trace information:
ALTER SESSION SET EVENTS '10046 trace name context forever level NN'
where NN:

1 is same as a regular trace.
4 means also dump bind variables
8 means also dump wait information
12 means dump both bind and wait information

Example Uses of the ALTER SESSION Command to Set EVENT Codes
To coalesce free space in a tablespace pre-version 7.3:
ALTER SESSION SET EVENTS 'immediate trace name coalesce level XX'
where:
XX is the value of ts# from ts$ table for the tablespace.
To coalesce free space in a tablespace defined as temporary:
ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';
where:
x is the value for file# from ts$ plus 1.
To get the information out of the db block buffers regarding order of LRU chains:
ALTER SESSION SET EVENTS 'immediate trace name buffers level x';
where:
x is 1-3 for buffer header order or 4-6 for LRU chain order.
To correct transportable tablespace export hanging (reported on 8.1.6, 8.1.7 on HPUX, a known bug):

ALTER SESSION SET EVENT '10297 trace name context forever, level 1';
To cause "QKA Disable GBY sort elimination". This affects how Oracle will process sorts:
ALTER SESSION SET EVENTS'10119 trace name context forever';

* You can disable the Index FFS using the event 10156. In this case, CBO will lean toward FTS or Index scan.

* You can set the event 10092 if you want to disable the hash joins completely.
It is very easy to see how SMON cleans up rollback entries by using the event 10015. You can use event 10235 to check how the memory manager works internally.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".