#!/usr/bin/ksh
#set -x
#---------------------------------------------------------------------------------
# program: smenu_get_str_in_sqlarea.ksh
# Author : 
# Date   : 08-Febuary-2006
#---------------------------------------------------------------------------------
SBINS=$SBIN/scripts
if [ -z "$1" ];then
   echo "I need a sub part of an sql"
fi
STR=$@
#while [ -n "$1" ]
#do
#  case "$1" in
#    -len ) FLEN=$2; shift ;;
#  esac
#  shift
#done
FLEN=60
STR="%$STR%"

HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`

. $SBIN/scripts/passwd.env
. ${GET_PASSWD} 
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi

sqlplus -s "$CONNECT_STRING" <<EOF

set linesize 190 pages 66  head on
col parsing_schema_name head 'User' for a16
col PLAN_HASH_VALUE head 'Plan id' for 999999999999
col EXECUTIONS head 'execs' for 9999999
col gets head 'gets' for 9999999999
col child_number head 'Ch|ld' format 99
col cost form 999999 head 'Cost'
col sql_text for a${FLEN} head 'Sql Text'
col lat for a11 head 'Last Active time'
select /*+ comment */ sql_id,child_number,PLAN_HASH_VALUE, optimizer_cost cost, EXECUTIONS, 
     buffer_gets,parsing_schema_name, to_char(last_active_time,'DD/HH24:MI:SS') lat , substr(sql_text,1,$FLEN) sql_text
      from v\$sql a
      where  upper(sql_text) like upper('${STR}')
   and substr(sql_text,1,12) != 'EXPLAIN PLAN' 
   and substr(sql_text,1,21) != 'select /*+ comment */' 
/
exit
EOF