Batch and sqlplus by example. Call bat file, that would execute sqlplus

The Flow
Need to run a SQL on remote database(s), and report the results.
In this case, the SQL logic is split into several steps.
As system, connect to remote DB and kill any stuck session.
As schema owner, connect to remote DB, and refresh snaspshot.

The files:
bat files
main_main.bat                   - Main bat file, that is calling all other bat files.
main_delete.bat                 - Call delete_refresh_job.sql
main_kill_sessions.bat     - Call gen_kill_sessions.sql
main_refresh_1by1.bat     - Call refresh_All_1by1.sql
main_create.bat                 - Call create_refresh_job.sql
main_run_refresh.bat      - Call run_refresh_job.sql

sql files
delete_refresh_job.sql     - Delete "stuck" job. (The "stuck" job is refresh of a group)  
gen_kill_sessions.sql       - Kill "stuck" job session
refresh_All_1by1.sql        - Refresh Group snapshots one by one
create_refresh_job.sql    - Create refresh job
run_refresh_job.sql         - Run refresh job 

parameter files
db_list.ini           - The remote database we need to connect to.
db_system.ini   - System account on remote database.

Code on Remote DB
The logic is in package on the Remote DB
REFRESH_PKG

===========================
Code example
===========================

===========================
parameter files
===========================

db_list.ini
===========================
userA/passA@connect_str

db_system.ini
===========================
system/pass@connect_str

===========================
bat files
===========================

===========================
main_main.bat        
===========================

ECHO OFF
setlocal
cls

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_delete.bat
call main_delete.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_kill_sessions.bat
call main_kill_sessions.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_refresh_1by1.bat
call main_refresh_1by1.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_create.bat
call main_create.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO.
ECHO calling main_run_refresh.bat
call main_run_refresh.bat
ECHO.
ECHO =============================================
ECHO.

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


===========================
main_delete.bat      
===========================

ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=delete_result.txt
SET TEMP_REFRESH_LOG=delete.log

ECHO.
ECHO =============================================
ECHO main_delete.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=delete_refresh_job.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO Running %SQL_FILE% on %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================



===========================

main_kill_sessions.bat

===========================
ECHO OFF
setlocal
cls

SET inifile=db_system.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=kill_sessions.log
SET TEMP_REFRESH_LOG=temp_kill_sessions.log

ECHO.
ECHO =============================================
ECHO main_kill_sessions.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.
ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=gen_kill_sessions.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================

===========================

main_refresh_1by1.bat

===========================
ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=refresh.log
SET TEMP_REFRESH_LOG=temp_refresh.log


ECHO.
ECHO =============================================
ECHO main_refresh_1by1.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%
SET SQL_FILE=IG1_Refresh_All_1by1.sql
ECHO Running %SQL_FILE%

FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


      
===========================

main_create.bat 

===========================
ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET PROCESS_LOG=create.log
SET TEMP_PROCESS_LOG=temp_create.log

ECHO.
ECHO =============================================
ECHO main_create.bat is starting
ECHO This Program read entries from file %inifile% and report results to %PROCESS_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_PROCESS_LOG%
ECHO. 2>%PROCESS_LOG%

SET SQL_FILE=create_refresh_job.sql
ECHO Running %SQL_FILE%
FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO %SQL_FILE% %%i
    call run_sql.bat %TEMP_PROCESS_LOG% %%i %SQL_FILE%
    TYPE %TEMP_PROCESS_LOG% >> %PROCESS_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================

===========================

main_run_refresh.bat 

===========================

ECHO OFF
setlocal
cls

SET inifile=db_list.ini
REM REFRESH_LOG - the name of the generated file
SET REFRESH_LOG=run_refresh_result.log
SET TEMP_REFRESH_LOG=run_refresh.log

ECHO.
ECHO =============================================
ECHO main_run_refresh.bat is starting
ECHO This Program read entries from file %inifile% and report results to %REFRESH_LOG%
ECHO =============================================
ECHO.

ECHO. 2>%TEMP_REFRESH_LOG%
ECHO. 2>%REFRESH_LOG%

SET SQL_FILE=run_refresh_job.sql
FOR /F "tokens=*" %%i IN (%inifile%) DO (
    ECHO Running %SQL_FILE% on %%i
    call run_sql.bat %TEMP_REFRESH_LOG% %%i %SQL_FILE%
    TYPE %TEMP_REFRESH_LOG% >> %REFRESH_LOG%
ECHO Done
ECHO.
)

