Error Message :
SQL Jobs remain active even when an issue with the database is encountered such as the TNS listener not found or a DB userid account is locked. The SQL job does not fail and the only way to determine there is a problem is to look in the Job report where you will see the database error and then cancel the Job.
If the SQL itself fails, then the Job will fail as expected.
Root Cause: Database connection errors do not cause SQL jobs to fail.
Use the "Runtime" tab of the SQL Job to set a maximum runtime (MRT). If the MRT is reached, execute a tasks to check for connectivity to the DB and send an email/msg/Notification. Then cancel the SQL Job.
Fix Status: No Fix