Passing from Unix to PLSQL using bind variables
. $ENV_FILE echo “Started test.ksh `date +%D-%T`” V1=1; V3=One; $ORACLE_HOME/bin/sqlplus pvm/pvm
Passing from Unix to PLSQL using bind variables
#!/usr/bin/ksh ########## Configuration ############# ENV_FILE=$1
get_vmstat.ksh for Solaris
#!/bin/ksh -x # First, we must set the environment . . . . ORACLE_SID=WEBP18F export ORACLE_SID ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d’:’` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH SERVER_NAME=`uname -a|awk ‘{print $2}’` typeset -u SERVER_NAME export SERVER_NAME # sample every five minutes (300 seconds) . . . . SAMPLE_TIME=30 while true do vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$…
Search and replace editor command in vi
To search and replace between line1 and line2 use following: (*) This is vi editor command: following command will serach and replace whole file. (this is default with %s). :line1,$ s/old_string/new_string/g following command will search and replace only in specific lines: :line1,line2 s/old_string/new_string/g for example, :3,5 s/CIF/UOC/g above command will replace CIF with UOC in…
Find average Row Length and other table size calculation. metalink notes
Subject: Extent and Block Space Calculation and Usage in V7-V9 Database Doc ID :10640.1
How to choose Driver table in SQL statement
Driver table: Take the driver table which returns the less number of rows for predicate with literal valae. For example, Consider the statement below: SELECT SUM(BIP.VALUE) value FROM BSKT_ITEM_CALC_PRICING BIP,BSKT_PRICING_ELEMENT BP WHERE BIP.BASKET_ITEM_ID IN (:”SYS_B_0″) AND BIP.PRICING_ELEMENT_ID = BP.PRICING_ELEMENT_ID AND BP.PRICING_TYPE_CODE = :1; Here SQL> select BASKET_ITEM_ID, count(1) from BSKT_ITEM_CALC_PRICING group by BASKET_ITEM_ID; BASKET_ITEM_ID COUNT(1)…
SQL_PLAN.sql for checking real execution plan
set lines 132 set pages 1400 SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and sql_id=’4ftvbpzhwkfd8′ and child_number=0 CONNECT BY PRIOR id = parent_id and sql_id=’4ftvbpzhwkfd8′ and child_number=0;
Good Doc 28-JUN-2006
Good Doc About UNDO Management: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6894817116500 Cache Buffer Chain Latch: http://www.orafaq.com/maillist/oracle-l/2003/02/27/2671.raw
How to stop OCSSD Daemon
/etc/init.d/init.cssd stop to stop cssd process.
Follwoing korn shell is used to move files from source folder to destination folder and gzip it in destination folder. mv_iotto.ksh
#!/usr/bin/ksh a=`ps -ef|grep “mv_iotto.ksh”|grep -v grep|wc -l` if [ $a -gt 2 ] then echo “Exiting because of more than 1 Moving archive log script is running ” exit fi for fname in `ls -rlt /tmpdb2/oradata/UOCIOTTO_ARCH/*.arc | awk ‘{print $9}’ | sed ‘$d’ | sed ‘$d’` do print “”; print “File Name : $fname “;…