ECHO.
ECHO =============================================
ECHO main has finished
ECHO =============================================


===========================
sql files
===========================


===========================
delete_refresh_job.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1

begin
  -- Call the procedure
  SH_REFRESH_PKG.delete_refresh_job;
end;
/

SPOOL OFF
EXIT

/

===========================
gen_kill_sessions.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD OFF
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL kill_sessions.sql
SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||v_session.sid||','||v_session.serial#||'''', 1,15)||' IMMEDIATE;'
FROM   v$session v_session,
       v$process v_process,
       V$INSTANCE,
       DBA_JOBS_RUNNING stuck_jobs,
       DBA_JOBS     
WHERE  v_process.addr = v_session.paddr 
  AND  v_session.type != 'BACKGROUND'
  AND  v_session.sid = stuck_jobs.sid
  AND  DBA_JOBS.job(+) = stuck_jobs.job;
SPOOL OFF  

EXEC DBMS_LOCK.sleep(20);
  
@kill_sessions.sql   

SPOOL kill_sessions.sql
SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||v_session.sid||','||v_session.serial#||'''', 1,15)||' IMMEDIATE;'
FROM   v$session v_session,
       v$process v_process,
       V$INSTANCE,
       DBA_JOBS_RUNNING stuck_jobs,
       DBA_JOBS     
WHERE  v_process.addr = v_session.paddr 
  AND  v_session.type != 'BACKGROUND'
  AND  v_session.sid = stuck_jobs.sid
  AND  DBA_JOBS.job(+) = stuck_jobs.job;
SPOOL OFF  
EXEC DBMS_LOCK.sleep(20);  

@kill_sessions.sql   

EXIT

/

===========================
refresh_All_1by1.sql
===========================
SET SERVEROUTPUT ON;
SET FEEDBACK OFF;

EXEC DBMS_OUTPUT.PUT_LINE('----------------------');
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_SS');                            
EXEC DBMS_SNAPSHOT.refresh('ALARM_SS','F');                                   
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_FILE_CONFIG_SS');                  
EXEC DBMS_SNAPSHOT.refresh('ALARM_FILE_CONFIG_SS','F');                         
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_MAP_SS');                     
EXEC DBMS_SNAPSHOT.refresh('ALARM_MAP_SS','F');                            
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALARM_NAME_SS');                         
EXEC DBMS_SNAPSHOT.refresh('ALARM_NAME_SS','F');                                
                                                                     
EXEC DBMS_OUTPUT.PUT_LINE('Working on: ALLOCATOR_SS');                          
EXEC DBMS_SNAPSHOT.refresh('ALLOCATOR_SS','F');                                 
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: BILLING_SERVICE_SS');                    
EXEC DBMS_SNAPSHOT.refresh('BILLING_SERVICE_SS','F');                           
                                                                                
EXEC DBMS_OUTPUT.PUT_LINE('Working on: WORLDWIDE_LIST_SS');                     
EXEC DBMS_SNAPSHOT.refresh('WORLDWIDE_LIST_SS','F');                            

EXEC DBMS_OUTPUT.PUT_LINE('----------------------');
EXEC DBMS_OUTPUT.PUT_LINE('Completed');
EXEC DBMS_OUTPUT.PUT_LINE('----------------------');

EXIT

/

===========================
create_refresh_job.sql
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK OFF
SET PAGES 500
SET TRIMS ON

SPOOL &&1

begin
  -- Call the procedure
  REFRESH_PKG.create_refresh_job;
end;
/

SPOOL OFF
EXIT

/

===========================
run_refresh_job.sql 
===========================
SET TERMOUT ON
SET SHOW OFF
SET VERIFY OFF 
SET HEAD ON
SET LINE 500
SET FEEDBACK ON
SET PAGES 500
SET TRIMS ON

SPOOL &&1

EXEC DBMS_OUTPUT.PUT_LINE('Starting REFRESH_PKG.refresh_prc;');    

begin
  -- Call the procedure
  REFRESH_PKG.refresh_prc;
end;
/

EXEC DBMS_OUTPUT.PUT_LINE('Finished REFRESH_PKG.refresh_prc;');    

