#!/usr/bin/ksh
# set -xv
#-------------------------------------------------------------------------------
#-- Purpose:	Display object tree
#-- Author:	Jacques Kilchoer
#__ adapted to smenu by By. Polarski
#              Fixed query wich returned mutliple rows when object name exists in many schema
#-------------------------------------------------------------------------------
function help
{
cat <<EOF

      dep <OBJ>  -u <OWNER>         # list  on which others object <OBJ> is dependent
      dep -p <OBJ>  -u <OWNER>      # List  which objects are dependent on <OBJ>
      dep -pl <OBJ> -u <OWNER>      # List all plan associated with OBJ

EOF
exit
}
OWNER=''
if [ -z "$1"  ];then
   help
fi
typeset -u OWNER
typeset -u OBJ
CHOICE=DEFAULT
while [ -n "$1" ]
do
  case "$1" in
     -u ) OWNER=$2; shift ;;
     -p ) CHOICE=DEP ;;
    -pl ) CHOICE=DEP_PLAN ;;
     -h ) help ;;
      * ) OBJ=$1 ;;
  esac
  shift
done
if [ -z "$OWNER" ];then
   echo "I need an Owner schema"
   exit
fi
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
# ----------------------------------------------------------------------
#  Based on an idea of Jonathan Lewis
# ----------------------------------------------------------------------
if [ "$CHOICE" = "DEP_PLAN" ];then
   if [ -n "$OWNER" ];then
        AND_OWNER=" and to_owner = '$OWNER'"
   fi
sqlplus -s " $CONNECT_STRING" <<EOF
prompt 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  
column nline newline
set head off lines 124 
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'List all plan associated with object' from sys.dual
/   
set pagesize 66 linesize 190 termout on pause off embedded on verify off heading  off 
select
        t.plan_table_output
from    (
        select
                sql_id, child_number
        from
                V\$sql
        where
                hash_value in (
                        select  from_hash
                        from    V\$object_dependency
                        where   
                             to_name = '$OBJ' $AND_OWNER )
        ) v,
        table(dbms_xplan.display_cursor(v.sql_id, v.child_number)) t;
EOF
elif [ "$CHOICE" = "DEP" ];then

sqlplus -s " $CONNECT_STRING" <<EOF

ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set head off lines 124 
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Objects on which $OBJ is dependent' from sys.dual
/   
set pagesize 66 linesize 90 termout on pause off embedded on verify off heading on
set linesize 190 pagesize 66
col object_name for a30
col object_type for a18
SELECT object_name, object_type, owner, status, last_ddl_time 
       FROM dba_objects WHERE ( owner,object_name, object_type ) IN 
         ( SELECT  owner,referenced_name, referenced_type FROM dba_dependencies WHERE name = '$OBJ' and owner = '$OWNER' ) 
/
EOF

else # choice = default
sqlplus -s " $CONNECT_STRING" <<EOF

ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 90 termout on pause off embedded on verify off heading off
 
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Objects that depends on $OBJ' from sys.dual
/   
set linesize 190 pagesize 66
set heading on
col owner format a20
col a format a30 head Name
col b format a30 head constraint
select lpad(' ',(a.nivel-1)*2)||obj.name a,
           decode(obj.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
                               FROM sum$ s WHERE s.obj#=obj.obj# and bitand(s.xpflags, 8388608) = 8388608), 'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP',
                     'UNDEFINED') type,
           u.name owner,
       lpad(' ',(a.nivel-1)*2)||cons.name b  , 
       to_char(obj.mtime,'YYYY-MM-DD HH24:MI:SS')Last_modified,
decode(obj.status, 0, 'N/A', 1, 'VALID', 'INVALID') status
from   sys.obj$ obj,
       sys.user$ u,
       sys.con$ cons,
       (
        select obj# obj#,
               con#,
               level nivel
        from sys.cdef$
        where rcon# is not null AND
              robj# is not null
        connect by robj# = prior obj# and
                   robj# != obj#      and
                   prior robj# != prior obj#
        start with robj# = (select obj# from  sys.obj$,sys.user$ where  obj$.name = upper('$OBJ') AND obj$.type# = 2
                                 and obj$.type#=2 and sys.obj$.owner# = sys.user$.user# and sys.user$.name = '$OWNER'
        )) a
where   cons.con# = a.con# AND
        obj.obj#  = a.obj# AND
        obj.owner#=u.user# AND
        obj.type# = 2
UNION ALL
select lpad(' ',(a.nivel-1)*2)||obj.name a,
           decode(obj.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
                               FROM sum$ s WHERE s.obj#=obj.obj# and bitand(s.xpflags, 8388608) = 8388608), 'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP',
                     'UNDEFINED') type,
           u.name owner,
       to_char(null),
       to_char(obj.mtime,'YYYY-MM-DD HH24:MI:SS')Last_modified,
      decode(obj.status, 0, 'N/A', 1, 'VALID', 'INVALID') status
from   sys.obj$ obj,
       sys.user$ u,
       (
        select d_obj# obj#,
               level nivel
        from sys.dependency$
        connect by p_obj# = prior d_obj#
        start with p_obj# = (select obj#
                             from   sys.obj$,sys.user$ where  obj$.name = upper('$OBJ') and obj$.type#=2 and 
                                      sys.obj$.owner# = sys.user$.user# and sys.user$.name = '$OWNER'
        )) a
where  obj.obj#  = a.obj#
       AND obj.type# != 2
       AND obj.owner#=u.user#
/
EOF
fi