#!/usr/local/bin/perl

use strict;
use DBI; 
use Getopt::Long;
my $usage = "Usage  : $0 -u user/pass -p pid \n";
use vars qw/ $opt_size @opt_sizes $opt_bar /;

 sub main {

 my (@ps, @ref,$i, $j, $c, $l);
 my ($pid, $owner, $parent, $child, $found, $oramatch, $x, $cmd, $method, $login);
 my ($dir, $trace_file, $opt_l);


 $SIG{"INT"} = 'int_handle';
 $found = 0;

  if ($#ARGV<0)
    {
      die $usage;
    }

 GetOptions('u=s', \$login, 'p=i', \$pid) || die $usage;

 print "Pid ".$pid."\n";
 @ps = `ps -e -o user,pid,ppid,args | grep $pid`;
 
 $oramatch = 'oracle'.$ENV{'ORACLE_SID'};
 foreach $l (@ps) {
  print $l;
  if ($l =~ /$oramatch/i ) {
	($x, $owner, $child, $parent, $cmd) = split / +/, $l;
        print ">".$owner.'.'.$child.'.'.$parent."\n";
        $found = 1;
        last;
  }	
 } 
 unless ($found) { 
      die "Not Oracle Process \n";
 }
 my $dbh = DBI->connect("dbi:Oracle:",$login);

 my $sth = $dbh->prepare(
      q{ 
        select s.sid, s.serial#, p.spid from v$session s, v$process p
         where p.spid = ? or p.spid = ? and s.paddr = p.addr
	           });
 my $trace = $dbh->prepare(
      q{
         begin
             sys.dbms_system.set_sql_trace_in_session( ?, ?, TRUE);
         end;
         });
 sleep 2;
 my $param = $dbh->prepare(
      q{      
             select value from 
              v$parameter 
               where name = 'user_dump_dest'
       });
# Udump directory 
 $param->execute();
 $dir = $param->fetchrow;
 $param->finish;

 
 $sth->execute ($parent, $child);
 my ($sid, $serial);

 while ( ($sid, $serial, $child) =  $sth->fetchrow_array ) {
       print "Now tracing ".$sid.' '. $serial."..\n";
       $trace->execute ($sid, $serial);
# Where result ?       
       $trace_file =  $dir.'/'. lc $ENV{'ORACLE_SID'} . "_ora_". $child.".trc"; 
# Wait for trace file
       print "Wait for trace_file ..\n"; 
       sleep(3);
       unless (-e $trace_file ) {
         $trace_file =  $dir.'/'. lc $ENV{'ORACLE_SID'} . "_ora_". $parent.".trc";
       }
       if (-e $trace_file ) { 
       print "Look at ". $trace_file."\n";
       print "Or use: tkprof ". $trace_file." /tmp/trace.out explain=user/pass sort=exedsk sys=no"."\n";
       }
       else {
        print "Oops. Looking as no trace file ! ( check permission ) \n";  
        print $trace_file;
       }
 }
 $sth->finish();
 $dbh->disconnect;
 
}

sub usage
{
    my $usage = "Usage  : $0 -u user/pass -p pid ";


	print STDERR $usage."\n";
        exit;
}

 &main;