SPOOL OFF
EXIT

/

===========================
REFRESH_PKG
===========================

CREATE OR REPLACE PACKAGE BODY REFRESH_PKG IS

===========================
REFRESH_PRC
===========================

PROCEDURE REFRESH_PRC IS

g_name  varchar2(30);

BEGIN

    

    -- Create the current entry in the Master log table)

    SELECT DISTINCT SUBSTR(process_name,1,12)

    into g_name 

    FROM process_information

    WHERE process_name like '%REFRESH%';

    INSERT INTO REFRESH_LOG@MASTER_DB(DB_NAME,LAST_REFRESH_START) 

    VALUES (g_name,SYSDATE);

    COMMIT;

    

    DBMS_REFRESH.REFRESH('MASTER_GROUP');

    

    UPDATE REFRESH_LOG@MASTER_DB SET LAST_REFRESH_END=SYSDATE 

    WHERE DB_NAME=g_name 

      AND LAST_REFRESH_START=(SELECT MAX(LAST_REFRESH_START) 

                                FROM REFRESH_LOG@MASTER_DB7 

                               WHERE DB_NAME=g_name);

    COMMIT;

     

END REFRESH_PRC;

===========================
CREATE_REFRESH_JOB
===========================

PROCEDURE CREATE_REFRESH_JOB IS

  X NUMBER;  

BEGIN

    DELETE_REFRESH_JOB;

    

    SYS.DBMS_JOB.SUBMIT

    ( job       => X 

     ,what      => 'REFRESH_PKG.REFRESH_PRC;'

     ,next_date => trunc(sysdate+1)+1/24+10/1440

     ,interval  => 'trunc(sysdate+1) +1/24+10/1440'

     ,no_parse  => TRUE );

    UPDATE DB_REFRESH_MAP@master_db set refresh_flag=1

    WHERE SUBSTR(DB_NAME,1,12) = (SELECT DISTINCT SUBSTR(process_name,1,12) 

                                    FROM process_information 

                                   WHERE process_name like '%REFRESH%');

    COMMIT;

END CREATE_REFRESH_JOB;

===========================
DELETE_REFRESH_JOB
===========================

PROCEDURE DELETE_REFRESH_JOB IS

cursor job_remove is

    select job,what from user_jobs where 

    what like '%REFRESH_PRC%';

BEGIN

    for  rec in job_remove loop

        dbms_job.remove(rec.job);

    end loop;

    UPDATE DB_REFRESH_MAP@master_db set refresh_flag=0

    WHERE SUBSTR(DB_NAME,1,12) = (SELECT DISTINCT SUBSTR(process_name,1,12) 

                                    FROM process_information 

                                   WHERE process_name like '%REFRESH%');

    COMMIT;

    

END DELETE_REFRESH_JOB;

END REFRESH_PKG; 

SQLServer Auto Commit and Implicit Transactions

==============================
Auto Commit and Implicit Transactions
==============================
Auto Commit mode
SQL Server immediately commits the change after executing the statement.

Implicit Transactions Mode
Need to manually control the Rollback and the Commit operation. 
In this mode a new transaction automatically begins after the commit/Rollback. No need to specify BEGIN TRANSACTION.

Explicit Mode. 
Same as Implicit Transactions Mode, only we begin each transaction with BEGIN TRANSACTION statement.

Turning ON/OFF the implicit transactions mode.
You can turn auto commit ON by setting implicit_transactions OFF:

SET IMPLICIT_TRANSACTIONS OFF
In Management Studio: Tools-> Options -> Query Execution -> SQL Server -> ANSI -> uncheck SET IMPLICIT TRANSACTIONS checkbox

SET IMPLICIT_TRANSACTIONS ON
When the setting is ON, it returns to implicit transaction mode. 
In implicit transaction mode, each transaction must be manually commited or rolled back.

Auto commit is the default for SQL Server 2000 and up.

For example:
SET IMPLICIT_TRANSACTIONS ON
UPDATE MY_TABLE SET col_a =  'A' WHERE col_b = 2
COMMIT TRANSACTION

SET IMPLICIT_TRANSACTIONS ON
UPDATE MY_TABLE SET col_a =  'A' WHERE col_b = 2
ROLLBACK TRANSACTION

Handling ORA-00060 Deadlock Detected Error in PL/SQL

==============================
General
==============================
Applicative example of Handling ORA-00060 Deadlock Detected Error in PL/SQL.

Consider following scenario:
There are two independent PL/SQL processes running on scheduler.
It might happen that these two process would update same table.
As a result one of the processes is killed by Oracle, and "ORA-00060 deadlock detected" exception is thrown.

The solution would be to catch the ORA-00060 exception, let the process sleep for 20 seconds, as so the other process would have a chance to commit, then rerun the same code. 

If the second run fails as well, rollback the transaction and throw an applicative exception.


First Step - Grant EXECUTE ON SYS.DBMS_LOCK to the user.
As SYSTEM
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO DBA_USER WITH GRANT OPTION;
Grant succeeded.

As DBA_USER
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO MY_USER
Grant succeeded.


Second Step - Define the ORA-00060 Exception in the Package Header
Define the ORA-00060 Exception in the Package Specifications using PRAGMA EXCEPTION_INIT

--CONSTANTS
  C_FIRST_RUN                 CONSTANT INTEGER := 1;
  C_SECOND_RUN                CONSTANT INTEGER := 2; 

--EXCEPTIONS
  EXP_ORA_DEADLOCK            EXCEPTION;
  PRAGMA EXCEPTION_INIT(EXP_ORA_DEADLOCK,-60);

Third Step - Implement the solution in the code.

PROCEDURE markTransactions(pDateOfCall IN VARCHAR2,  
                           pCustomerId IN VARCHAR2, 
                           pRunTime IN NUMBER) IS

  vModuleName    VARCHAR2(100) := 'markTransactions';

BEGIN

   UPDATE  MY_TABLE_A MY_TABLE
   SET MY_TABLE.is_processed = 1
   WHERE date_of_call = pDateOfCall
     AND customer_id = pCustomerId;

   UTIL.writeTrace(vModuleName, 'Finish update MY_TABLE_A');

   UPDATE  MY_TABLE_B MY_TABLE
   SET MY_TABLE.is_processed = 1
   WHERE date_of_call = pDateOfCall
     AND customer_id = pCustomerId;

   UTIL.writeTrace(vModuleName, 'Finish update MY_TABLE_B');


EXCEPTION
   WHEN EXP_ORA_DEADLOCK THEN      
      IF pRunTime=C_FIRST_RUN THEN
          DBMS_LOCK.sleep(20);
          Util.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Attempting Rerun');
          markTransactions(pDateOfCall, pMinDateOfcall ,pOriginGateId,C_SECOND_RUN);
      ELSE  
       ROLLBACK;
          UTIL.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Rerun Failed');
          UTIL.writeTrace(vModuleName, SQLERRM);
          RAISE Util.E_LOGGED_EXP;
      END IF;  
   WHEN OTHERS THEN
     ROLLBACK;
          UTIL.writeTrace(vModuleName, SQLERRM);
          RAISE Util.E_LOGGED_EXP;
END markTransactions;

PL/SQL Exception Handling

==============================
General
==============================

PL/SQL Exception handling by example.

==============================
Index
==============================
Catch predefined Exception
Catch non-predefined specific ORA-999 Exception
Catch application Exception
Raise new Application Exception

==============================
Catch pre-defined Exception
==============================
There are some ~20 predefined Oracle Exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc.

Example:

EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors

==============================

Catch non-predefined specific ORA-999 Exception

==============================

Option A - with PRAGMA EXCEPTION_INIT

For Anonymous block/Function/Procedure - PRAGMA EXCEPTION_INIT should be under DECLARE section.

For Package - PRAGMA EXCEPTION_INIT should be in Package Specification.

Example:

DECLARE

   my_deadlock_ora_exp EXCEPTION;

   PRAGMA EXCEPTION_INIT(e_deadlock_ora_exp, -60);  --catch ORA-00060
   PRAGMA EXCEPTION_INIT(e_resource_busy_ora_exp, -54);  --catch ORA-00054

BEGIN

   ... -- Some operation that causes an ORA-00060 error

EXCEPTION

   WHEN e_deadlock_ora_exp THEN

      -- handle ORA-00060

   WHEN e_resource_busy_ora_exp THEN

      -- handle ORA-00054

END;

Option B - without PRAGMA EXCEPTION_INIT

Example:

DECLARE

   my_deadlock_ora_exp NUMBER := -60;

BEGIN

   ... -- Some operation that causes an ORA-00060 error

EXCEPTION

   WHEN OTHERS THEN 

     IF SQLCODE = my_deadlock_ora_exp THEN

     END IF;

END;

==============================

Raise new Application Exception

==============================

General syntax to raise application exceptions:

RAISE_APPLICATION_ERROR(error_number, message[, {TRUE | FALSE}]);

Where 

error_number - Any number in the range -20000 .. -20999

message - Free text, up to 2048 bytes long

Third Parameter is optional

  If TRUE, the error is placed on the stack of previous errors. 

  If FALSE (the default), the error replaces all previous errors. 

RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it.

==============================

Catch application Exception

==============================

DECLARE

   my_exception_exp        EXCEPTION;
   C_APP_EXCEPTION_NUM     CONSTANT NUMBER := -20100;
   v_msg_text              VARCHAR2(2048);

BEGIN

   IF ... THEN

      RAISE my_exception_exp;

   END IF;

EXCEPTION

   WHEN my_exception_exp THEN

      RAISE_APPLICATION_ERROR(C_APP_EXCEPTION_NUM,v_msg_text);

END;

identifier "DBMS_LOCK" must be declared

GRANT EXECUTE ON SYS.DBMS_LOCK to MY_USER;

Reference from Oracle

ksh and perl by Example: crontab task to find and kill zombie Oracle Jobs.

General.
Once a day, at 12:15 execute a script that would identify and kill Oracle Job processes, that are running longer than 24 hours.
This script is launched on Oracle server, as oracle user.

steps
A. crontab task - that runs ksh script once a day, at 12:15.
B. ksh script that launches perl script
C. perl script that connects to DB to find zombie Linux processes that were launched by Oracle job, and then kills these tasks.

crontab -l
15 12 * * * /backup/ora_exp/kill_zombie_jobs.sh

kill_long_zombie_jobs.sh
#!/bin/bash
. /etc/profile
. /etc/sh/orash/oracle_login.sh igt

perl /backup/ora_exp/kill_zombie_jobs.pl

kill_long_zombie_jobs.pl
#! /usr/bin/perl

use DBI;
use Time::gmtime;
use File::Copy;

####################################################################
#####  Subroutine to get the current date  #########################
####################################################################

sub getDate
{
  use Time::gmtime;
  $tm=gmtime;
  ($second,$minute,$hour,$day,$month,$year) = (gmtime) [0,1,2,3,4,5];
  my $locDate=sprintf("%04d%02d%02d_%02d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday,$tm->hour,$tm->min,$tm->sec );
  return $locDate;

}

########################
# main start here ######
########################
my $RetCode;
my $myDate=getDate;
my $logLocation="/backup/ora_exp/Logs/kill_zombie_jobs/";
my $logFile=$logLocation."kill_zombie_log_".$myDate.".log";

my $sql;
my $db="cgw_new";
my $db_driver="dbi:Oracle:".$db;
my $dbh=DBI->connect($db_driver,'collector','cgw154igt',{RaiseError =>1,AutoCommit=>0})|| die "$DBI::errstr";

print $DBI::errstr;

print "Log file:".$logFile."\n";

my $sql="SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL, ";
$sql=$sql."PROCESSES.spid, PROCESSES.program, ";
$sql=$sql."SESSIONS.username, SESSIONS.sql_id, SESSIONS.logon_time, SESSIONS.event ";
$sql=$sql."FROM v\$process PROCESSES, V\$SESSION SESSIONS ";
$sql=$sql."WHERE PROCESSES.program like '%J0%' ";
$sql=$sql."  AND PROCESSES.addr=SESSIONS.paddr ";
$sql=$sql."  AND SESSIONS.schemaname <> 'SYS' ";
$sql=$sql."  AND SESSIONS.type = 'USER' ";
$sql=$sql."  AND PROCESSES.background IS NULL ";
$sql=$sql."  AND SESSIONS.sid NOT IN (SELECT sid FROM DBA_JOBS_RUNNING) ";
$sql=$sql."  AND logon_time < SYSDATE-1/4";

$sql=$sql." AND logon_time < SYSDATE-1";

open (MyLog,">>".$logFile);
print MyLog "----I---- Startting kill zombie process on ".$myDate."\n";
print MyLog "Running SQL: "."\n".$sql."\n";
print MyLog "\n\n";
print MyLog "These are the details of the job that were killed : \n";

print MyLog ".........................."."\n";

my $FNsth=$dbh->prepare($sql);
$FNsth->execute();
my @row;
my $kill_cmd;
while (@row=$FNsth->fetchrow())
{
  open (MyLog,">>".$logFile);
  print MyLog "V$SEESION.sid: ".$row[1]." \n";
  print MyLog "PROCESSES.program: ".$row[2]." \n";
  print MyLog "SESSIONS.username: ".$row[3]." \n";
  print MyLog "SESSIONS.sql_id: ".$row[4]." \n";
  print MyLog "SESSIONS.logon_time: ".$row[5]." \n";
  print MyLog "SESSIONS.event: ".$row[6]." \n";
  print MyLog ".........................."."\n";
  $kill_cmd = $row[0];
  print MyLog "Running command ".$kill_cmd."..........";
  system($kill_cmd);
  print MyLog "Done"."\n";
}
$FNsth->finish();

$dbh->disconnect;

print MyLog "\n=========================\n";
print MyLog "Script Finished Successfuly";
print MyLog "\n=========================\n";


close MyLog;

=================================
SQL to identify zombie jobs
=================================
SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL, 
       PROCESSES.spid, 
       PROCESSES.program, 
       SESSIONS.username, 
       SESSIONS.sql_id, 
       SESSIONS.logon_time, 
       SESSIONS.event 
 FROM  V$PROCESS PROCESSES, 
       V$SESSION SESSIONS, 
       DBA_JOBS_RUNNING RUNNING_JOBS 
 WHERE PROCESSES.program like '%J0%' 
   AND PROCESSES.addr=SESSIONS.paddr 
   AND SESSIONS.sid = RUNNING_JOBS.sid(+) 
   AND SESSIONS.schemaname <> 'SYS' 
   AND SESSIONS.type = 'USER' 
   AND RUNNING_JOBS.job IS NULL
-- AND SESSIONS.logon_time < SYSDATE-1;


=======================================
profile files, not really important for this example...
=======================================
. /etc/profile

# /etc/profile

# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc

pathmunge () {
        if ! echo $PATH | /bin/egrep -q "(^|:)$1($|:)" ; then
           if [ "$2" = "after" ] ; then
              PATH=$PATH:$1
           else
              PATH=$1:$PATH
           fi
        fi
}

# Path manipulation
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin
pathmunge /usr/local/bin
pathmunge /usr/X11R6/bin after


# No core files by default
ulimit -S -c 0 > /dev/null 2>&1

USER="`id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"

HOSTNAME=`/bin/hostname`
HISTSIZE=1000

if [ -z "$INPUTRC" -a ! -f "$HOME/.inputrc" ]; then
    INPUTRC=/etc/inputrc
fi

export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC

for i in /etc/profile.d/*.sh ; do
    if [ -r "$i" ]; then
        . $i
    fi
done

unset i

. /etc/sh/orash/oracle_login.sh orainst
#!/bin/bash
#
# NAME:
#      oracle_login.sh
# !!!
#
# ABSTRACT:
#           Defining oracle's enironment variables. - Unix version
#
#
# ARGUMENTS:
#           [p1] - oracle sid
#
#       Note - 1) the parameter can be ommitted
#
#
# SPECIAL FILE:
#
# ENVIRONMENT VAR:
#     PROJECT_SID -
#        should be defined in the following way:
#        globaly        - the node run only one oracle instance
#        group login    - there is more than one instance on the current node
#        as a parameter - there is more than one instance per project,
#                         or for system users.
#
# SPECIAL CCC MACROS:
#
# INPUT FORMAT:
#
# OUTPUT FORMAT:
#
# RETURN/EXIT STATUS:
#            1) PROJECT_SID is not defined
#            2) the database PROJECT_SID does not exist
#            3) PROJECT_SID missing from $ORASH/sid.hosts
#            4) ORA_VER missing from $ORASH/sid.hosts
#            5) charcter missing for current sid from $ORASH/charset.dat
#
# LIMITATIONS:
#
# MODIFICATION HISTORY:
# +-------------+-------------------+-----------------------------------------+
# | Date        |     Name          |  Description                            |
# +-------------+-------------------+-----------------------------------------+
# +             +                   +                                         +
# +-------------+-------------------+-----------------------------------------+
if [ "$ORASH" = "" ]; then
   ORASH=$SH_ETC/etc/orash
fi

FACILITY="oracle_login:"
UNAME=`uname | cut -d_ -f1`   #=> Linux

  if [ "$#" -ge 1 ]; then
      PROJECT_SID=$1
   else
      project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
      if [ "$project_sid_def" -eq 0 ]; then
         echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
         return 1
      fi
   fi

   ORACLE_BASE=/software/oracle
   project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
   if [ "$project_sid_def" -eq 0 ]; then
         echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
         return 1
   fi

   ORACLE_HOME=`grep ^"$PROJECT_SID": /var/opt/oracle/oratab | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$ORACLE_HOME" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in /var/opt/oracle/oratab or /etc/oratab"
      return 2
   fi

   ORA_VER=`grep "$PROJECT_SID" $ORASH/sid.hosts | awk '{if (NR==1) print $0}' |cut -d: -f3`
   if [ "$ORA_VER" = "" ]; then
      echo "$FACILITY unable to verify ORA_VER for $PROJECT_SID using $ORASH/sid.hosts"
      return 4
   fi
   DEFAULT_SID=$PROJECT_SID

   ORACLE_SID=$DEFAULT_SID
   TNS_ADMIN=${ORACLE_HOME}/network/admin

#----------------------------------------------------------------#
# the hostname of the DEFAULT_SID appears first in file sid.hosts
#----------------------------------------------------------------#
   HOST=`grep ^"$PROJECT_SID": $ORASH/sid.hosts | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$HOST" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $ORASH/sid.hosts"
      return 3
   fi

#-------------------------------------#
#  TWO_TASK is used for a unix clients
#-------------------------------------#
# Disable TWO_TASK feature eliminating the need to change hostname, after disk replication
#   NODE=`hostname`
#   if [ "$NODE" != "$HOST" ]; then
#      TWO_TASK=${DEFAULT_SID}_remote
#   fi

#-------------------------------------#
# setting up NLS_LANG
#-------------------------------------#
   CHARSET=`grep ^"$PROJECT_SID": $ORASH/charset.dat | awk '{if (NR==1) print $0}' | cut -d: -f2`
   if [ "$CHARSET" = "" ]; then
      echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $
ORASH/charset.dat"
      return 5
   fi

   NLS_DATE_FORMAT="DD-MON-RR"
   ORACLE_LPPROG=lpr
   ORACLE_LPARGS="-P la424"
   NLS_LANG="AMERICAN_AMERICA."${CHARSET}
   ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data # for oracle
   ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data # for oracle8

   cleaned_path=$PATH
   for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
      cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
   done
   if [ $UNAME = 'SunOS' ] ; then
      cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
   done
   if [ $UNAME = 'SunOS' ] ; then
     PATH=$cleaned_path:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/openwin/bin
   else
     PATH=$cleaned_path$ORACLE_HOME/bin
   fi

   shlib_def=`set | grep "^LD_LIBRARY_PATH" |wc -l`
   if [ "$shlib_def" -eq 0 ]; then
      LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
   else
      cleaned_shlib_path=$LD_LIBRARY_PATH
      for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
         cleaned_shlib_path=`echo -e "${cleaned_shlib_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
      done

      if [ $UNAME = 'SunOS' ] ; then
         LD_LIBRARY_PATH=$cleaned_shlib_path:$ORACLE_HOME/lib:/usr/lib
      else
         LD_LIBRARY_PATH=$cleaned_shlib_path$ORACLE_HOME/lib:/usr/lib
      fi
   fi

   export PROJECT_SID
   export ORACLE_TERM
   export ORACLE_HOME
   export ORACLE_BASE
   export ORACLE_SID
   export TNS_ADMIN
   export ORACLE_SERVER
   export PATH
   export LD_LIBRARY_PATH
   export NLS_LANG
   export ORA_NLS33
   unset ORA_NLS32
   ORACLE_ENV_DEFINED="yes"
   export ORACLE_ENV_DEFINED

   export ORA_VER